Power BI Query Editor

Power BI 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.

How to Use Power BI Query Editor?

When you open Power BI Desktop under the HOME tab we can see this “Edit Queries” option.

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.

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.

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

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”.

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

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

• The first one is the “Ribbon” tab of Power Query Editor Window.

• The second one is the “Table” name of our data table.

• The third one is the data preview of the table.

• 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.

• 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.

• When you click on this it will open up the below option for you.

• From this choose only “Date” option and it will format the date & time to only “Date”.

• When this action is performed we can see this step recorded in the “Query Settings”.

• If you wish to go back to the previous step you can simply delete this step by clicking on the delete icon.

• Ok, now we need to add a new column to arrive “Profit” value. So under “Add Column” click on “Custom Column”.

• This will open the “Custom Column” window.

• Give a name for the “Custom Column” as “Profit”.

• Next, we need to apply the formula to arrive at the “Profit” value. Under the custom column formula apply the formula as shown below.

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.

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”.

• 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.

• Ok, now we will load back the newly transformed data back to Power BI Desktop. Under Home tab click on the “Close & Apply” option.

• As you click on the above option it will close out the “Power Query Editor” window and shows the applying changes message.

Now we have newly transformed data in place.

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.

Things to Remember

• Power BI Query is nothing but Power Query Editor.
• Power BI Query 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.

Recommended Articles

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 –

• Power BI GroupBy Dax
• Date Formatting in Power BI
• DAX Functions in Power BI
• Compare – Power BI vs QlikView

The post Power BI Query appeared first on Wallstreet Mojo.