Power Query is a free tool from Microsoft for Excel 2010 & Excel 2013 that lets you import imperfect data into Excel, clean it, and then later re-do the same steps with the simple click of Refresh.
Table of Contents:
The Power BI Stack 0:05
The data on a website 1:20
Power Query data from web 1:40
Choosing a Table 1:55
Cleaning the data 2:10
Removing "kts" 2:30
Removing Feet and KM using custom delimiter of "feet" 3:10
Introducing the M language 3:50
Link to Power Map video 5:10
Contrasting with regular Excel 5:43
Refresh button 6:00
Refreshing after 30 minutes 6:50
The drudge of daily steps 7:10
Final refresh 8:00
Note: This video also shows some Power Map, but it is a repeat of what I did in episode 1674:
Table of Contents:
The Power BI Stack 0:05
The data on a website 1:20
Power Query data from web 1:40
Choosing a Table 1:55
Cleaning the data 2:10
Removing "kts" 2:30
Removing Feet and KM using custom delimiter of "feet" 3:10
Introducing the M language 3:50
Link to Power Map video 5:10
Contrasting with regular Excel 5:43
Refresh button 6:00
Refreshing after 30 minutes 6:50
The drudge of daily steps 7:10
Final refresh 8:00
Note: This video also shows some Power Map, but it is a repeat of what I did in episode 1674:
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1914.
Refreshing Data With PowerQuery.
Welcome back to the MrExcel netcast, I'm Bill Jelen.
As I mentioned yesterday, I was at Tulsa, doing a Live Power Excel Seminar this week.
And one of the… one of the features that I demoed, that got a nice gasp from the audience, was Power Query.
Power Query is part of the Microsoft Power Business Intelligence stack.
I have a little chart here, that shows that stack.
You know, we start with Excel, the product you already know and love, Power Queries for getting imperfect data into Excel.
And the beautiful thing about Power Query is that it's free if you have Excel 2010 or Excel 2013.
You know, you don't need Pro Plus or anything like that.
So today I want to talk about Power Query.
I'm just going to go to a brand new worksheet here, Ctrl+End.
But to set this up, I want to get some imperfect data.
And today the imperfect data is going to come from the website.
Alright, so here I am, at flightstats.com and just looking for a flight that is on route, this is from New York LaGuardia to Chicago O'Hare, and we're looking for something that is currently in in the air, see position details.
All right, so here we have a website that's going be updated every minute.
It shows me the last two hundred positional updates for the flight and let's say, that I need to get this data into Excel.
So of course I could come here and I could select this whole table and copy, and paste, and then start to clean up the data.
But I would have to clean up the data every minute, when I refresh it.
Okay, so here is the amazing thing about Power Query.
I'm going to copy that URL, Ctrl+C, I’ll come back to Excel and on the POWER QUERY tab I'm going to say, that I want to get my data from the web (From Web).
And our webpage URL, I'm going to do Ctrl+V to paste, click OK and Power Query shows me that there are three different tables.
Which table do I need?
I don't know, but here's the beautiful thing: I just hover over table and it shows me – oh, well, that's the header – that's the data I need, right there, and then that's the footer.
So right there, Table 1, choose that and then down here, at the bottom, say that I want to edit the table.
All right, well, this is an ugly data set.
If I copied and pasted this data in, we have 3 different time columns and I really don't need that, so I'm going to choose the first one and normally in Excel I would right-click and choose Delete.
But here I'm just going say that I want to remove that column (Remove Columns).
Time At Departure, so that's a New York time, that's what I keep.
Time At Arrival, I'm going to remove that one.
Speed, look at this, how evil, they put the word “kts” for knots in every single one.
So let's see, if I can split this data.
Split Column, based on a certain number of characters (By Number of Characters) and I want to choose 3 characters, “Once as far left as possible”, click OK, and beautifully they split it into the speed and then the word “knots”.
So I'm going to choose that column and say “remove”.
Altitude, again, huge problems here, because we have different number of characters.
I can't just ask for the leftmost 5 characters.
Now, we have this word “feet” here, but check this out, this is a beautiful Text To Columns right here.
I'm going to split it by Delimiter and in the delimiter box, it's not Comma, Equal Sign, Semicolon.
It's –Custom-- and as delimiter is going to be the word “feet”, “ feet”, actually, At the left-most delimiter, click OK.
Alright, so now I actually have the Altitude and then here, again, this column will be one that I will remove (this column).
And then Latitude and Longitude, those are fine.
Okay, the amazing thing about Power Query is that all of those steps, that I just did – and they're not hard steps, any of us could have done those in Excel, you know, it's a couple of DATA-Text to Columns, right click, delete, right click, delete – you know, there's nothing amazing there.
But when I did those steps in Power Query: they wrote program code in a new language called M and that program code is remembered and stored as part of the Query.
It's certainly not necessary for you to do this, but if you were curious, you could come out here to View and Advanced Editor, and actually see the M code that was created.
All right, so we've done all the things we need to do, I'm going to click Apply & Close and the data is now loaded into Excel, right.
That's great, that's beautiful.
Now I want to pause the video and then restart the video.
It's about 3 minutes later and I've done one of my favorite tricks here, that I do with data.
The flight data is tracked in Power Map in Excel 2013, one of the other pieces of the Power BI stack.
And so, right here is LaGuardia Airport and you can see, that every minute we have a position log of that flight, I can zoom out a little bit and actually track where the flight is.
Today's video is not about Power Map, I've shown this on the podcast before, it's about Power Query.
So, let me pause again.
And we'll just kind of take a look at the other end.
So at the time that I ran the query in Power Query, the flight had spent some time over Pennsylvania and just crossed into New York, kind of heading in the direction of Buffalo, I would say.
Now, those steps that I did and the M language that was created: if I wanted to go, get an update and I was just using regular Excel: I go back to the website, I copy, I paste, I do all of those steps again.
But here is the really beautiful thing about Power Query: about 5 minutes have elapsed, since I originally did those steps.
So I'm going to go back to Excel and on the DATA tab, I'm going to choose Refresh All.
Power Query goes back out to the website, it pulls the data in, it does all of the steps that I did on the first go-round and there: we now have 6 new points.
When I go back to Power Map, all I have to do is click Refresh Data, now watch the end of the flight here.
And bam, just like that: the flight is updated.
Now, it's 10:47 am, I'm going to pause the video and we're going to come back in about half an hour and refresh.
Okay, half an hour later, back in Excel, DATA, Refresh All: more data points.
Power Map, Refresh Data… and that flight is passing over the Detroit Metro on their way to Chicago.
Time to pause again.
15 minutes later, all right, they are on descent.
Now look, the point here isn't that we're tracking some flight using the Power Map.
The point is, we do this all the time, right?
The first day that you discover how to do something, it's really cool.
You download that data, you do these steps, you do these steps, you don't write them down.
Now, the second day that you do it, you're trying to remember: okay, what steps did I do the first day.
And then, the third day you do it, it starts to become a real drudge and you have to document all the steps you do, so that way from then on out, it's just, you know, road “follow the steps and remember all the things that we did”.
But the beautiful thing about Power Query is: I don't have to remember the steps, all right?
Power Query watches the steps the first time and from there on out, it just becomes Refresh All.
And one last refresh, and we have a complete flight.
Power Query is absolutely free, if you have Excel 2010 or Excel 2013, you don't need any Pro Plus or anything special like that.
There are amazing, amazing features in Power Query, you know.
This was just ugly data, every day I'd have to refresh this, or actually every few minutes I’d have to refresh this and do a whole bunch of steps.
But thanks to the M language, we were able to just simply remember, clicking “Refresh”.
Power Query, one of the great features of the Microsoft Power Bi stack of products.
Well, hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1914.
Refreshing Data With PowerQuery.
Welcome back to the MrExcel netcast, I'm Bill Jelen.
As I mentioned yesterday, I was at Tulsa, doing a Live Power Excel Seminar this week.
And one of the… one of the features that I demoed, that got a nice gasp from the audience, was Power Query.
Power Query is part of the Microsoft Power Business Intelligence stack.
I have a little chart here, that shows that stack.
You know, we start with Excel, the product you already know and love, Power Queries for getting imperfect data into Excel.
And the beautiful thing about Power Query is that it's free if you have Excel 2010 or Excel 2013.
You know, you don't need Pro Plus or anything like that.
So today I want to talk about Power Query.
I'm just going to go to a brand new worksheet here, Ctrl+End.
But to set this up, I want to get some imperfect data.
And today the imperfect data is going to come from the website.
Alright, so here I am, at flightstats.com and just looking for a flight that is on route, this is from New York LaGuardia to Chicago O'Hare, and we're looking for something that is currently in in the air, see position details.
All right, so here we have a website that's going be updated every minute.
It shows me the last two hundred positional updates for the flight and let's say, that I need to get this data into Excel.
So of course I could come here and I could select this whole table and copy, and paste, and then start to clean up the data.
But I would have to clean up the data every minute, when I refresh it.
Okay, so here is the amazing thing about Power Query.
I'm going to copy that URL, Ctrl+C, I’ll come back to Excel and on the POWER QUERY tab I'm going to say, that I want to get my data from the web (From Web).
And our webpage URL, I'm going to do Ctrl+V to paste, click OK and Power Query shows me that there are three different tables.
Which table do I need?
I don't know, but here's the beautiful thing: I just hover over table and it shows me – oh, well, that's the header – that's the data I need, right there, and then that's the footer.
So right there, Table 1, choose that and then down here, at the bottom, say that I want to edit the table.
All right, well, this is an ugly data set.
If I copied and pasted this data in, we have 3 different time columns and I really don't need that, so I'm going to choose the first one and normally in Excel I would right-click and choose Delete.
But here I'm just going say that I want to remove that column (Remove Columns).
Time At Departure, so that's a New York time, that's what I keep.
Time At Arrival, I'm going to remove that one.
Speed, look at this, how evil, they put the word “kts” for knots in every single one.
So let's see, if I can split this data.
Split Column, based on a certain number of characters (By Number of Characters) and I want to choose 3 characters, “Once as far left as possible”, click OK, and beautifully they split it into the speed and then the word “knots”.
So I'm going to choose that column and say “remove”.
Altitude, again, huge problems here, because we have different number of characters.
I can't just ask for the leftmost 5 characters.
Now, we have this word “feet” here, but check this out, this is a beautiful Text To Columns right here.
I'm going to split it by Delimiter and in the delimiter box, it's not Comma, Equal Sign, Semicolon.
It's –Custom-- and as delimiter is going to be the word “feet”, “ feet”, actually, At the left-most delimiter, click OK.
Alright, so now I actually have the Altitude and then here, again, this column will be one that I will remove (this column).
And then Latitude and Longitude, those are fine.
Okay, the amazing thing about Power Query is that all of those steps, that I just did – and they're not hard steps, any of us could have done those in Excel, you know, it's a couple of DATA-Text to Columns, right click, delete, right click, delete – you know, there's nothing amazing there.
But when I did those steps in Power Query: they wrote program code in a new language called M and that program code is remembered and stored as part of the Query.
It's certainly not necessary for you to do this, but if you were curious, you could come out here to View and Advanced Editor, and actually see the M code that was created.
All right, so we've done all the things we need to do, I'm going to click Apply & Close and the data is now loaded into Excel, right.
That's great, that's beautiful.
Now I want to pause the video and then restart the video.
It's about 3 minutes later and I've done one of my favorite tricks here, that I do with data.
The flight data is tracked in Power Map in Excel 2013, one of the other pieces of the Power BI stack.
And so, right here is LaGuardia Airport and you can see, that every minute we have a position log of that flight, I can zoom out a little bit and actually track where the flight is.
Today's video is not about Power Map, I've shown this on the podcast before, it's about Power Query.
So, let me pause again.
And we'll just kind of take a look at the other end.
So at the time that I ran the query in Power Query, the flight had spent some time over Pennsylvania and just crossed into New York, kind of heading in the direction of Buffalo, I would say.
Now, those steps that I did and the M language that was created: if I wanted to go, get an update and I was just using regular Excel: I go back to the website, I copy, I paste, I do all of those steps again.
But here is the really beautiful thing about Power Query: about 5 minutes have elapsed, since I originally did those steps.
So I'm going to go back to Excel and on the DATA tab, I'm going to choose Refresh All.
Power Query goes back out to the website, it pulls the data in, it does all of the steps that I did on the first go-round and there: we now have 6 new points.
When I go back to Power Map, all I have to do is click Refresh Data, now watch the end of the flight here.
And bam, just like that: the flight is updated.
Now, it's 10:47 am, I'm going to pause the video and we're going to come back in about half an hour and refresh.
Okay, half an hour later, back in Excel, DATA, Refresh All: more data points.
Power Map, Refresh Data… and that flight is passing over the Detroit Metro on their way to Chicago.
Time to pause again.
15 minutes later, all right, they are on descent.
Now look, the point here isn't that we're tracking some flight using the Power Map.
The point is, we do this all the time, right?
The first day that you discover how to do something, it's really cool.
You download that data, you do these steps, you do these steps, you don't write them down.
Now, the second day that you do it, you're trying to remember: okay, what steps did I do the first day.
And then, the third day you do it, it starts to become a real drudge and you have to document all the steps you do, so that way from then on out, it's just, you know, road “follow the steps and remember all the things that we did”.
But the beautiful thing about Power Query is: I don't have to remember the steps, all right?
Power Query watches the steps the first time and from there on out, it just becomes Refresh All.
And one last refresh, and we have a complete flight.
Power Query is absolutely free, if you have Excel 2010 or Excel 2013, you don't need any Pro Plus or anything special like that.
There are amazing, amazing features in Power Query, you know.
This was just ugly data, every day I'd have to refresh this, or actually every few minutes I’d have to refresh this and do a whole bunch of steps.
But thanks to the M language, we were able to just simply remember, clicking “Refresh”.
Power Query, one of the great features of the Microsoft Power Bi stack of products.
Well, hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
