From the course: Excel: Power Query (Get & Transform)

Differences between Excel and Power Query - Microsoft Excel Tutorial

From the course: Excel: Power Query (Get & Transform)

Start my 1-month free trial Buy for my team

Differences between Excel and Power Query

“

Power Query can do a lot of really cool things, but there are still some things that are easier to deal with in native Excel or on worksheet. I want to show you a few things that you have to watch out for. In this dataset, we've got City Center, Southwest. These are locations. And then you see Carla down to Marty Hunt. Those are instructors associated with City Center. And then scroll down. We got down to The Apiary and those are instructors. Great. And then in column C, we have these emojis. What they symbolize, well, it's just kind of fun. Doesn't matter really. But we can use emojis in formulas in our worksheets. But they're there for me to demonstrate something for you. So first of all, I'm going to go down to, say, Grady Andersen, hit the Windows and period keys, and let's add an emoji. How about this jack-o'-lantern? Okay. Now, my cursor is in the dataset. I'll right-click, Get Data from Table/Range. Okay. Now, let's look at what happened. First of all, I'm going to slide this over. The party face next to Carla Noland's name has bombed out as a square. The face with hearts next to Bridgette's name has bombed out, as did the pretzel next to Lou Livingston's name. But we did get the smiling face, Christmas tree, and jack-o'-lantern. Be careful of that. Notice also City Center is no longer bold, Southwest, Ft. Adderly, The Apiary. If those were important, that needed to be handled in Excel, somehow flagging them or coming up with some strategic way to where you can still have those demarcations when you bring this data into Power Query. Also, let me bring this back over, we can't work at the cell level like we can in Excel. Let's say we need to add a new name to City Center. I can't insert a row and then type directly in there. What I have to do, I'm going to close and load this. There's the data and see our emojis are all back. But let's go over here. I need to add a name. Right-click, Insert, Row Above, a new name, Amy Larson. Insert. We'll go over and double-click so we can get back into the query. There is Amy Larson in Row 7, but we don't see Amy Larson over in column A in the query result. I'm going to close this. And what we have to do is go to Data and then refresh. Now, there is Amy Larson. One other thing to show you. I've got this volunteer data, and I'm going to close this panel for now. We've got these volunteers, the areas that they volunteered in, and then how many hours in Q1 and Q2. Now in Excel, I can do equals sum, open parentheses highlight the two columns, enter. Drag this down and now I have totals. Notice that for Ed and Q1 that's blank. That might be blank because we don't know how many hours Ed volunteered yet. We're still waiting for those details. And Farley, blanks. But then Svetlana has a zero in Q2 hours and Tammy has a zero in Q1 hours. But we still have sums. I'm going to delete this. Now, cursor in the dataset, right-click, Get Data from Table/Range, table has headers. Okay. Want to slide over and notice where we had blanks in our source data, we have nulls. Also, in Power Query, I can't open up a new column and then write a formula. There are a number of ways that I can get this addition to add these columns together. I'm going to do this. I'm going to highlight Q1 hours and then hold down the control key, highlight Q2 hours, go to Add Column and then click here for standard, that comes down, and then I add. Look at the result. Got 72 hours in Row 1, adding a 40 and 32, but we have a null in Row 2 when we tried to add the 35 hours and a blank cell. This tells you that a blank and a zero are not treated the same way in Power Query as they are in Excel. One thing we can do. Highlight this column, hit the control key, highlight Q2, I'm going to right-click, replace values, replace null with zero, and okay. Now, I want to change the order of the steps. Move this one up and then go to Inserted Addition. Now, I have calculations. And I'm going to go back out, close and load to existing worksheet. I'm going to put the data right here. Okay. Now, when I do get more information -- it did. Twenty-five hours and now scroll down and Data, Refresh. There's a 25 hours and now we have 60 total hours. One other thing. I'm going to scroll up, and what if we decide we want this to just say Q1 and this to say Q2? And then I'm in the Data tab, I want to refresh. We've got an error. Okay. And now we have this warning symbol. I'm going to double-click. The code behind Power Query is looking for Q1 and Q2, but it just says Q1 right now. We can go to the error, and it's taking us to the change type step, but here's one thing to show you. We're going to go into the advanced editor. See, is writing code in the background. And when we changed Q1 hours to Q1 and Q2 hours to Q2, the code is befuddled. So I'm going to correct that by doing this. Just change the code. Done. All right. So that is also looking for Q1 hours. Let's look back at the code. Yep. You see. Should be Q1, Q2. All right. Slide over. Let's check it all out, and then done. Everything is back in order. Just a few of the things to watch out for when you're working with Power Query. You can't work at the cell level. You can't just open up new rows or new columns like you can in a workbook. And if you have to change column headers, make sure you have to or be able to change those in the code behind Power Query.

Contents