How to move a column to the beginning of a data set in Excel Power Query. How to Group and summarize data in Excel Power Query
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2267. Power query, move to beginning.
Any time I do my full day power Excel seminar, in the afternoon, we always cover power query.
And I usually try and come up with something that, you know, someone in the audience brought as a bad data set and I try to show how to use power query. And so inevitably, you know, I’m in there and I’m creating some new columns or something like that, so let’s edit this power query that I created in the last episode. And I end up with some awesome new column out here at the far-end side and I want to move that back to the beginning and I would always just kind of try and drag it and if you have 15, 20 columns, it is super annoying to drag. So someone in my Orlando seminar, just this month in February said that if you go right click and then go to move and then it’s just outside ofyour view or maybe just inside your view. There’s something there that’s called, To Beginning, right and that allows you to take that field and move it to the beginning which is a huge time saver. And it just goes to show there’s so much stuff in power query that you know, I’m always learning new things all the time.
Now here, that’s a quick one. So let me give you one more that is just kind of a bonus trick here.
Let’s say I want to get total revenue. Total revenue over here so the revenue field by first characters. Really all I need is first characters and revenue. I choose those two columns and then right click and remove the other columns so I’m left with just first characters and revenue and we’ll right click here and rename. This was our industry or a sector abbreviation like that. Alright.
And I really just want to figure out, like I have to upload this somewhere, you know, just six rows or something like that. So under transform, there’s an item here that’s called Group By. Just do the basic today, Group By Sector where you want the new columns is going to be called Total Revenue and the operation will be count rows but be a sum of the revenue column, click OK and beautiful.
We have hundreds of rows of data, we’ve collapse it down and added a new column, collapse it down, summarize that new item home and close and load. I love power query. It is just an amazing, amazing feature.
I’m trying to do all these videos today in one take and I just absolutely screwed that one up for sure. Alright so mrexcel.com, check out this new book by Oz du Soleil, Oz just wrote this Cleaning Excel Data with Power Query, straight to the point, 5 bucks has 7 really good examples of how to do things in power query. So check that out.
Hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Any time I do my full day power Excel seminar, in the afternoon, we always cover power query.
And I usually try and come up with something that, you know, someone in the audience brought as a bad data set and I try to show how to use power query. And so inevitably, you know, I’m in there and I’m creating some new columns or something like that, so let’s edit this power query that I created in the last episode. And I end up with some awesome new column out here at the far-end side and I want to move that back to the beginning and I would always just kind of try and drag it and if you have 15, 20 columns, it is super annoying to drag. So someone in my Orlando seminar, just this month in February said that if you go right click and then go to move and then it’s just outside ofyour view or maybe just inside your view. There’s something there that’s called, To Beginning, right and that allows you to take that field and move it to the beginning which is a huge time saver. And it just goes to show there’s so much stuff in power query that you know, I’m always learning new things all the time.
Now here, that’s a quick one. So let me give you one more that is just kind of a bonus trick here.
Let’s say I want to get total revenue. Total revenue over here so the revenue field by first characters. Really all I need is first characters and revenue. I choose those two columns and then right click and remove the other columns so I’m left with just first characters and revenue and we’ll right click here and rename. This was our industry or a sector abbreviation like that. Alright.
And I really just want to figure out, like I have to upload this somewhere, you know, just six rows or something like that. So under transform, there’s an item here that’s called Group By. Just do the basic today, Group By Sector where you want the new columns is going to be called Total Revenue and the operation will be count rows but be a sum of the revenue column, click OK and beautiful.
We have hundreds of rows of data, we’ve collapse it down and added a new column, collapse it down, summarize that new item home and close and load. I love power query. It is just an amazing, amazing feature.
I’m trying to do all these videos today in one take and I just absolutely screwed that one up for sure. Alright so mrexcel.com, check out this new book by Oz du Soleil, Oz just wrote this Cleaning Excel Data with Power Query, straight to the point, 5 bucks has 7 really good examples of how to do things in power query. So check that out.
Hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
