Power BI Power Query Examples [Complete Tutorial]

In this Power BI tutorial, I will explain all about Power BI Power Query examples, Power Query m Language examples, and Power Query Advanced Editor examples.

Also, we will cover the topics below:

  • How to use Power Query Editor in Power BI
  • M language examples in Power BI
  • How to work with Power BI power query
  • What is Power Query text.start
  • What is Power Query text.end

What is Power Query in Power BI?

Power Query is a feature within Power BI (also available in Excel) that allows users to transform, clean, and shape their data before importing it into Power BI for analysis and visualization.

In other words, Power Query Editor is a tool for preparing data for analysis. It allows you to clean up messy data, combine multiple sources, remove duplicates, and perform various transformations to make your data easy to work with.

How to Use Power Query Editor in Power BI?

Let’s see how to use the power query editor in Power BI Desktop.

1. Open Power BI Desktop, then under the Home tab, click Transform data.

power query examples in power bi

2. Now, you can see Power Query Editor opens.

power query in power bi examples

Now, you can use the power query editor in Power BI Desktop.

How to Work with Power BI Power Query?

In this example, we see how to Import Data from a power query editor.

1. Open Power BI Desktop; after that, open Power Query Editor.

power query tutorial

2. Under the Home tab, expand New Source and click Excel Workbook. You can pick any data source you need.

power query in power bi

3. This will ask you to browse an Excel file. Here, select the Excel file and click on Open.

how to use power query

4. After that, it will display all the tables in the Excel file you created. Select the table you want to use for creating the report. Click on OK.

what is power query in power bi

5. Then you can see our loaded in power query editor.

power bi query tutorial

Then, to meet your needs, you can clean and manipulate the data.

Power Query Advanced Editor Examples

Here, we will learn how to use the Power Query Advanced Editor in Power BI.

The Advanced Editor in Query Editor is like a box for your data. Inside, you can edit and organize your data so it looks right for your reports in Power BI Desktop.

See also  Power BI Bookmarks [With 21 Examples]

The Advanced Editor provides detailed information about your data, including the code for each step of the editing process.

Whenever you make changes to your data, you can check them in the Advanced Editor. It uses Power Query or M-language.

Let’s say we have the table below, and we want to change the task name from Framing to Painting.

power bi power query tutorial

Now follow the below steps:

1. load the above data in Power Query Editor.

power query advanced editor syntax

2. In the Power Query editor, under the Home tab, click Advanced Editor from the ribbon.

power query advanced

3. we can use the Text.Replace function to Framing to Painting. Put the below expression.

#"Replaced Value" = Table.ReplaceValue(#"Changed Type","Framing","Painting",Replacer.ReplaceText,{"Task"}),

Where:

  • “Replaced Value” = This is the name given to the new table that will be created after the replacement operation.
  • Table.ReplaceValue = This DAX function helps in replacing specific values within a table.
  • #”Changed Type” = it’s refers to the name of a table to which replacements will be applied.
  • “Framing” = Specifies the value that needs to be replaced.
  • “Painting” = Denotes the new value that will replace “Framing.”
  • Replacer.ReplaceText = Defines the method to replace text (in this case).
  • {“Task”} = Specifies the column where replacements will occur (in this case, the “Task” column).

Then, under the “in,” write #”Replaced Value.” Then Click on Done.

power query advanced editor syntax in Power BI

4. Now you can see the name is changed in the table from Framing to Painting.

power query advanced in Power BI

This way, you can use the Power Query Advanced Editor in Power BI.

Power Query M Language Examples

Power Query M Language is a scripting language used in Power BI to manipulate and transform data before loading it into your report.

It’s like giving instructions to clean, organize, and shape your data exactly how you want it.

Suppose you have a dataset with a column of dates, but the dates are in different formats, and you want to standardize them. With Power Query M Language:

m language examples in Power BI

Now Follow the below steps:

1. In the Power Query editor, under the Home tab, click Advanced Editor from the ribbon.

Power BI m code tutorial

2. To convert the “Date” column to date type and then transform it into a standardized format of “yyyy-MM-dd.” Put below expression:

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Standardized Date" = Table.TransformColumns(#"Changed Type", {{"Date", each Date.ToText(_, "yyyy-MM-dd"), type text}})

