Power Query

Power Query is an Excel add-in that is used to retrieve data from various sources, connecting to them and purging data before we use them to create a report. It manipulates data in a worksheet, managed within structured table, and allows directly adding them the model. First release of Power Query was an optional add-in for Excel. In version 2016 it has become an integral part of the application. This technology is embedded in PowerBI service.

We approach Power Query from Data ribbon. Creation of new query can be done on the basis of existing data in the workbook (From Table) or by activation of previously created connection (Recent Sources). However, in most cases we will create a brand new query. In order to do this it is necessary to open the menu and select New Query, and then choose a data source.. Power Query supports a variety of data sources, divided into four categories: files (From File) databases (From Database), Microsoft cloud services (From Azure) and data from other sources (From Other Sources), which may be other applications or other locations on the network. One of its interesting features is that Power Query can retrieve data from the Internet, choosing one of publicly available data sources.In this post we’ll use example containing data about the analysis of the world’s annual consumption of alcoholic beverages. For this purpose, I found the link:

https://en.wikipedia.org/wiki/List_of_countries_by_alcohol_consumption_per_capita.

To we have taken it is necessary to first go to the Data toolbar, then to open the menu New Query / Other Sources and select From Web. The window in which it is necessary to enter the link above. Then, it opens another window in which we confirm that we want to import data from that particular link. In the end, a dialog window for importing data. To his left we can see a list of tables that Excel found on a given link. Among them should find one that contains data on energy drinks. The content of the selected table can be seen on the right side of the window. To import data first we should go to Data ribbon, and then open menu  New Query/Other Sources, then choose option From Web. This action opens a window where should enter a link I previously mentioned. At the end, dialog box for data import will appear. On its left side you can see a list of tables Excel have found in given link. Amongst them should fine the one that contains data about consumption of beverages. The content of the selected table can be seen on the right side of the window.

After selecting the table we choose what we do with the data. In the lower right corner of the window there is a menu Load containing two options: Load and Load To. By choosing the first option, the data is imported directly into structured table to a new worksheet, and by selecting second option we can determine whether data are imported into an existing or a new worksheet, and whether we want add them directly into Data model. By clicking on Edit enter the Query Editor, a tool for data processing which is similar to the Model, and opens in a separate window. By clicking the Cancel you cancel the import process.

Select Load. Data from the table will be added in the structured table in a new worksheet. At the same time, on the right side opens panel named Workbook Queries. In this panel can see all the „questions“ that we have made so far. The title of each of them shows the number of row taken from a source of data. On the right is an icon that is used for refreshing data.

If you are positioned above the query window opens in which we see that some information contains, as well as several other features of queries …

If we position above query a window will open that will show us contained data, as well as several others query properties.