Three Tips for a Better Experience – Power Query

Power Query is a powerful ETL tool for Excel and Power BI. It has the capability of connecting with multiple data sources and easy-to-use data transformations tools. This article shows three tips to enhance the overall experience.

Rename Applied Steps

For me, Applied Steps under Query Settings is the most crucial aspect of the Power Query table. It is a snapshot of each transformation performed on the table. For each step, Power Query assigns a generic name, which essentially is the name of the applied change.

Three Tips for a Better Experience – Power Query

By renaming these steps, we can make this segment of Power Query more informative.

Three Tips for a Better Experience – Power Query

How to rename steps?

It is simple – Select the step > Use the key F2

Alternatively, you can right-click on the steps > Rename

We can add more details using Properties. It provides additional information when we hover on the step.

Enable Formula Bar

The formula bar is where we can see the M code for each transformation.

Three Tips for a Better Experience – Power Query

We can use it for a few quick modifications and reduce the number of Applied Steps in a query.

In the image below, we are eliminating the steps of renaming a column by modifying the M code in the formula bar:

Apart from this, it also helps in getting familiar with the M query.

To view the formula bar, go to View > Check the option Formula Bar

Using Power Query Formatter

Formula Editor displays the M code for one step. With Advance Editor, we can view all the M codes applied on the table:

Home > Advanced Editor

Three Tips for a Better Experience – Power Query

The M code makes it difficult to read due to the lack of proper structure even with using the Word-wrap feature:

Power Query Formatter provides a neat solution for this. Just copy the entire code from the Advanced Editor, paste it to the PowerQueryformatter.com and format it with one click:

Three Tips for a Better Experience – Power Query

PowerQueryFormatter.com

The output looks neat and more legible.

Three Tips for a Better Experience – Power Query

With a few simple tweaks and modifications, we can enhance the experience of Power Query.

@imvivran

About the Author:

Vivek is a data enthusiast who works on Excel, Power BI, Power Apps, SQL Server, and SharePoint. He is mostly a self-taught person and loves to share his knowledge. This inspired him to leave his full-time job and start working as a BI Consultant & Trainer. He first saw Power BI approx. five years ago, and it was love at first sight. He prefers to upgrade himself to the latest BI technologies and best practices during his free time, writes blogs, and answer questions on the Power BI community. He loves traveling and has covered over 25,000 kilometers on a motorcycle with his wife in India. He loves trekking Himalayas, an avid reader (primarily books, but blogs also do), an amateur photographer, and plays guitar. Apart from this, he is quite a dull person.

Reference:

Ranjan, V. (2021). Three Tips for a Better Experience – Power Query. Available at: https://www.vivran.in/post/three-tips-for-a-better-experience-power-query [Accessed: 1st April 2021]

Share this on...

Rate this Post:

Share:

You might also like ...