Clicks or code? Power Query offers a rich set of features, that allow to transform the data without writing a single line of code. Even if you want to focus primarily on coding skills, it is still good to know what UI has to offer.

When talking about Power Query for Power BI, we have two experiences available:

• Desktop – Allows to transform and load or connect to the data required to build reports.
• Dataflows – Allows to build reusable data entities and share them across multiple semantic models.

There is already some complexity I must highlight here. I could say that there are two places where Power Query experience is available. Instead, I mentioned that there are two experiences available. It’s because both solutions are not exactly the same. Dataflows were introduced later, and they are equipped with a bunch of nice features, that Power Query in Desktop experience is lacking. There is a commitment from the product team the same, however, for now we must know the difference.

Not to mention that within Microsoft Fabric (so no longer Power BI related only) there is another Power Experience – Dataflows Gen 2, which are again slightly different. I will cover this topic in separate articles.

It is impossible to cover all the features of Power Query in single article. That’s why I will cover only high-level overview, to help you navigate inside of this great tool. Without further ado, let’s start with today’s topic – desktop experience.

How to open Power Query editor

Building blocks of Power Query

You did the first step, and you are in Power Query, what next? Let’s have a look what hides inside:

1. Data Preview – at the very center you can see the preview of your data. By default, you will see a thousand rows here.
2. Ribbon – here you can find all the features and settings available through UI. I will cover them a bit later.
3. Queries page – Naming here is a bit tricky, but correct. Very often people tend to say that this is list of tables loaded to the model. Whatever we do here, there is a Query generated in the backend using M language. So, the queries list covers all data structures (tables, lists, records, scalar values), parameters, functions. In short, whatever we need to prepare the tables to load them to the model.
4. Querry Settings – Details of currently selected query in Queries pane. Here we have easy access to query properties, and (most importantly) list of all the transformation steps applied in selected query.
5. Status Bar – a bit hidden but important section. It shows some statistics like number of columns and rows (rows limited to sample size – 1000 rows). Most importantly, you will find here the processing status and warnings if something is wrong with your query. Unfortunately, no warning is displayed if problem appears outside of sample loaded as data preview.

Data Preview

This is basically a graphical representation of your Query. Whatever transformation you use, Power Query recalculates the results and present them as Preview of the final result. For many Power Query beginners, that concept of the Data Preview is hard to understand. Many of them try to select a row and remove it, or they ask how the row order can be changed or modify a value in specific cell. So, the only way to remove rows here is to apply filters or use Keep/Remove Rows function. The last one must be used wisely, as you can’t assume that your data preview will look always the same, unless you can force that behavior using other functions. However, the thing that is more or less intuitive for excel users is a Column of Data. This is actually an object that you can select, move around and remove.

Each column has a header and data type associated with it. You can also display additional information for each column, by enabling specific settings in View tab in ribbon. You can either right click on specific column to quickly discover useful column level transformation, or click on a small icon in top-left corner of the table, to discover table level transformations:

Therefore, if we talk about only what is available through User Interface, to apply any change to your data on a row level, you must apply a column level or table level transformation. 

 

It is important to mention here, that technically speaking, tables that we see in Power Query are not made of Rows and Columns but Records. If you select a specific row of data, what you see at the bottom of Data Preview is a Record:

You can say that a Record is a list of fields, where each field is a name/value pair. The field names are nothing else but Column headers, and field values are simply values from column for selected row. I mentioned before, that staying in the world of UI there are no row level transformations. There are record level transformations, but they are available through coding only.

Coming back to Columns topic, column headers have couple of “hidden” features. As mentioned above, you can right-click on column header to quickly find some of the column level transformations, but that’s not all you can do with column headers:

• • You can click on the symbol to the left of the column name to change the data type of that column.
  • You can double-click on the name column name to rename it
  • You can click on arrow to the right, to perform sorting or filtering operations

I also mentioned that you can use Data Preview to quickly get useful insights on the column content:

1. Column Quality – Valid row is a row with data and without errors. Error indicates issues with at least one applied transformation. Empty rows hold no data. It is important to mention, that no data for Power Query is represented by null value. Blank is an empty string, which for Power Query is a value.
2. Column Distribution – Quick graphical overview of the uniqueness of the data. Not sure if there is a lot of added value in column chart, but the summary with number of distinct and number of unique values is already pretty useful.
3. Column Profile – Wider column statistics, including information available in Column Distribution feature. Here, value distribution chart is useful, as we at least see the category related to the bar length. On top of that, you can select on specific bar, and quickly apply filter operations: