Power BI Query

How to use Power BI Query Editor?

Query Editor is the tool available with Power BI Desktop to transform the data, shape data structure to meet the needs of the users, and after transforming the data we can load back to the Power BI Desktop to start working with the transformed data.

In this article, we will show you how to use the “Power BI Query” model to the full extent.

  • When you open Power BI Desktop under the HOME tab we can see this “Edit Queries” option.
Power BI Query (Edit Queries)
  • When you click on this option it will take you to “Power Query Editor” window, with no data uploaded to Power BI we can see a window like the below one.
Power BI Query (Power Query Editor)

But without data, we cannot do anything with “Power Query Editor”. Ok, let’s upload some data and see how to use this window.

I am going to upload the below data to the Power BI Desktop through Excel Workbook. You can download the workbook and upload the same to follow along with me.

You can download this Power BI Query Excel Template here – Power BI Query Excel Template
Power BI Query (Excel Data)

After uploading the data we can see the same in “Data Layout”.

Power BI Query (Data)

Now in this data, we have a date column which includes time as well but we don’t want to see this time and we don’t have the “Profit” column which is arrived by using the formula “Sales – Cost”.

So, all the data transformation is done by using “Query Editor” in Power BI.

  • Go to the “HOME” tab and click on “Edit Queries”.
Power BI Query (Home - edit queries)

When you click on this option it will take you to the “Power Query Editor” window, with uploaded data.

In the “Power Query Editor” window we have 4 common options.

  • The first one is the “Ribbon” tab.
Power BI Query (Ribbon Tab)
  • The second one is the “Table” name of our data table.
Power BI Query (Table Name)
  • The third one is the data preview of the table.
Power BI Query (Data preview)
  • The fourth one is the “Query Setting” window and in this, we can cancel the changes we make before uploading the data back to Power BI Desktop.
Power BI Query (Query Settings)
  • Our first requirement of the data transformation is to change the “Date” format of the column “Date”, so select the column and click on the “Format” option.
Power bi query (Format option)
  • When you click on this it will open up the below option for you.
Power BI Query (Different formats)
  • From this choose only “Date” option and it will format the date & time to only “Date”.
Power BI Query (Date Option)
  • When this action is performed we can see this step recorded in the “Query Settings”.
Power BI Query (Changed Type)
  • If you wish to go back to the previous step you can simply delete this step by clicking on the delete icon.
Power BI Query (Go back to previous)
  • Ok, now we need to add a new column to arrive “Profit” value. So under “Add Column” click on “Custom Column”.
Power BI Query (Custom Column)
  • This will open the “Custom Column” window.
Custom Column window
  • Give a name for the “Custom Column” as “Profit”.
Custom Column Name
  • Next, we need to apply the formula to arrive at the “Profit” value. Under the custom column formula apply the formula as shown below.
Profit Formula

Note: Double click on column names to insert to the formula field.

  • Now click on “Ok” and we will see this new column in the data table.
Power BI Query (Profit Column)

So, the column which was not there earlier in the data table has been added to the data table.

  • Now assume we need to insert “Serial Number” to have index numbers. Under the same “Add Column” tab click on the drop-down list of “Index Column”.
Index Column
  • If you want the index numbers to have appeared from 0 then choose “From 0” or if you want the index numbers to have appeared from 1 then choose “From 1”. I have chosen “From 1” and I got below the new column.
Index 1
  • Ok, now we will load back the newly transformed data back to Power BI Desktop. Under Home tab click on the “Close & Apply” option.
Close & Apply
  • As you click on the above option it will close out the window and shows the applying changes message.
Apply query changes

Now we have newly transformed data in place.

Power BI Query (Transformed Data)

Like this using “Power BI Query,” we can transform the data.

Note: Power BI Query Editor file can also be downloaded from the link below and the final output can be viewed.

You can download this Power BI Query Template here – Power BI Query Template

Things to Remember

  • It is nothing but Power Query Editor.
  • It is used to transform the data.
  • There are plenty of options available in the Power BI Query model so according to the requirement of the data structure, we can make use of them in Power BI.

This has been a guide to Power BI Query. Here we discuss how to use the Power BI query editor to transform the data which meets the needs of users along with practical examples. You can learn more about Power BI from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>