Power query is available in both MS Excel and Power BI. In MS Excel, this feature is primarily used for data cleaning and preparation (note that data size limitations exist) before loading the data into an Excel spreadsheet for analysis and reporting. However, in Power BI it is an essential and an integrated component of the Power BI workflow. It’s the first step in creating a data model (large datasets) for visualizations and dashboards on Power BI. Additionally, Power Query for MS Excel is often a few versions behind the Power BI desktop version.

In order to test out the following features, the freely available Power BI desktop version is sufficient. Assuming that you have already installed the app, from the start menu click on Power BI Desktop icon to start the Power BI application. If you are new to Power BI, read this blog post before going through this blog. A few of the useful features of PQ editor for Power BI is as follows.

Change Datatypes

Since the Power BI datasets are large, it is a good practice to set your data types as early as possible. Either you can navigate to each of the columns and change the data type as shown in the below picture or select all the columns in the PQ editor to change the data type at once using the Transform > Detect data type option.

Create and transform columns

In the process of data cleaning, you will need to remove the white spaces in many of the columns with random values. To do this, just navigate to Transform > Format > Trim. The trim option will remove the white spaces in a single click.

To add a new column in Power BI that calculates the length of text in an existing column, select the column and navigate to Transform > Extract > Length. Just a click on the Length option creates a new length column with the length values for each row.

A conditional column in Power BI allows you to create new columns based on conditions applied to existing columns. This feature is useful for categorizing data, creating flags, or deriving new values based on specific criteria within your dataset. After selecting the existing column, navigate to Add column > Conditional column to create a new conditional column based on the conditions specified.

Reference vs Duplicate

Duplicating creates an independent copy, where changes in the original query don’t affect the duplicate, and vice versa. Referencing creates a link, where changes in the original query are reflected in the referenced query. If you want to duplicate or reference a query, just right click on the query to select the desired action as shown in the picture below.

Append and Merge Queries

Append queries adds rows from one table to another with the same number of columns, essentially stacking them vertically. On the other hand, merge queries can combine tables with different number of columns based on a common column. This is conceptually similar to VLOOKUP function in MS Excel.

Primary or Foreign Keys

Primary keys and Foreign keys are fundamental for creating relationships between tables, enabling efficient data modeling and accurate analysis. A primary key is a unique identifier for each record in a table. In a database, this is a column (or a combination of columns) that contains unique values and cannot contain NULLs. A foreign key is a column or a combination of columns in one table that establishes a link between data in two tables. It typically refers to the primary key in another table.

When you import data into Power BI, the columns that uniquely identify rows in your data tables are effectively acting as primary keys. While relationships between tables in Power BI are often established using what are conceptually foreign keys.

Configure Load Options

The option ‘Enable load’ can be found by right-clicking on the table. Typically, by default, the load is already enabled. You can refer to the above Reference vs Duplicate section to view the option in the screenshot. Disabling the load means the query/table won’t be loaded into the front-end report. However, these helper queries will work behind the scenes. By disabling the load, you keep your model size small and make it efficient.

Further Reading :