Where:

  • “#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type date}}) = This M language code transforms the “Date” column in the table named Source to a date data type.
  • “#”Standardized Date” = Table.TransformColumns(#”Changed Type”, {{“Date”, each Date.ToText(_, “yyyy-MM-dd”), type text}}) = This M language code converts the values in the “Date” column (previously transformed to dates) into text format, with the specified “yyyy-MM-dd” format, in a new table named #”Standardized Date.”
See also  Power BI IF NULL then 0

Then, under the “in,” write #”Standardized Date.” Then Click on Done.

Power BI m query tutorial

3. Now, the dates are all in the format “yyyy-MM-dd.”

Power BI m query examples

This way, you can use m language in Power BI.

Power BI Power Query text.start

let’s see how to use Text.start in the Power query of Power BI.

In Power Query, the “Text.Start” function is used to extract a specified number of characters from the beginning of a text string.

So, if you have a long text and only need the first few characters, you can use this function to obtain them quickly.

Syntax:

Text.Start(text as nullable text, count as number) as nullable text

Suppose you have a dataset with columns of Task and Due Date, like the screenshot below.

Power BI how to use power query text.start

Now follow the below steps:

1. Now, I hope you load the data on the Power BI desktop and open the Power Query editor.

how to use power query text in Power BI

2. Under the Add Column tag, click Custom Column.

power query text.start

3. In the Custom Column window, under “New column name,” give a name. Then, in the custom column formula, enter the below expression. Then click OK.

Text.Start([Task], 5)

Where:

  • Text.Start =This is likely a function that extracts a specified number of characters from the beginning of a text string.
  • ([Task]): This part is likely the input parameter, which [Task] is probably a placeholder for the text string from which you want to extract the characters.
  • 5: This number indicates how many characters you want to extract from the beginning of the text string.
power query text.start in Power BI

4. Then, you can see that the power query editor extracts 5 characters.

Power BI power query text.start

This way, you can use Text.start in the Power query of Power BI.

Power BI Power Query text.end

let’s see how to use Text.end in the Power query of Power BI.

In Power Query, the “.Text.End” function helps you grab a specific number of characters from the end of a text string.

Syntax:

Text.End(text as nullable text, count as number) as nullable text

Suppose you have a dataset with columns of Task and Due Date, like the screenshot below.

Power BI how to use power query text.start

In the Custom Column window, under “New column name,” give a name. Then, in the custom column formula, enter the below expression. Then click OK.

Text.End([Task], 5)

Where:

  • Text.End = This is the name of the function. It’s like a command that tells the computer to do something with text.
  • [Task] = This is the input or text the function will work on. It could be any text, like a word or a sentence.
  • 5 = This is a number that tells the function how many characters from the end of the text it should look at.
Power BI power query text.end

Then, you can see that the power query editor extracts 5 characters.

power query text.end

This way, you can use Text.end in the Power query of Power BI.

See also  How to sort slicer by measure in Power BI

How to use Power Query Text.contains

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

The Text.contains() function is used to search for specific text within a string. If the text is found in the string, it returns true; otherwise, it returns false.

Syntax:

Text.Contains(text as nullable text, substring as text, optional comparer as nullable function) as nullable logical

Suppose you have a dataset with columns of Task and Due Date, like the screenshot below.

Power BI how to use power query text.start

In the Custom Column window, under “New column name,” give a name. Then, in the custom column formula, enter the below expression. Then click OK.

Text.Contains([Task],"Flooring")

Where:

  • Text.Contains: This is a function used to check if a piece of text contains another specific piece of text.
  • [Task]: This is the text or string where we want to search for something.
  • “Flooring”: This is the specific word or phrase we’re looking for within the task. In this case, it’s “Flooring”.
How to use Power Query Text.contains

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

Power BI Power Query Text.contains

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

Also, you may like:

Conclusion

In this article, we explored Power Query in Power BI, which is a powerful tool for data transformation and preparation. Also, we learned how to use the Power Query Editor to clean, reshape, and combine data from various sources.

Additionally, we learned power query examples, what is advanced power query, power query advanced editor examples.

>