Blog

An Introduction to Power BI's Query Editor

An Introduction to Power BI's Query Editor

Power BI is a fantastic tool for creating visually appealing reports to analyze numerous sources of data. Report creation is often as simple as dragging and dropping if the data available is appropriately formatted. However, this is not always the case. Fortunately, Power BI contains a powerful Query Editor that allows you to transform your data into exactly the form you need. Below is a list of features that can make life easier!

To access the query editor, navigate to the home tab, and click on Edit Queries in the External Data section.

Power BI query

Renaming Columns

Renaming columns in Power BI is a best practice, as it makes working with the dataset easier. Many database columns often have longer names that only make sense within the context of the database. If so, right click on the column header, select Rename, and type in a user-friendly title! There is also a rename option under the Transform tab in the Any Column section.

Power BI columns

Creating a Custom Column

If the data points aren’t in the exact format required, creating a custom column is a great option. With this feature, you can combine columns into one, and even conditionally create new ones based on data values. For instance, below I am combining the City and State columns into one. In the second image, I am creating a column that classifies the data point as either “Large Opportunity,” “Medium Opportunity,” or “Small Opportunity” based on its amount value. To access these features, click on the Add Column tab along the top menu, and create the column that best fits your needs.

Power BI custom columnconditional column POwer Bi

Filtering Data

Depending on your needs, there may be data points that are irrelevant to the dashboard you are creating. Rather than filter them out on each visual, the Query Editor can filter them out as soon as the data gets to Power BI. Click on the arrow by the column header, and deselect the options that are not applicable to your situation.

Power BI filter

Grouping Data

While Power BI is smart enough to group data in visuals across aggregations like Sum, Average, and Maximum, the Query Editor provides an option to group the data explicitly. Simply select the grouping column, and the aggregation details and the data will group as expected.

Power BI groups

Combining Datasets

If you are pulling in similar data from multiple sources, Power BI allows you to append the tables together easily. If the tables already have the same columns, and you’re looking to operate similar SQL’s Union, you can use the append feature, seen below. If you are trying to imitate SQL’s Join, the Query Editor has an option to Merge Queries, which will work just as a Join operation would.

Power BI merging

Pulling it all Together

As you perform each one of these steps, Power BI tracks the exact changes you’ve made. You can see these changes queuing on the left of the Query Editor. If you are a more technical user, you can also view all these query modifications in the advanced editor, where you can make modifications by changing the text of the query steps themselves.

Power BI applied stepsPower BI applied steps 2

After you’ve applied all your data transformations, you’ll have a dataset ready to be morphed into a shiny dashboard!

Learn more about DMC’s Business Intelligence services.

Comments

There are currently no comments, be the first to post one.

Post a comment

Name (required)

Email (required)

CAPTCHA image
Enter the code shown above:

Related Blog Posts