Power Query Examples [25 Useful examples]

Are you new to Power Query in Power BI? Here is a complete tutorial on Power query in Power BI with 25 power query examples. We will also cover:

• What is Power Query in Power BI
• Power Query Editor in Power BI
• Power BI access Power Query Editor
• Load data into Power Query Editor
• Power BI how to use power query advanced editor
• Power BI how to use power query aggregate
• Power BI how to use power query absolute value
• Power BI how to use power query based on condition
• Power BI how to use power query concatenate
• Power BI how to use power query concatenate text
• Power BI how to use power query grouping
• Power BI how to use power query left function
• Power BI how to use power query left join
• Power BI how to use power query length of string
• Power BI how to use power query right function
• Power BI how to use power query replace multiple values
• Power BI how to use power query rank
• Power BI how to use power query substring
• Power BI how to use power query trim
• Power BI how to use power query text function
• Power BI how to use power query text. contains
• Power BI how to use power query text left
• Power BI how to use power query text. combine
• Power BI how to use power query transpose
• Power BI how to use power query union
• Power BI how to use power query unique values

What is Power Query in Power BI?

Power Query in Power BI is a data transformation and data preparation tool. Power Query has a graphical user interface for retrieving data from different data sources and a Power Query Editor for applying transformations.

Power Query is available in many services and products, such as Microsoft Excel, Microsoft Power BI, Microsoft Dataverse, and many more, and the station where data is stored is based on where Power Query is used.

In Power BI, the Power Query user experience is provided through the Power Query Editor graphic user interface. The target of the interface helps us to apply transformation by simply interacting with the set of menus, ribbons, buttons, and other buttons.

