Query Editor

When you use Power Query to retrieve data often need to be “purified” before they can continue to be used in the reports. For this purpose you can use a tool called Query Editor. It opens in a separate window, just like Data model, with its own ribbon menus that have a multitude of options for data processing. The following text will be talking about some basic options that allow transformation of data.

In previous „recipe“ we have shown how to, with a little help of Power Query, import data from the Internet. In particular, we were importing data on the world’s annual consumption of alcoholic beverages. Now we are going to process this data in order to determine in which country drinks the most beer.

Query Editor can be started in in several ways. First, in the panel Workbook Queries we can choose the desired query, right-click above it, and then choose Edit from the context menu. This option also resides in the window that “pops up” after being positioned over a query. Further, after creating the query, you will see the context ribbon Query, which also has the option Edit. Whichever method you choose, will be able to open Query Editor.

After you open the window, can see that it consists of several areas. At the top, like in Excel, are ribbons that hold number of options for data processing. The central part consists of data tables. On the left side there is a hidden menu Queries. Clicking on the icon with the same name could open or close the menu containing a list of all previously created queries. On the right there is a panel of Query Settings. It contains an area which is called Properties, where we can enter the new name for the query, for example. “Consumption of beer.” Also, on the lower side is the list of Applied Steps where we can see all the steps we have taken, from data import to their current content. Deleting a step performs „undo“ process, similar as found in other Windows applications.

Let’s purge some data! Since the imported table has first row as a header it would be good to emphasize that, so Excel would know how to differ header from data rows. Go to the Transform ribbon and pick menu Use First Row As Headers. It consists of two options. First, a homonymous one, confirms that first row is table header. Second option, named Use Headers As First Row, is seldom used because it requires that the table has no headers. That is, if the table already had a header that property annihilate. Let’s start first option and you’ll notice that the header is separated.

Then, it would be good to remove all unnecessary columns. Select all the columns you want to remove by clicking their header, and then from the Main ribbon open the menu Remove Columns. It consists of two options. The first one (that has same name) is used to remove selected column and second (Remove other columns) is used to remove those columns that are not selected. We’ll repeat the process until only the columns “Country” and “Beer” remain.

Column names should be changed to names in Serbian language. Select each column separately, and then from the Transform menu select Rename. We will use this option to rename columns to “Zemlja” and “Potrošnja piva“.

In most cases data will be used for some additional calculations. In this regard, it would be useful to clearly emphasize which data are text, and which represent numbers. Select the desired column, and in Transform ribbon find menu Data Type. Once you open it, you will see a multitude of data types offered. For example, if we chose the column “ Potrošnja piva,” it would be nice to emphasize that this is a decimal number. On the other hand, the column “Zemlja” should contain text data.

We performed basic transformation of data. Now you only need to choose whether to save them or cancel all previous actions. By clicking File tab will open a menu containing several options. Close&Load closes Query Editor and loads the data. Discard&Close cancels all previous actions. Choose the first option and in the worksheet will appear structured table with arranged data. Further, we can add this table to Data model or create Pivot table, a chart etc.