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.
By renaming these steps, we can make this segment of Power Query more informative.
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.
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
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:
PowerQueryFormatter.com
The output looks neat and more legible.
With a few simple tweaks and modifications, we can enhance the experience of Power Query.
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:
