Data transformation

After importing data there is often a need to rearrange or update them so that we get adequate inputs on the basis of which we can make reports. Previously, to perform that, we used traditional Excel functions, and a few years ago an Excel add-in called PowerQuery was released. It offers the possibility of obtaining data from a variety of sources and the possibility of transforming them. Good News: PowerQuery is integrated in PowerBI!

We run it with the Edit Queries option, which is easily visible in the Home ribbon. We can access it immediately after importing the data or any time later, there are no strict rules … After the start Query Editor window will open in which we make data transformations. Let’s see what it consists of!

On the left side of the window you will see a panel called Queries. It contains a list of all queries in the Model. Each query represents a single table that was imported, and by clicking on the query name, the tables in the central part of the window will be changed. There we see the exact data we are transforming. In the central part of the window there is a table that resembles a structured Excel table. In a similar way to Excel, we can select columns, change their layout, filter content, sort them according to given criteria … On the right side there is a panel called Query Settings and consists of two parts. Properties is a field in which we can change the query name, and hence the name of the table in the Data Model. Applied Steps is a list where we can see the history of data modifications. Deleting modifications returns transformation process one or more steps backwards, similar when we chose the Undo option in Excel. In the status line, we can see several useful data, for example, the number of rows and columns in the table. At the top of the window there are ribbons, as in common Windows applications. They are called: File, Home, Transform, Add Column, and View, and they contain a number of options for manipulating data. The File card is actually a shortcut to the menu where there are several useful options. The most important are: Close & Apply, Apply and Close. The first one closes the window and applies all the transformations we’ve made to the data, the other only applies the changes, and the third one is used to exit the window without changing the data.

Query Editor offers many options for transforming data. First, we can determine if the first line in the table is its header. Then, we can delete rows and columns or keep the selected ones (and we delete all others). We can merge or split columns, search or change values in them, change the type of data, add calculated columns … Simply put, everything that you previously worked in Excel to transform data using the formulas now you can perform by choosing one of many offered options.