The URL in the video is Microsoft Forms
While importing data into Power Query, you want to VLOOKUP to a summarized version of the data. In this video, the first step is a quick query to produce total revenue by region as a lookup query. Then, when you merge in Power Query, you will VLOOKUP to bring the Region total to each row, so you can calculate that row's percentage of region. This video debuts the Group By feature in Power Query.
While importing data into Power Query, you want to VLOOKUP to a summarized version of the data. In this video, the first step is a quick query to produce total revenue by region as a lookup query. Then, when you merge in Power Query, you will VLOOKUP to bring the Region total to each row, so you can calculate that row's percentage of region. This video debuts the Group By feature in Power Query.
Transcript of the video:
3,2,1... here we go, Learn Excel for MrExcel Podcast Episode 2293 Power Query - Link to Summarized Version of Ourselves. Hey, welcome back to MrExcel Netcast. I'm Bill Jelen. Well this is really exciting. I'm recording this live at Microsoft Ignite. They have this great podcast booth here that they gave me with all kinds of equipment that I don't know how to use and I won't be using. But what will be doing here is recording this in Camtasia.
Alright so this is our second episode from the weekend.
The whole theme this week is Power Query. I will be doing a lot of power query, so we're going to talk about how to load a file to power query and doing a VLOOKUP to a summarized version of that file. So we're actually doing to import this twice. So, first thing we want to do if the data is going to stay here in Excel, we want to make it into a table. So Ctrl+T or Format as Table. Click OK and rather than table one, I will call it MyOriginalData.
OK, first step: summarize the data. So here we go. Our goal is to get percent of region, which means I need to summarize this by region. I want to know region and revenue in a lookup table. Essentially I'm going to create that using power query which is pretty wild. Alright so.
Say Data, From a Table or Range. There we are like that.
OK, and what fields do we need here? We just need region and revenue. Regional revenues. I choose Region. I choose Revenue. I right-click and say remove the other columns. Alright then we're left with region and revenue.
Now the region column is already selected. On the transform tab we will use group by.
And we will say Group by Region the new column is going to be called total revenue for region.
And the operation is going to be Sum and the field is going to be the Revenue column all right now. Right now I have 617 rows in this file. When I click OK after this step, we're going to get a really small file, just five Rows with the total revenue. That's all I needed to do. And instead of calling is my original data. I'm going to call it region totals like that.
Region Totals. And then Home, Close and Load..., do we get to load to the spreadsheet?
No, not yet Close and Load to....
Only Create a Connection.
There we are. Click OK. Alright, so now we have our Region Totals, Connection only. That's beautiful. Now we're going to come back in and do the query to get the whole table into Excel.
So we're going to say into power query, so we're going to say get Data From a Table or Range.
And we'll call this Pointer to Whole File.
Enter. Date or they added a date and time. There's all transform and say that that is really just a date. Like that?
Replace current item quantity, region, price each, revenue that looks beautiful Home Close and Load to...
Only create a connection.
Click OK.
Alright, so now I have two tables here. What are these have been hanging around? You know, I just use the same file each time. This is probably from an old podcast and will get rid of that. Alright, so now I have the whole file and I want to connect that to the region totals the whole file connect to the region total so just start out here from nowhere, Get Data from our combined queries, Merge. This is essentially doing a look up from its own table.
And we'll start out with pointer to whole file like this.
And we're linking it to the region totals like that. And what fields are in common region and region. Those are the fields we want, of course. 617 rows from the table are matched.
That's perfect. Click OK.
All right, here we are date item, quantity, region, price each, revenue and then region totals. We want to expand this just to get one thing. The total revenue for the region. I don't need a prefix on that. Click OK.
Alright, so now we have total revenue for the region and revenue and for each of these items I want to see what the percentage of the total item is. So we're going to add a column. It's going to be a custom column.
Will call this percentage of region.
Like that and the formula is going to be this row's revenue divided by total revenue for the region like that click OK.
And bam, there it is just outside of view. Unfortunately, let's see if we can make things smaller back here so it gets back inside the view. Of course we can't. You'll just have to trust me that percentage of region is out there and working well here. Let's just bring it back in so you can see it. So there's percentage region. So if we grab a Calculator 899 divided by 1759562 better be that number. .005 and hey, I'm going to check that later when I have a Calculator for right now though, not going to check it.
We're on the Clock alright, so here we go. Alright, so here we go now we're ready to Close and Load and there's our file with total revenue for the region.
That is success isn't that crazy? We're taking this one file we're loading into power query twice, once as a summary once as the whole thing, and then essentially we're doing essentially the revenue divided by a sum if of the revenue. That would be the Excel equivalent of course we didn't have to do SUMIF or SUMIFS, it all just works well. Hey tomorrow, at Ignite on Thursday. November 7th, I'll be doing a 20 minutes. I'm right over there in theater 9.
Feel free to stop by for that or next week in Chattanooga, TN and then that's it. No more seminars left. I will be done traveling.
I'll still be doing seminars in Florida and I'll be doing the YouTube channel and writing books. Don't worry about any of that. That's all going to work like that. By the way. The latest book, MrExcel, LX, the Holy Grail of Excel tips. Click that on the "i" in the top right hand corner to buy that book also.
Hey, because we're here at Ignite, they have a great giveaway.
Thanks for listening to our podcast that we recorded at Microsoft ignite and Microsoft is giving away Microsoft Surface earbuds to our listeners? To enter visit aka.ms/podcastsweepstakes (the URL is down there in the YouTube description) before December 15th, 2019. If you like what you see here on the channel please *subscribe* and ring that Bell so you will get notifications.
Thanks for stopping by we will see you next time for another netcast from MrExcel.
Alright so this is our second episode from the weekend.
The whole theme this week is Power Query. I will be doing a lot of power query, so we're going to talk about how to load a file to power query and doing a VLOOKUP to a summarized version of that file. So we're actually doing to import this twice. So, first thing we want to do if the data is going to stay here in Excel, we want to make it into a table. So Ctrl+T or Format as Table. Click OK and rather than table one, I will call it MyOriginalData.
OK, first step: summarize the data. So here we go. Our goal is to get percent of region, which means I need to summarize this by region. I want to know region and revenue in a lookup table. Essentially I'm going to create that using power query which is pretty wild. Alright so.
Say Data, From a Table or Range. There we are like that.
OK, and what fields do we need here? We just need region and revenue. Regional revenues. I choose Region. I choose Revenue. I right-click and say remove the other columns. Alright then we're left with region and revenue.
Now the region column is already selected. On the transform tab we will use group by.
And we will say Group by Region the new column is going to be called total revenue for region.
And the operation is going to be Sum and the field is going to be the Revenue column all right now. Right now I have 617 rows in this file. When I click OK after this step, we're going to get a really small file, just five Rows with the total revenue. That's all I needed to do. And instead of calling is my original data. I'm going to call it region totals like that.
Region Totals. And then Home, Close and Load..., do we get to load to the spreadsheet?
No, not yet Close and Load to....
Only Create a Connection.
There we are. Click OK. Alright, so now we have our Region Totals, Connection only. That's beautiful. Now we're going to come back in and do the query to get the whole table into Excel.
So we're going to say into power query, so we're going to say get Data From a Table or Range.
And we'll call this Pointer to Whole File.
Enter. Date or they added a date and time. There's all transform and say that that is really just a date. Like that?
Replace current item quantity, region, price each, revenue that looks beautiful Home Close and Load to...
Only create a connection.
Click OK.
Alright, so now I have two tables here. What are these have been hanging around? You know, I just use the same file each time. This is probably from an old podcast and will get rid of that. Alright, so now I have the whole file and I want to connect that to the region totals the whole file connect to the region total so just start out here from nowhere, Get Data from our combined queries, Merge. This is essentially doing a look up from its own table.
And we'll start out with pointer to whole file like this.
And we're linking it to the region totals like that. And what fields are in common region and region. Those are the fields we want, of course. 617 rows from the table are matched.
That's perfect. Click OK.
All right, here we are date item, quantity, region, price each, revenue and then region totals. We want to expand this just to get one thing. The total revenue for the region. I don't need a prefix on that. Click OK.
Alright, so now we have total revenue for the region and revenue and for each of these items I want to see what the percentage of the total item is. So we're going to add a column. It's going to be a custom column.
Will call this percentage of region.
Like that and the formula is going to be this row's revenue divided by total revenue for the region like that click OK.
And bam, there it is just outside of view. Unfortunately, let's see if we can make things smaller back here so it gets back inside the view. Of course we can't. You'll just have to trust me that percentage of region is out there and working well here. Let's just bring it back in so you can see it. So there's percentage region. So if we grab a Calculator 899 divided by 1759562 better be that number. .005 and hey, I'm going to check that later when I have a Calculator for right now though, not going to check it.
We're on the Clock alright, so here we go. Alright, so here we go now we're ready to Close and Load and there's our file with total revenue for the region.
That is success isn't that crazy? We're taking this one file we're loading into power query twice, once as a summary once as the whole thing, and then essentially we're doing essentially the revenue divided by a sum if of the revenue. That would be the Excel equivalent of course we didn't have to do SUMIF or SUMIFS, it all just works well. Hey tomorrow, at Ignite on Thursday. November 7th, I'll be doing a 20 minutes. I'm right over there in theater 9.
Feel free to stop by for that or next week in Chattanooga, TN and then that's it. No more seminars left. I will be done traveling.
I'll still be doing seminars in Florida and I'll be doing the YouTube channel and writing books. Don't worry about any of that. That's all going to work like that. By the way. The latest book, MrExcel, LX, the Holy Grail of Excel tips. Click that on the "i" in the top right hand corner to buy that book also.
Hey, because we're here at Ignite, they have a great giveaway.
Thanks for listening to our podcast that we recorded at Microsoft ignite and Microsoft is giving away Microsoft Surface earbuds to our listeners? To enter visit aka.ms/podcastsweepstakes (the URL is down there in the YouTube description) before December 15th, 2019. If you like what you see here on the channel please *subscribe* and ring that Bell so you will get notifications.
Thanks for stopping by we will see you next time for another netcast from MrExcel.
