↑ Return to Power Query

Power Query – Automatic update

First and most interesting aspect of Power Query is the fact that the result is in a table.

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

Power_Query_Update_Link_1

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 :) )

Power_Query_Update_Link_2

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.

Power_Query_Update_Link_3


 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

Power_Query_Update_Link_4

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 …

Power_Query_Update_Link_5

Select the option Refresh data when opening the file 

Power_Query_Update_Link_6

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 :)

Power_Query_Update_Link_7
This time, the same query has returned 222 rows. Prove that the Excel workbook is linked to the csv file.

Conclusion

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>