A Quick Overview On Different Power Query Tools In Power BI - Enterprise DNA

A Quick Overview On Different Power Query Tools In Power BI

One comment

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.

Power Query tool

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.

Power Query tool

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.

Power Query tool

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.

Power Query tool

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.

Power Query tool

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.

Power Query tool

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.

Power Query tool

If you enable the Column Distribution option, another row will appear.

Power Query tool

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.

Power Query tool

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.

Power Query tool

A new window then appears showing the flow of data from one query to the next.

Power Query tool

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

Enterprise DNA Power BI On-Demand

1 comments on “A Quick Overview On Different Power Query Tools In Power BI”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.