Power BI Desktop by steps #2 — Transforming data with Power Query

5 min readAug 27, 2022

If you’re into data, Microsoft Power BI is one of the must-know tools, for many reasons. In this series I want to walk through the essential features of Power BI Desktop to import, transform, and build insights on data, in a simple and practical way.

· Intro
· Importing data
· Power Query queries
  ∘ First transformation: column selection
  ∘ The essence of Power BI data transformation: built-in Vs DAX
  ∘ Pushing transformations to Data Model

Intro

After a short overview of Power BI in my story Power BI Desktop by steps #1 — Overview, I want to talk about data transformations. This is the fun part of most Data projects, where humans can hardly be replaced, as it’s in this phase in which we start to give business meaning to raw data, by simply cleansing it, or applying complex business rules.

One of the Power BI main features is the possibility to apply anything to very simple to complex data transformations. That’s a fundamental step we always need to apply when we want to generate direct data insights, or to feed other data processes. The component within Power BI to create sequences of data transformations is the “Power Query” tool.

The goal of this story is getting started with it, by introducing a way to create data transformations in Power BI.

Importing data

In this story, we will refer to the following data set, which you can download from here as a csv file, named “Customers.csv”.

Let’s import the source data from our source file, by click the Get data button, browse your file location, and then click Load to import it.

Then we want to open Power Query, by clicking the Transform data button.

Power Query queries

Data sources are seen by Power Query as queries. In our this example our import of Customers.csv, just created a query “Customers”

Of course, we can modify queries or create new ones, to add new transformations, clean data, apply business rules, just as you would do by SQL on a database.

Best practice: make sure that each query you define has a clear and self-explanatory name (e.g. Customers). You can rename it if the default name isn’t explicit enough.

Each of the Power Query’s queries will be part of our Data Model as a distinct table.

First transformation: column selection

Our first transformation will be removing columns that we don’t need from the source. This is a best practice also when writing SQL queries as well.

In Power Query we can do that in a few different ways, but here is my favourite: under the Home ribbon, click the button Choose Columns

Let’s pick all except customerName column.

Notice that on the list of Applied Steps show on the right-hand side, a step “Removed.. Columns” was added to the previous steps. At any time you can remove or modify the steps that were already created.

The essence of Power BI data transformation: built-in Vs DAX

Often we need to transform data based on an existing column, to enhance the informational content and allow more sophisticated analysis.

Simple example: we want to analyse customer behaviour based on the customer’s year of birth, but we have just a customer’s date of birth on our data source.

I usually prefer to duplicate the existing raw column and make my data transformation on the newly created column. Here we duplicate the birthDate column

here it is:

and let’s rename it in a meaningful way:

Now, we need to transform its data. In Power Query we can either do it in 2 main ways

by the built-in transformations
by DAX

For now let’s just start by the easiest way: the built-in data transformations, leaving DAX for other future articles in my series.

By just right-click on the column we want to work on, we can see multiple potential transformations.

Depending on the column’s data type we have different potential transformations

Here I want to extract the Year from the data, so I pick Year and here we go: our birthDate — Year column no longer contains the full date, but just the year.

Also notice the list of the applied steps on the right-hand side: we have more and more of them, as each action we applied to the data source (importing the source file, selecting which columns to keep, duplicate the birthDate column, renaming it, extracting the year) is listed, and can be edited or removed.

So that we can keep track of all our transformations, which is super useful when we need to summarize and document business rules.

Pushing transformations to Data Model

Once we are happy with all our brand-new data transformations, we want to propagate them to the actual data model that ultimately will feed our data visualizations, reports and dashboard.

We do so by click on the Close & Apply button. Simple, but important to do!

Now, when you go to the Data view, and you can notice that all the transformations we made are actually part of our data model.

Data view:

Model view:

If you found this article helpful, please consider leaving a tip. Thanks for reading and best of luck on your job search !
Buy me a coffee so I can do surgery for my cat : https://www.buymeacoffee.com/botservices