Differences between Excel and Get & Transform

- Before we fall completely in love with Get & Transform, we have to go over a few points because some tasks are still easier to do in native Excel, some Get & Transform features aren't intuitive, and error messages aren't always helpful. This requires us to be very strategic in how we approach certain tasks. So let's look at a few things. First of all, we have this list of data of bakers and location. The locations, Nebraska, New Jersey, Arizona, are bold, and that's the only cue we have.

So if we wanted to separate these, the states and the names, and go into Get & Transform, let's check out what happens. Select the data set. From Table. Ah. Now the data looks like one big stack with no cue as to how to separate what from what. If that was the only cue we had, we would have to correct it in native Excel. The next caution: We cannot operate at the cell level.

Let's say Aurelia Ball, her name is really Aurelia Bell. We can't make that change here. So let's close and load this to the workbook. We have to make that change at the source. Let's go back. Aurelia Bell. Now let's go back into our query.

Boom. Aurelia Bell. Now, just so you know, sometimes you won't see the change show up immediately. In those cases, you go up here to Refresh Preview. Let's close and load and look at our result. That says Aurelia Ball. Let's go up to data, Refresh All, now Aurelia Bell. Beautiful. Let's go to our next warning.

Here we have a list of pies, Monday through Wednesday, how many were sold. Let's take this into Get & Transform and make a Totals column. Okay. With the cursor in the data set, From Table. First, notice that we don't have a column that we can automatically start typing in to make our Totals. We have to deliberately add a column. In this case, we're going to add a custom column. Add, Custom Column.

Call it Totals. Another caution: There is no 'Sum' function in Get & Transform. There are many dozens, but not a 'Sum' function, so we have to do these one at a time. So I'm going to double-click Monday, plus Tuesday, plus Wednesday, okay. Now why do we have a 17 as a result in 1 and null for 2? We should have a 6 there.

Here's why: Let's highlight these columns. Monday, hold down the shift key, Wednesday, now go Home, replace values. What to find: null. Replace with 0. Okay. Now we have to rearrange the steps. This replace value is last, we need to move this up to before we end the column. Now let's look at Column.

There you go. Now we have our 6 in Totals for Banana Cream Pie, Whole. Point being here, null or an empty cell, do not equal a zero. Next, we would like a column that brings together the items, an equal sign, and then the total. Let's do that. Add a Column, Add a Custom Column.

Call this Total Pies. Equals, Items, &, equal sign, space, double-quote, space, equal sign, space, double-quotes, &, Totals. Okay, we have an error. And that error is we cannot put text and a number in the same cell like we can do in native Excel.

So here's the correction. We have to go to our Totals and change this to Text. Rearrange the steps. Now we have exactly what we wanted. We have the full statement, Banana Cream Pie, Slice equals 17. And finally, the biggest caution of all in Get & Transform, there is no undo.

I can click this x and get rid of that step, but if that was a mistake and I need it back, there is no undo button. I would have to manually put it back. So these are just a few things that you need to keep an eye on as you move forward and get to know Get & Transform.