FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Power BI Query

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

How to use Power BI Query Editor?

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

This article 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 the “Power Query Editor” window. With no data uploaded to Power BI, we can see a window like the one below.
Power BI Query (Power Query Editor)

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

We will upload the data below to the Power BI Desktop through Excel Workbook. So, you can download the workbook and upload the same to follow along with us.

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

–>> If you want to learn Power BI professionally, then Power BI Basics Course (5+ hours) is the perfect solution. This course will introduce you to a wide range of Power BI concepts like collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights, Creating Reports in Power BI Desktop, Analyze Data with Excel, Graphs & Visualizations, Interactive Dashboards along with some real life examples.

Power BI Query (Excel Data)

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

Power BI Query (Data)

Now, in this data, we have a date column that includes time, but we do not want to see this time. We do not have the “Profit” column, which is arrived at using the formula: Sales – Cost.

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

  • Go to the “Home” tab and click “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 is the “Ribbon” tab.
Power BI Query (Ribbon Tab)
  • The second is the “Table” name of our data table.
Power BI Query (Table Name)
  • The third is the data preview of the table.
Power BI Query (Data preview)
  • The fourth is the “Query Settings” window. 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.
Power BI Query (Different formats)
  • From this, choose only the “Date” option. It will format the date and 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 return to the previous step, you can delete this step by clicking on the “Delete” icon.
Power BI Query (Go back to previous)
  • We must add a new column to arrive at the “Profit” value. So under “Add Column,” click on “Custom Column.”
Power BI Query (Custom Column)
  • It will open the “Custom Column” window.
Custom Column window
  • Give a name for the “Custom Column” as “Profit.”
Custom Column Name
  • Next, we must apply the formula to determine the “Profit” value. First, under the “Custom Column” formula, apply the formula as shown below.
Profit Formula

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

  • Now, click on “OK.” We can see the new column in the data table.
Power BI Query (Profit Column)

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

Index Column
  • If you want the index numbers to have appeared from 0, then choose “From 0.” If you want the index numbers to have appeared from 1, then choose “From 1”. We have chosen “From 1,” and we got below the new column.
Index 1
  • We will load the newly transformed data back to Power BI Desktop. Then, under the “Home” tab, click on the “Close & Apply” option.
Close & Apply
  • As you click on the above option, it will close the window and show 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: We can also download Power BI Query Editor file from the link below and view the final output.

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

Things to Remember

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

This article is a guide to Power BI Query. Here, we discuss using the Power BI query editor to transform the data that meets users’ needs, along with practical examples. You can learn more about Power BI from the following articles: –