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.
Power Query Examples
Power Query Examples

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 plays an intermediate data container role, where we perform actions to modify the data files by selecting rows and columns, such as replacing values, splitting rows and columns, pivoting and unpivoting columns, etc.

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.
power bi access power query Editor
power bi access power query Editor

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

How do i access power query Editor
How do I access power query editor

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.

Load the data using Power Query Editor
Load the data using Power Query Editor

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

How do i load the data into Power Query Editor
How do i load the data into Power Query Editor

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

In Power BI load data into Query editor
In Power BI load data into the 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.

Power Bi  Advanced Editor in Query editor
Power Bi Advanced Editor in Query editor.
  • In Power Query editor, go to the Home tab-> Advanced Editor from the ribbon.
Power BI how to use power query add row 1
  • 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.
Power BI  how to use power query advanced editor
Power BI how to use power query advanced editor
  • Now you can see the name is changed in the table from Ron to Alex.
In Power BI  how to use power query advanced editor
In Power BI how to use power query advanced editor

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.

Aggregate the table in Power Query editor
Aggregate the table in Power Query editor
  • In Power Query Editor, go to Transform tab -> select Group by option from the ribbon.
Aggregated data in Power Query editor
Aggregated data in Power Query editor
  • 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,
Aggregated data using Power Query editor in Power BI
Aggregated data using the Power Query editor in Power BI

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

In Power BI Aggregated data using Power Query editor
In Power BI Aggregated data using the Power Query editor

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.

Power BI  how to use power query absolute value
Power BI how to use power query absolute value
  • In Power Query Editor, go to Add column tab-> select the Custom column from the ribbon.
In Power BI  how to use power query absolute value
In Power BI how to use power query absolute value
  • 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.

how to use power query absolute value in Power BI
how to use power query absolute value in Power BI
  • Now you can see the absolute Profit value in the newly created custom column.
use power query absolute value in Power BI
use power query absolute value in Power BI

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.

power query concatenate
power query concatenate
  • In Power Query Editor, go to Add column tab, and then select the Custom column from the ribbon.
In Power BI  how to use power query concatenate
In Power BI how to use power query concatenate
  • 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.
power query concatenate columns
how to use power query concatenate in Power BI
  • 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.
use power query concatenate in Power BI
use power query concatenates in Power BI

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.

Power BI  how to use power query concatenate text
Power BI how to use power query concatenate text
  • In Power Query Editor-> go to Add column tab-> select Custom column from the ribbon.
In Power BI  how to use power query concatenate text
In Power BI how to use power query to concatenate text
  • 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]}," ")
Power BI  how to use power query text.combine
how to use power query concatenate text in Power BI
  • Now you can see the new column is added to the table with the combination of the Fname and Lname column.
use power query concatenate text in Power BI
use power query to concatenate text in Power BI

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.

power query grouping
Power BI how to use power query grouping

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.
In Power BI  how to use power query grouping
In Power BI how to use power query grouping
  • 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.
how to use power query grouping
how to use power query grouping
  • Now you can see the total price is grouped based on the category.
how to use power query for grouping
how to use power query for grouping

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.

Use left function in Power query
Use the left function in Power query

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.
Use left function in query editor
Use the left function in query editor

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

 left function in query editor
left function in the query editor
  • Once you click on Ok, from first three characters are extracted in a new column.
How to use left function in query editor
Power BI how to use power query text left

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)
How do you use the left function in Power Query
How do you use the left function in Power Query
  • Now you can see the custom column get created with the First three characters of the Weekdays column in the Power query editor.
How do you use the left function in Power Query Editor
How do you use the left function in 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.

How do you use the right function in Power Query Editor
How do you use the right function in Power Query Editor

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.
 use the right function in Power Query Editor
use the right function in Power Query Editor
  • 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.
Power query text.end
Power query text.end
  • Now you can see the last three characters are extracted i.e. day from the Weekdays column using the Power query editor.
in Power Query Editor use the right function
in Power Query Editor use the right function

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)
 Power Query Editor use the right function
Power Query Editor use the right function

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.

Power BI how to use power query right function
Power BI how to use power query right function

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.

Power BI  how to use power query length of string
Power BI how to use power query length of string

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])
how to use power query length of string
how to use power query length of string
  • Now you can see the new column with the length value of each name in the Power query.
how to use power query to get length of string
how to use power query to get length of string

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.

Power BI  how to use power query to replace values from multiple columns
Power BI how to use power query to replace values from multiple columns

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.
Microsoft Power BI  how to use power query to replace values from multiple columns
Microsoft Power BI how to use power query to replace values from multiple columns
  • 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.
how to use power query to replace values from multiple columns 1
how to use power query to replace values from multiple columns
  • Now you can see the value 1200 is replaced with the value 1800 in the Power query editor.
use power query to replace values from multiple columns
use power query to replace values from multiple columns

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.

Power BI how to use power query substring
Power BI how to use power query substring

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.
In Power BI how to use power query substring
In Power BI how to use power query substring
  • The range window will open, then provide the starting index and the number of characters.
how to use power query substring
how to use power query substring
  • Once you click on Ok, you can see the new column with the extracted substring.
how to use power query substring in Power Bi
how to use power query substring in Power Bi

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)
how to use power query to get the substring
how to use the power query to get the substring
  • Now you can see the column with the returned substring from the string present in the ProductId.
use power query to get the substring
use power query to get the substring

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.

Power BI  how to use power query rank
Power BI how to use power query rank

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.
In Power BI  how to use power query rank
In Power BI how to use power query rank
  • 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.
how to use power query rank in Power BI
how to use power query rank in Power BI
  • 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.
how to use power query rank
how to use power query rank
  • 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.
use power query rank
use power query rank

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.

Power BI  how to use power query trim
Power BI how to use power query trim

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.
how to use power query trim
how to use power query trim
  • Now you can see all spaces are removed from the First name column in the Power query editor.
how to use trim in power query
how to use trim in power query

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.

Power query text. contains
Power query 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")
how to use text. contains in power query
how to use text. contains in power query
  • Now you can see the custom column with the boolean value and if the coffee is present then it returns true or else False.
use text. contains in power query
use text. contains in power query

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.

Power BI how to use power query transpose
Power BI how to use power query transpose

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.
how to use power query transpose
how to use power query transpose
  • Now you can see the result table is transposed from column to row.
use power query transpose
use power query transpose

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

Power BI  how to use power query unique values
Power BI how to use power query 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.
Microsoft Power BI  how to use power query unique values
Microsoft Power BI how to use power query unique values
  • 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.
how to use power query unique values
how to use power query unique values

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:

>