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

Power Query example - Microsoft Excel Tutorial

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

Start my 1-month free trial Buy for my team

Power Query example

“

Welcome to my Power Query home. Now, this video is supposed to be an example of what Power Query can do for you. Think about standing in line at an ice cream shop on a nice summer day, and you're looking up at the menu board and there's all of these wonderful ice cream flavors, but there's one that you're curious about. And you think, "Wow, it looks good, but I would like that little wooden spoon sampler before I commit to two scoops of this ice cream." Well, this video is that wooden spoon taster of Power Query. And I'm going to show you an example of something that I used to have to do years ago, and I wish I had Power Query available to me. Now, we're starting here with this list of REPs and IDs. And the IDs are stuck to the end of the REP's names. We have an agreement that for REPs who've processed $4,500 or more, they get $500; $8,000 or more and they get $750. Let's look at the data. Minimize this and open the transactions report. Here is March and IDs. And this used to drive me crazy because the report that I would get was like this. It just had the IDs, no names. And my director doesn't know who is for J02. He knows names like Amy, Charles, Bennett. All right. So here's the March data, February, January. Right. So I need to compile all of this data, figure out amongst all of the three months of transactions. If 4C60 gets any commissions, and if so, do they get $500 or do they get 750? There's our data, and it's a common need to have multiple worksheets worth of data and need to get it stacked up. Here is how I'm going to start. I'm going to close this, actually. I'm going to use Power Query. Data, Get Data, From File, From Excel Workbook. Navigate to the workbook. Go to the Desktop, right there's the file. Double-click it. There are the three sheets. February, January, March. I'm going to click the folder and transform data. My data is in this table, okay, but I have to expand it. So I'm going to hold down this column, hold down the control key, highlight this column, right-click, remove other columns, and then I'm going to expand. I don't want to use original column name as prefix. Okay. Now, to clear out some of these headers, I don't need the ones that say REP or transaction amount, but notice Power Query has stacked this data up all in one place. No copying and pasting. Let's clear some of this out. I don't want any to say REP. Okay. The whole row is cleared out. Also, notice over on the right side, we've got our query settings panel, we've got the name of the query, and then we have applied steps that are being recorded. Also, let me show you the query editor. The advanced editor. There we go. Code is being written behind the scenes. And when you get good enough, you can write this code from scratch, or you can come in here and make minor modifications if you bold. All right. So I'm going to close that. So what I want to do now, I really don't need this month because I'm looking for the totals. Right-click, remove that. I want to call this REPS and amounts. Now, I'm going to go Home, Group By, group by the REPS column and then some, the amount column. Okay. Next, I am not interested in any total transactions that are below $4,500, so I am going to filter those out. So less than, keep rows where count is greater than or equal to 4,500. Okay. Then I'm going to sort this descending. Now, we need to know who are these REPS based on their IDs. I'm going to close and load this. And then go back to this sheet, right-click, Get Data from Table/Range. Okay. And I haven't written any formulas here. What I need to do now is split the column. Split it at non-digit to digit. But these do need to stay together because those are the full IDs. I like there. Control key, highlight, merge columns. No separator. Okay. ID and call this Names. This list has everybody on it. So I'm going to call this Main List. This is the list of people who get a commission. And what I'm going to do is add a column, add a conditional column. If count is less than 8,000 then 500, otherwise, 750. Okay. Call this Commission and then call this TransactionTotal. Now, we need names to go with the IDs. I want to go Home, Merge Queries, Merge Queries as New. I'm going to leave transactions and commissions up top and then main list on the bottom. Match up ID and REPS and I'm going to leave this as a left outer join and okay. I'm going to expand. I don't need the IDs, I need the names. Okay. Move this over. And notice, again, all of the steps are being recorded. All of the steps. All of the steps. Great. Now, I can tell my director, Mariposa down to Graciella get $750, and then Vicki, Regina, and Marcus get $500 and close and load. So here are our commissions. I'm going to boost this up. Make this a 16-point font so we can all see. Right. Get rid of the filter buttons. And once I thought I was done or I was even in the middle of the process, my director would come to me and say, "The IT people ran the report too early. What you're working on is incomplete." Oh, boy. So now they've got me a new report. But if I had Power Query available to me, I could do this. Open up the workbook where I've done everything in Power Query and then go Refresh. Look at that. I didn't have to do those steps over again. I didn't even have to open that new workbook. I didn't have to write any formulas. I just had to refresh. And sometimes Power Query does require a lot of work upfront, but once you've done it, you just have to refresh in the future. Now, I've shown you a lot with this taste of Power Query, and hopefully, you do want to dive in and get a couple of scoops of this because it is so helpful. And I had somebody contacted me to tell me that she saved her employer over $30,000 a year because of what she was able to do with Power Query in saving time, automating tasks, and being more accurate. So hopefully, you'll get in on this and reap the benefits of knowing Power Query.

Contents