Power Query is a powerful tool in data transformation. After understanding about the ribbons and panes found in the Query Editor, the next step is getting acquainted with its various features.
In this tutorial, we’ll go over a quick overview on the different Power Query Editor tools that’ll assist you in organizing your data.
Power Query Tool #1: Advanced Editor
Under the Home tab, you can see the Advanced Editor option.
When clicked, it opens the Advanced Editor window.
At the top left corner of this window, opposite to the query name, you’ll find the Display Options drop-down menu and the question mark.
When you click the question mark, it opens a standard browser that takes you straight to the Power Query M function reference.
This is especially convenient if you want to look up available functions or explore how they work. The Advanced Editor also lets you choose the display option for the M code. When you click Display Options, you can choose between four displays.
If you choose to display line numbers, for example, the M code generated by the Advanced Editor will be written in a single line.
However, this display option will require you to scroll frequently unless you enable the word wrap option. When you enable this, the M code will be automatically indented and split into multiple lines.
You can’t adjust the font size in the Advanced Editor window. However, you can zoom in and out by pressing Ctrl+Shift together with either the plus sign to zoom in or the minus sign to zoom out. Press Ctrl+0 to restore the default zoom percent. These zoom options also work in the Query Editor.
Power Query Tool #2: Data Profile
Go to the View tab and focus on the Data Preview section.
By default, column profiling is only performed on the top one thousand rows. In this example, even if the profile options are disabled, you can already see a snippet of the column quality using the green line directly under the column header.
At the bottom right of the preview, there’s an ellipsis. If you click on it, you’ll get access to a menu with options that allow you to immediately start working on your data.
If you enable the Column Quality option, a new row appears under the column headers. This row shows the same information as when you hover over the green line.
If you enable the Column Distribution option, another row will appear.
This shows the number of distinct and unique values per column. It can also help determine the consistency of the data.
At a quick glance, you can see if the data is evenly distributed. This makes it easier to identify problems within your data that you need to sort before proceeding with your work.
Next, if you enable the Column Profile option, you’ll see the column statistics and value distribution of your data found at the bottom.
Aside from the figures you’ve already seen so far, this option offers a more detailed explanation depending on your data type.
Moreover, if you click on the ellipsis found at the top-right corner of the value distribution section, you can change the group by setting.
Again, the options of the ellipsis menu change depending on the data type. If you select a text column, you’ll get a different set of group by options.
Power Query Tool #3: Query Dependency View
Click the Query Dependency button under the View tab.
A new window then appears showing the flow of data from one query to the next.
If you click on a query, all of its associated queries get highlighted.
This makes it easier to follow through errors in your report. You can also see the load destination of your queries.
Depending on your personal preference, you can switch between views by clicking the drop-down menu at the bottom.
If you want to delete a query with dependencies, you’ll get a notification listing all the downstream queries of the one you want to delete.
Power Query will never allow you to delete a query with dependencies.
However, if you do need to delete a query with dependencies, you have two options. First, you can delete the downstream queries. Second, you can change their data source so that they’re no longer dependent on the query you want to delete.
***** Related Links *****
Power BI Parameters Via Query Editor
Query Editor In Power BI | An Overview
Power BI Query Editor: Row & Column Transformations
Conclusion
Before learning about queries and M codes, it’s important to first be acquainted with the features in the Query Editor and how they work.
By understanding how the query editor is organized along with its features, it will be easier for you to navigate the program as you work. The shortcuts and quick access options will also help you work more efficiently.
Melissa