When we create a new transformation step by collaborating with the components of the Power Query Editor Interface. Power Query mechanically creates the M code to do the transformation so we don`t want to put in writing the code.

There are two types of Power Query Experience available:

• Power Query Online: This type of experience you can find in Microsoft power platform dataflow, Power BI Dataflows, and many more, that provide through an online web page.
• Power Query For Desktop: This type of experience you can find in integration like Power Query for Excel and Microsoft Power BI Desktop.

Read Create table using Power Query in Power BI

Power Query Editor in Power BI

The Power Query Editor is used to edit or transform or shape the data files before the data is supplied to the Power BI.

In Power BI, the query editor serves as an intermediate data container, allowing us to manipulate data files by selecting rows and columns and performing operations such as changing values, partitioning rows and columns, pivoting and unpivoting columns, and so on.

How to access Power Query Editor in Power BI

Here we will see how to get the Power Query Editor through Power BI.

1. Login to Power BI Desktop with Office 365 credential.
2. Then In Power BI click on the Transform data.

3. As there is no data connection with Power BI, so Query Editor looks like a blank page.

This is how we can access Power Query Editor in Power BI.

Read Power BI integration with PowerApps Portals

Load data into Power Query Editor

Here we will see how to load data into Power Query Editor in Power BI Desktop.

There are two ways to load data into Power Query Editor.

• We can use the Get data option in Power BI to load the data into the Power Query editor.
• We can use the New source option in Power Query Editor to directly load the data.

For example, we will load the Financial excel data using the New source option in Power Query Editor.

Once you Load the data you can see the Navigator window, select the table and then click on Ok.

Once you Load the data, you can see the Data in the Current view of Power Query Editor.

This is how we can load the data into Power Query Editor.

Read Power BI if date + 27 Examples

How to use power query advanced editor

Here we will see how to use the Power Query Advanced editor in Power BI.

Advanced Editor in Query Editor is used as the container of your data, where you can edit your data and arrange it into the format that works best for your reporting needs on the Power BI desktop.

The Advance editor provides you with the inner working information of your data. In this editor, you can see the set of code for each step of the editing process. Whenever you edit something with your data, you can open and see it in Advanced Editor. The advanced editor uses Power Query language which is known as M- language.

Let’s say we have a below table, and we want to change the name Ron to Alex.

• In Power Query editor, go to the Home tab-> Advanced Editor from the ribbon.

• Then Advance editor window will open, and now you can change the name Ron to Alex. Then under the in write Source. Click on Done.

• Now you can see the name is changed in the table from Ron to Alex.

This is an example of how to use the advanced editor in a power query.

Read Power BI IF + 41 Examples

Power BI how to use power query aggregate

Here we will see how to aggregate columns value using the Power Query editor in Power BI.

From the below table we will aggregate the sales and profit based on each segment.

• In Power Query Editor, go to Transform tab -> select Group by option from the ribbon.

• Then the Group by the window will open. Select the Advanced option, then provide the column based on which grouping is done.
• Next, provide the aggregated column name, then select the operation and select the column
• To aggregate another column, click on Add aggregation. Then click on Ok
• So here I have aggregated the sales and profit column to get the sum of sales and profit based on each segment,

Now you can see the Total sales and Total profit based on the segment.

This is an example of how to use power query aggregate.

Read Power BI Pie Chart

How to use power query absolute value in Power BI

Here we will see how to use the Power Query editor to get the absolute value in Power BI.

We will use the M query function to calculate the absolute value of a number i.e. Number. abs().

The absolute value of null is null. And the absolute value of -4 is 4, and the absolute value of 4 is 4.

For this example, we will use the below table to calculate the absolute profit value.

• In Power Query Editor, go to Add column tab-> select the Custom column from the ribbon.

• Then Custom column window will open, and provide the name of the custom column. Provide the below custom column formula to calculate the absolute value:

Number.Abs([Profit])

Then click on Ok.

• Now you can see the absolute Profit value in the newly created custom column.

This is an example of how to use power query absolute value in Power BI.

Read Power BI Switch – DAX function

Power query concatenate

Here we will see how to use Power Query concatenate in Power BI.

For this example, we have the below table concatenate the Name and LName column using a custom column in Power Query Editor.

• In Power Query Editor, go to Add column tab, and then select the Custom column from the ribbon.

• Then the Custom column window will open, and then provide the name of the custom column, next provide the below formula to concatenate the two strings.

• Next, you can see the Name and Last name get concatenated and the output you can see in the Custom column i.e. Full name.

This is an example of how to use concatenate in a power query.

Read Power BI Card

Power query text.combine in Power BI

Here we will see how to concatenate two texts into a single text using Power Query Editor in Power BI.

To concatenate the texts we will use the Text. combine () of M query in Power Query Editor. The syntax is

Text.combine(texts as lists, separator as nullable texts(optional))

For this example, we will use the below table and we will concatenate the First name and last name. And store the result in the custom column.

• In Power Query Editor-> go to Add column tab-> select Custom column from the ribbon.

• Now Custom column window will open, provide the name of the Custom column. Then write the below formula in the formula box. Click on Ok.

Text.Combine({[First name],[Last name]}," ")

• Now you can see the new column is added to the table with the combination of the Fname and Lname column.

This is an example of how to use power query text.combine in Power BI.

Read Power BI Measure Sum and Subtract Example

How to use power bi power query grouping

Here we will how to group the data using the Power Query editor in Power BI.

We will group the values present in the various rows into one value, by grouping the rows based on the values in one or more than one columns.

For this example, we will use the below table to group the total price column based on the category column.

To group the total price value based on the category column using Power query editor.

• In the Power Query editor, go to the Transform tab and then select the Group by from the ribbon.

• Now Group by the window will open, then select the Basic option, then select the category option.
• Then provide a New column name, then choose the operation as sum, and select the column as Price. Click on OK.

• Now you can see the total price is grouped based on the category.

This is an example of how to use power query grouping in Power BI.

Read Remove blank from Power bi slicer

How to use power query left function

Here we will see how to use the Power query left function in Power BI.

The Left function is available in Excel, which in Power query is called Text.Start(). The Text.Start() in the Power query is used to extract the character from the left side based on the length of character you have provided.

There are two ways to use Text.Start() in the Power query:

1. Using the First character in Extract Function.
2. Using the Text.Start() in Custom column.

To apply the above two methods we will use the below Calendar table and we will extract the left three characters from the Weekdays column.

Using the First character in Extract Function

To extract the left three characters from the Weekdays column follow the below steps:

• In Power query editor, select the column-> then go to Add column tab.
• Then select click on Extract in the From text action-> select First characters from the option.

Then Insert First character window will open, so provide the number of characters you want to extract in the Count field. Click on Ok.

• Once you click on Ok, from first three characters are extracted in a new column.

Using the Text.Start() in Custom column

We will extract the first 3 characters by using Text.Start() in Power query editor.

• In the Power Query editor- go to Add column tab-> select the Custom column option.
• Then Custom column window will open. Provide the name to the custom column and then provide the below formula in the Formula box.

Text.Start([Weekdays],3)

• Now you can see the custom column get created with the First three characters of the Weekdays column in the Power query editor.

This is an example of how to use the power query left function in Power BI.

Read Difference between USERNAME() and USERPRINCIPALNAME() in Power BI Dax

Power BI how to use power query right function

Here we will see how to use the right function in the Power query editor.

The Right function is available in Excel, which in Power query is called Text.End(). The Text.End() in the Power query is used to extract the character from the right side based on the count of characters you have provided.

There are two ways to use Text.End() in the Power query:

1. Using the Last character in Extract Function.
2. Using the Text.End() in the Custom column

To apply the above two methods we will use the below Calendar table and we will extract the left three characters from the Weekdays column.

Using the Last character in Extract Function

To extract the last character i.e., day, from the Weekdays column using the Power Query editor in Power BI.

• In Power query editor, select the column-> then go to Add column tab.
• Then select click on Extract in the From text action-> select Last characters from the option.

• The Insert last characters window will open and provide the number of characters in the Count field, you want to retrieve from the end of the string. In this case, it is 3.

• Now you can see the last three characters are extracted i.e. day from the Weekdays column using the Power query editor.

Using the Text.Start() in Custom column

We will extract the first 3 characters by using Text.End() in Power query editor.

• In the Power Query editor- go to Add column tab-> select the Custom column option.
• Then Custom column window will open. Provide the name to the custom column and then provide the below formula in the Formula box. Then click on Ok.

Text.End([Weekdays],3)

Once you click on OK, you can see the new custom column get created with the last three characters from the weekday column in the Power query editor.

This is an example of how to use the power query right function in Power BI.

Read How to create and use Power BI Bookmarks

Power BI how to use power query length of string

Here we will see how to use the Power Query to calculate the length of the string in Power BI.

We will use the below table to calculate the length of each name present in the Full name column.

To calculate the length of the name using the M query in the Power query editor.

• In the Power Query editor, go to the Add column tab-> select the Custom column from the ribbon.
• Then custom column window will open, provide the column name Custom to Length, and in the formula box write the below formula:

Text.Length([Full name])

• Now you can see the new column with the length value of each name in the Power query.

This is an example of how to use the power query length of a string in Power BI

Read What is the difference between calculated column and measure in Power BI

How to use power query to replace values from multiple columns in Power BI

Here we will see how to replace the value from the multiple columns using the Power Query editor in Power BI.

For this example, we will use the below table and we want to replace the value 1200 from the price and the previous row column.

To replace the value from the multiple columns using the Power query editor:

• In the Power Query editor, to select both columns, press the ctrl and click on the column to select.
• Then in the Home tab click on the Replace values option.

• The Replace window will open, providing the value to find and the value to replace with.
• So here the value to find is 1200 and the value to replace is 1800.

• Now you can see the value 1200 is replaced with the value 1800 in the Power query editor.

This is an example of Power BI how to use power query to replace values from multiple columns.

Read Power bi free vs pro vs premium

Power BI how to use power query substring

Here we will see how to get the substring from the string using the Power Query editor in Power BI.

To get the substring from the string we will use the M query function i.e., Text.Range(). this function will return the substring to the range you have provided or the string found at the offset.

There are two ways to get the substring from the string in the Power query:

1. Using the Range in Extract
2. Using the Text.Range() in the Custom column

We will use the below table, to get the substring from the product id column, which means from Ak1200, we will get 1200.

Using the Range in Extract

We will use the range method from the Extract, to get the string from the ProductId column.

• In the Power Query editor, go to Add column tab-> Extract-> select Range from the option.

• The range window will open, then provide the starting index and the number of characters.

• Once you click on Ok, you can see the new column with the extracted substring.

Using the Text.Range() in the Custom column

• In Power Query Editor-> go to Add column tab-> select the Custom column from the ribbon.
• The custom column window will open, provide the Custom column name and then provide the formula in the Formula box

Text.Range([Product id],2,4)

• Now you can see the column with the returned substring from the string present in the ProductId.

This is an example of Power BI how to use power query substring.

Read How to use weekday function power bi with example

Power BI how to use power query rank

Here we will see how to add a row rank based on a different column using the Power Query in Power BI.

For example, we have 3 columns in the table i.e. Full name, department, and Event date, Department column will use for grouping and we will use the Event date to rank the rows.

Let’s see how to add a column that will rank based on the Event date and grouping is done by Department.

• In the Power Query editor, go to the Home tab-> click on the Group By from the ribbon.
• Then group by the window will open, select the Basic option, select the Department Field, provide the new Column name and select the Operations as All rows. Then click on Ok.

• Once you click on Ok, you can see something like the below image. If you can click on the table, that will show the cells with the data.

• Now what we need is an M code in the Formula bar. So we have the M code for the previous step i.e Grouping step.

Table.Group(#"Removed Columns", {"Department"}, {{"Count", each_, type table [Full name=text, Department=nullable text, Event Date=nullable date]}})

• To add the rank column, we need to edit the above code. So we will add the code after each, and the name of the column is Row Rank, and choose the ordering is ascending or descending. We will add the rank column at the very end of the code. So the new code is

Table.Group(#"Removed Columns", {"Department"}, {{"Count", each Table.AddIndexColumn(
        Table.Sort(_,{{"Event Date", 
        Order.Ascending}}), "Row Rank",1,1), type table [Full name=text, Department=nullable text, Event Date=nullable date,Row Rank=nullable number]}})

• To expand the column by clicking on the double arrow icon, and then deselect the department column from the option.

• Once you click on Ok, you can see the newly grouped row rank. Then you can rename the column by double click on the column header.

This is an example of Power BI how to use power query rank.

Read Contact your admin to enable embed code creation Power bi

Power BI how to use power query trim

Here we will see how to use the Trim feature in the Power query editor.

Trim in Power query editor is very commonly used for transformation. This feature is used to remove all spaces from the beginnings and ends of every string in the column.

We have the below table we can trim the space present in the First name column. For example, there is space in front of Ron and John.

To remove the spaces from the string of text in the column using Trim in Power query editor.

• In the Power Query editor, go to the Transform tab-> select Format-> Trim option from the ribbon.

• Now you can see all spaces are removed from the First name column in the Power query editor.

This is an example of Power BI how to use power query trim.

Read Power BI convert hours to minutes

How to use power query text.contains

Here we will see how to use Text.contains in Power query of Power BI.

The Text. contains() is used to find the text from the string. If the text is present in the string, then it returns true or else false.

Text.contains(Text, SeachText)

For this example, we will use the below table and we will check whether the Product name column contains coffee or not by using Text. contains.

Let’s follow the below steps to find the coffee from the Product column in the table.

• In the Power query editor, go to Add column tab->select the Custom column from the ribbon.
• Then custom column window will open, Provide the Column name, and then in the Formula box provide the below M code.

Text.Contains([Product],"Coffee")

• Now you can see the custom column with the boolean value and if the coffee is present then it returns true or else False.

This is an example of Power BI how to use power query text. contains.

Read Power BI convert yyyymmdd to date

Power BI how to use power query transpose

Here we will see how to use the transpose feature in the Power Query.

The Transpose feature in the Power Query editor is used to rotate the table 90 degrees, which will turn your rows into columns and columns into rows.

Let’s say we have the below table and we will transpose the below table so that it will end up with the 4 rows and 3 columns.

Let’s see how to transpose the above table:

• In the Power Query editor, go to the Transform tab-> select the Transpose from the ribbon.

• Now you can see the result table is transposed from column to row.

This is an example of Power BI how to use power query transpose.

Read Get Month Name from Month Number in Power BI

Power BI how to use power query unique values

Here we will see how to get unique values using the Power Query editor in Power BI.

For this example, we will use the below table to get the unique value from the product column. The unique value point is how many values happen only once. So we can also say by removing the duplicate value, we can get the unique values

To get the unique value using the Power query editor follow the below steps:

• In the Power Query editor, select the Product column-> in the home tab-> select the Remove rows option-> to click on the Remove duplicates from the option.

• Once you click on the Remove Duplicates value, you can see the unique values are left in the column. That means one value does not occur more than one time.

This is an example of Power BI how to use power query unique values.

I hope you have learned a lot of things from the power bi power query examples and the below points.

• Power Query Editor in Power BI
• Power BI access power query Editor
• Load data into Power Query Editor
• Power BI how to use power query advanced editor
• Power BI how to use power query aggregate
• Power BI how to use power query absolute value
• Power BI how to use power query based on condition
• Power BI how to use power query concatenate
• Power BI how to use power query concatenate text
• Power BI how to use power query grouping
• Power BI how to use power query left function
• Power BI how to use power query left join
• Power BI how to use power query length of string
• Power BI how to use power query right function
• Power BI how to use power query replace multiple values
• Power BI how to use power query rank
• Power BI how to use power query substring
• Power BI how to use power query trim
• Power BI how to use power query text function
• Power BI how to use power query text.contains
• Power BI how to use power query text left
• Power BI how to use power query text.combine
• Power BI how to use power query transpose
• Power BI how to use power query union
• Power BI how to use power query unique values

You may like the following Power BI tutorials:

• How to use Power BI sync slicers
• Power BI Calculated Column Example
• Power bi conditional column example
• Power BI Union Two Tables