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:
The output looks neat and more legible.
With a few simple tweaks and modifications, we can enhance the experience of Power Query.