This video, for Excel's 30th anniversary walks through what a game-changer Power Query is for Excel. You start with a folder full of horribly-formatted CSV files. Using the Query Editor, you load all of those files, clean the data, unpivot the data. Tomorrow, when more files are in the CSV folder, a simple Refresh will load and clean all of the data again. Power Query is built in to Excel 2016 under Data, Get & Transform or a free add-in for Excel 2010 and Excel 2013. Download from Download Microsoft Power Query for Excel from Official Microsoft Download Center
To try it out yourself with my files, download them from http://www.mrexcel.com/podcast1956.zip
To try it out yourself with my files, download them from http://www.mrexcel.com/podcast1956.zip
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1956.
Power Query Changes Everything.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, today is a very special day.
September 30th, 2015 is the 30th anniversary of Excel. Happy birthday to Excel and to all of the [ inaudible ] people, who rely on Excel, every day to make our jobs easier.
This illustration here by Walter Moore.
Walter did lot of the illustrations in my MrExcel, Excel Book.
And so, I thought about today, what is most important to talk about today and I wanna talk about one of the best things I've seen here in Excel, in last 20 years and that is power query.
If you have Excel 2010 or Excel 2013, you can use power query.
Just go download it from Microsoft and Excel 2016 is right there, on the data tab.
Get and load or load and transform, or whatever crazy name, they get rid of the power query.
The name in Excel 2016 just build it all in to the data tab.
So, let me setup today's problem.
I have here, a folder of CSV files.
You see, CSV files are dumped in here by the IT department.
They are ugly, ugly CSV files.
Why they are ugly?
Well, here in column A, Someone decided, was a great idea to put customer/region, right in that column.
How horrible is that.
Totals are hard-coded instead of being in formulas and there is a run date at the bottom of every formula and I have to clean this data, not just from this file but from every single file.
Oh! And check it out.
Windows of course, puts a file in here that's not a CSV file.
Whoops! Saved my grocery list in this folder, how stupid was that.
All right! So, hey! This is [ inaudible ].
We have this folder, other stuff get saved in to the folder.
More stuff gets dumped in here, every time.
Power query is going to let us, solve this problem.
I'm gonna copy that URL, copy that folder name and we'll come back here to Excel, I've created a brand new work sheet.
And power query, I'm gonna say, I'm gonna get the data from a file, from a folder.
I'll paste the folder path in there, [ ctrl + V ] and click [ OK ].
All right! Now, just this step right here is really cool, I now have an Excel grid, there I can close and load to the Excel grid, that has a list of all the files in this folder.
Think about it, you are working on next year's budget and you have a file from every single department, you need to check of to make sure that they're all done.
You can use this trick.
But here let's clean the data little bit.
First of, let's get rid of things that are non CSV files.
So, xlsx and dot db.
get rids of my grocery list and the...
the thumbs dot db This next step is absolutely amazing, I've to tell you I've use power query for six months before and I realize that this step is the...
I learn the step.
I was looking at the pre-release copy of "M is for (DATA) MONKEY" by Ken Puls and Miguel Escobar.
This little symbol up here I never realized, I could double click this symbol, and Excel is going to go out and load the rows from every one of the CSV files, in to a single grid here.
That is absolutely amazing.
So, here is the first CSV file and then here the headings start over again.
Second CSV file, third CSV file.
Amazing, amazing tool and when I look at this...
Any time I've a word total here, I wanna get rid of that.
Any time I've a word date or the customer, region heading, I will get rid of that.
So, in power query, I can teach power query that we always wanna get rid of any rows that say customers, region.
Any row that say, run date.
Any row that say, total.
Click [ OK ] and it will delete those rows.
What about tomorrow, what if I have new customers?
It didn't say keep these lists to the customers that said delete those specific items.
All right! Customers, region, all in one column like this.
I'm gonna choose that column, I'm gonna to transform .
Actually, I can do right here on the home tab, split column by delimiter.
Let's talk about data, text to columns, you know.
You'll say, oh! Wait a second, this is power query doing things like doing in regular Excel.
That's absolutely true, is doing things you can do in regular Excel.
But here is the beautiful thing, Power Query does it better.
We've first name, last name in column A and you just split it out in to two columns.
Works great right, Bob Smith.
Bob in column A, Smith in column B but then you have Jim Jones Junior and the Junior ends up in column C.
Marry Allan Walton.
Walton ends up in column C.
Billy, Joe, Jim, Bob Smith takes up data in columns A through F.
Oh! This is beautiful.
Right here, if there is a noble prize for Excel features, this would be the one that I would nominate.
At the left most delimiter says, hey! We have a column and we're gonna split it in to two columns.
I don't care if there is 14 slashes (/) in here.
Do not fill up columns A through P, or A through N.
I just let it go to two columns, so click [ OK ].
All right! And Bam! There we go, we have customer and region, I'm gonna rename both of these columns, so this one becomes customer.
And right click, rename this one becomes region.
All right! If you ever build a pivot table from data that goes like this, you know that months stretching across through board is absolutely horrible.
I even have a grand total here, I'm gonna get rid of that grand total, so simple enough.
I'm just gonna remove that column but the months.
To get rid of the months, to un-pivot the months, well, you have to use Mike Alexander's old trick with multiple consolidation ranges or you just cut and paste, cut and paste, cut and paste, 12 times to create, 12 times of many rows, so the months go down.
Check this out, power query has a feature on transform called un-pivot I'm marking un-pivot, I'm gonna say un-pivot other columns and it will take data that was going across and it'll make it go down.
So, we rename this to be month, and this one over here, this is our sales.
I rename that column to be sales.
All right! couples of things I see that the sales were actually coming as text, they're left justified, well that's easy to fix.
Just come here and it says, currency or whole number.
In my case the sales are in whole numbers, I'm gonna choose whole numbers.
The months are text.
All right! Here we go.
Add column, a new custom column.
I'm gonna take that month name, and I'm going to add in quotes equal [ space ] 1 comma 2015.
It's 2015 and all this data is from 2015.
Click [ OK ].
All right! So, now watch this, I take that text month and that 1 comma 2015, I'm gonna merge those columns.
Separator none, new column name is gonna be called date.
Click [ OK ].
All right! We have something which looks like a date but it still text.
I choose that column and under transform, I'm gonna change type, text to date.
Bam! Just check that out.
But this point I don't need this temporary column any more and I don't even need that column any more.
I'm gonna choose both of those columns and say remove columns.
All right! Hey, look.
I'm a data analyst, I work for a long time as data analyst.
It is fun to do all of this stuff right, you discovered a new file one day and you go through all of this steps to clean the data.
Look over here on the right hand side this is the list of all the steps.
This is huge fun, i love this, is nothing better than this.
On first day, I get to do it.
But then I take this data, I create report and I hand that report to my manager and then the horrible words come out.
This is amazing.
Do it again, tomorrow.
It was fun on day 1.
It's sort of fun on day 2.
On day 400, it's the worst tragedy ever.
Here's the amazing thing about power query.
Power query remembers all of the steps, is actually building a program behind the scenes.
Advanced editor, this is a language called M.
The letter M, the 13th letter of the alphabet.
Apparently, [ inaudible ] already taken.
So, here's the M code, you don't have to know the M code.
Although, there are things you can do with the M code.
They make it even better but the important part is power query saves the data and all of the steps.
So, close and load.
My tip here is if you see data in query, it's still working.
When you see the data in green, that you know it's saved and good to go.
I'm gonna save this file.
Tomorrow, if I need to come back in, I just go to data, I go to refresh and it will go back through all of those steps.
Let me prove that to you.
We'll go to insert, pivot table.
The data is there, I'm going to go existing worksheet, right down here to the right hand side.
So, we can see it.
Click [ OK ].
I'll build little report with regions down the left hand side and say, all right! It's a worth 5.67 million dollars.
Simple enough.
Tomorrow, there's more CSV files that come from IT department.
All right! So, here's a bunch of more CSV files.
I'll take these, I drag them, I'll drop them in that folder.
Come back to Excel.
Open this file tomorrow, all I have to do come to the data tab, click [ refresh all ], that will force Excel to back out.
to the forward and pull in more data.
with 780 rows loaded and then on the pivot table, refresh and we get the new total.
This is the greatest thing, I have ever seen.
I cannot wait for M is for (DATA) monkey to come out.
I've pre-ordered this on Amazon.
There are all kinds of amazing tricks in power query.
It was incredibly easy to start using the power query.
But then, just all kind of amazing things that I did not realize are there.
Could we have clean this data using regular Excel?
Absolutely we gotta clean the data using regular Excel.
But then tomorrow, I have to do all of that steps, again.
Power query remembers the steps.
It is an amazing addition to Excel.
They work pro-actively made it work in Excel 2010.
In Excel 2013, it's build in and Excel 2016.
What a great way to celebrate Excel's 30th anniversary.
Go download Power Query, today.
Hey! I wanna thanks you for stopping by, we'll see you next time for another netcast from MrExcel.
MrExcel.com
Learn Excel from MrExcel podcast, episode 1956.
Power Query Changes Everything.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, today is a very special day.
September 30th, 2015 is the 30th anniversary of Excel. Happy birthday to Excel and to all of the [ inaudible ] people, who rely on Excel, every day to make our jobs easier.
This illustration here by Walter Moore.
Walter did lot of the illustrations in my MrExcel, Excel Book.
And so, I thought about today, what is most important to talk about today and I wanna talk about one of the best things I've seen here in Excel, in last 20 years and that is power query.
If you have Excel 2010 or Excel 2013, you can use power query.
Just go download it from Microsoft and Excel 2016 is right there, on the data tab.
Get and load or load and transform, or whatever crazy name, they get rid of the power query.
The name in Excel 2016 just build it all in to the data tab.
So, let me setup today's problem.
I have here, a folder of CSV files.
You see, CSV files are dumped in here by the IT department.
They are ugly, ugly CSV files.
Why they are ugly?
Well, here in column A, Someone decided, was a great idea to put customer/region, right in that column.
How horrible is that.
Totals are hard-coded instead of being in formulas and there is a run date at the bottom of every formula and I have to clean this data, not just from this file but from every single file.
Oh! And check it out.
Windows of course, puts a file in here that's not a CSV file.
Whoops! Saved my grocery list in this folder, how stupid was that.
All right! So, hey! This is [ inaudible ].
We have this folder, other stuff get saved in to the folder.
More stuff gets dumped in here, every time.
Power query is going to let us, solve this problem.
I'm gonna copy that URL, copy that folder name and we'll come back here to Excel, I've created a brand new work sheet.
And power query, I'm gonna say, I'm gonna get the data from a file, from a folder.
I'll paste the folder path in there, [ ctrl + V ] and click [ OK ].
All right! Now, just this step right here is really cool, I now have an Excel grid, there I can close and load to the Excel grid, that has a list of all the files in this folder.
Think about it, you are working on next year's budget and you have a file from every single department, you need to check of to make sure that they're all done.
You can use this trick.
But here let's clean the data little bit.
First of, let's get rid of things that are non CSV files.
So, xlsx and dot db.
get rids of my grocery list and the...
the thumbs dot db This next step is absolutely amazing, I've to tell you I've use power query for six months before and I realize that this step is the...
I learn the step.
I was looking at the pre-release copy of "M is for (DATA) MONKEY" by Ken Puls and Miguel Escobar.
This little symbol up here I never realized, I could double click this symbol, and Excel is going to go out and load the rows from every one of the CSV files, in to a single grid here.
That is absolutely amazing.
So, here is the first CSV file and then here the headings start over again.
Second CSV file, third CSV file.
Amazing, amazing tool and when I look at this...
Any time I've a word total here, I wanna get rid of that.
Any time I've a word date or the customer, region heading, I will get rid of that.
So, in power query, I can teach power query that we always wanna get rid of any rows that say customers, region.
Any row that say, run date.
Any row that say, total.
Click [ OK ] and it will delete those rows.
What about tomorrow, what if I have new customers?
It didn't say keep these lists to the customers that said delete those specific items.
All right! Customers, region, all in one column like this.
I'm gonna choose that column, I'm gonna to transform .
Actually, I can do right here on the home tab, split column by delimiter.
Let's talk about data, text to columns, you know.
You'll say, oh! Wait a second, this is power query doing things like doing in regular Excel.
That's absolutely true, is doing things you can do in regular Excel.
But here is the beautiful thing, Power Query does it better.
We've first name, last name in column A and you just split it out in to two columns.
Works great right, Bob Smith.
Bob in column A, Smith in column B but then you have Jim Jones Junior and the Junior ends up in column C.
Marry Allan Walton.
Walton ends up in column C.
Billy, Joe, Jim, Bob Smith takes up data in columns A through F.
Oh! This is beautiful.
Right here, if there is a noble prize for Excel features, this would be the one that I would nominate.
At the left most delimiter says, hey! We have a column and we're gonna split it in to two columns.
I don't care if there is 14 slashes (/) in here.
Do not fill up columns A through P, or A through N.
I just let it go to two columns, so click [ OK ].
All right! And Bam! There we go, we have customer and region, I'm gonna rename both of these columns, so this one becomes customer.
And right click, rename this one becomes region.
All right! If you ever build a pivot table from data that goes like this, you know that months stretching across through board is absolutely horrible.
I even have a grand total here, I'm gonna get rid of that grand total, so simple enough.
I'm just gonna remove that column but the months.
To get rid of the months, to un-pivot the months, well, you have to use Mike Alexander's old trick with multiple consolidation ranges or you just cut and paste, cut and paste, cut and paste, 12 times to create, 12 times of many rows, so the months go down.
Check this out, power query has a feature on transform called un-pivot I'm marking un-pivot, I'm gonna say un-pivot other columns and it will take data that was going across and it'll make it go down.
So, we rename this to be month, and this one over here, this is our sales.
I rename that column to be sales.
All right! couples of things I see that the sales were actually coming as text, they're left justified, well that's easy to fix.
Just come here and it says, currency or whole number.
In my case the sales are in whole numbers, I'm gonna choose whole numbers.
The months are text.
All right! Here we go.
Add column, a new custom column.
I'm gonna take that month name, and I'm going to add in quotes equal [ space ] 1 comma 2015.
It's 2015 and all this data is from 2015.
Click [ OK ].
All right! So, now watch this, I take that text month and that 1 comma 2015, I'm gonna merge those columns.
Separator none, new column name is gonna be called date.
Click [ OK ].
All right! We have something which looks like a date but it still text.
I choose that column and under transform, I'm gonna change type, text to date.
Bam! Just check that out.
But this point I don't need this temporary column any more and I don't even need that column any more.
I'm gonna choose both of those columns and say remove columns.
All right! Hey, look.
I'm a data analyst, I work for a long time as data analyst.
It is fun to do all of this stuff right, you discovered a new file one day and you go through all of this steps to clean the data.
Look over here on the right hand side this is the list of all the steps.
This is huge fun, i love this, is nothing better than this.
On first day, I get to do it.
But then I take this data, I create report and I hand that report to my manager and then the horrible words come out.
This is amazing.
Do it again, tomorrow.
It was fun on day 1.
It's sort of fun on day 2.
On day 400, it's the worst tragedy ever.
Here's the amazing thing about power query.
Power query remembers all of the steps, is actually building a program behind the scenes.
Advanced editor, this is a language called M.
The letter M, the 13th letter of the alphabet.
Apparently, [ inaudible ] already taken.
So, here's the M code, you don't have to know the M code.
Although, there are things you can do with the M code.
They make it even better but the important part is power query saves the data and all of the steps.
So, close and load.
My tip here is if you see data in query, it's still working.
When you see the data in green, that you know it's saved and good to go.
I'm gonna save this file.
Tomorrow, if I need to come back in, I just go to data, I go to refresh and it will go back through all of those steps.
Let me prove that to you.
We'll go to insert, pivot table.
The data is there, I'm going to go existing worksheet, right down here to the right hand side.
So, we can see it.
Click [ OK ].
I'll build little report with regions down the left hand side and say, all right! It's a worth 5.67 million dollars.
Simple enough.
Tomorrow, there's more CSV files that come from IT department.
All right! So, here's a bunch of more CSV files.
I'll take these, I drag them, I'll drop them in that folder.
Come back to Excel.
Open this file tomorrow, all I have to do come to the data tab, click [ refresh all ], that will force Excel to back out.
to the forward and pull in more data.
with 780 rows loaded and then on the pivot table, refresh and we get the new total.
This is the greatest thing, I have ever seen.
I cannot wait for M is for (DATA) monkey to come out.
I've pre-ordered this on Amazon.
There are all kinds of amazing tricks in power query.
It was incredibly easy to start using the power query.
But then, just all kind of amazing things that I did not realize are there.
Could we have clean this data using regular Excel?
Absolutely we gotta clean the data using regular Excel.
But then tomorrow, I have to do all of that steps, again.
Power query remembers the steps.
It is an amazing addition to Excel.
They work pro-actively made it work in Excel 2010.
In Excel 2013, it's build in and Excel 2016.
What a great way to celebrate Excel's 30th anniversary.
Go download Power Query, today.
Hey! I wanna thanks you for stopping by, we'll see you next time for another netcast from MrExcel.
MrExcel.com
