Principe of Power Query
When you import your data with Power Query in Excel, you will create a query.
A query is, in the Power Query language, a program.
Most of the people create a VBA program to import and also manipulate the data. But this Power Query, you can easily specify the step and the task to do before the import and Power Query will automatically convert your manipulation in a code.
Result in an Excel’s table
Whatever the manipulate you need to do on your data, the final result, with Power Query, will be always inside a table. And this is really important. When your data are in a table, this means you have create a connection between your source and your Excel workbook.
When your data is in a table, it will be refreshed automatically.
Example with a csv file
Let’s start with this standard csv file with the Microsoft stock prices. Download it and rename it with the name MSFT.csv To Import the file with Power Query, you have to go in the menu Power Query > From File > From CSV
In the dialog box, you select your file and when you validate. A new Power Query windows opens. It’s in this interface that you will manipulate your data. But what is very interesting to notice, is that Power Query has already detect the column delimiter and presents the final result of the import (Yeeepppiiiii :) )
As you can see in this picture, your columns are automatically detected. In the right pane you can see that Power Query has automatically create 3 steps.
To export this result in Excel, you have to click on the icon Apply & Close on the top left corner and the contain of the csv file is loaded in Excel IN A TABLE.
For the rest of the demo, it is important to notice that you have import 203 row.
Refresh the connection
Because your data is in a table, your table is link to your source of data. When you will receive a new csv file with the same name, in the same folder, you just have to refresh the query to update your workbook or you can refresh automatically your table when you open your workbook. To do that, you have go to the menu Data > Connections
In the dialog box Workbook connections, you select one of the connections of your workbooks (here there is only one connection) and you click on Properties …
Select the option Refresh data when opening the file
Don’t forget to save and close your file.
Change the source
Have you close your Excel file with the new connection parameter ? Sure ? Ok, let’s go further.
So now, you download this new csv file and you rename it exactly like the first file (MSFT.csv) in the same folder. Then you reopen your workbook. And …. And ……………….. And …. !!!!! Automatically, your data is refreshing with the new source :)
With Power Query, when you import for the first time a file (csv, txt, Excel, …), the program records for you all the step you want. And also, Power Query create a relationship with your source and your workbook. Like that, each time you re-open your workbook, the update is done automatically.