How to Extract Text from a String in Power Query

The fastest way to Extract Text from a string in Power Query is the Extract feature. It provides several pre-made settings to extract text from strings under common scenarios. For more control over the text extraction process you can also use Power Query M Formulas.

The Power Query Extract Text Feature

To access the Extract Text feature in Power Query, navigate to the Transform section of the Power Query Editor and select Extract under the Text Column section. A dropdown menu will appear that allows you to select different common scenarios.

Each one of the options under Extract will provide you with a dialogue box with further options. Some of the options let you get very granular in your text extraction without having to write any Power Query M code.

Use the Extract feature in Power BI to split out a specific part of a text string between two delimiters or a range.

Example: Extracting Text from a File Path String

Here’s an example of extracting text from a column made up of a Windows File Path. Note, that the column is designated as a Text column. This data type is a pre-requisite for being able to Extract text. It will not work with numeric columns.

Our goal is to extract only the name of the file without the extension.

While it’s possible to hard code the text extraction, we want it to be dynamic in case it is moved into another folder at a future point in time.

To Extract Numbers, you have to define where to start and end the extraction. These are defined with specific characters.

Advanced Text Extraction Options

The advanced options tell Power BI which direction to look when searching for the start and end delimiters. You can tell it to start at the end of the string, or from the beginning. Then once it finds the first delimiter, which direction it should go to search for the ending delimiter.

Example of using Text Between Delimiters

Extracting Text from a String Using Power Query M

When the built in text extract function of Power Query doesn’t give you enough control, it’s possible to create custom columns and use Power Query M formulas. The formulas needed will depend on the specific setup of your incoming text string. The following breaks down the relevant text extraction formulas in Power Query.

Text.Middle

The Text.Middle function extracts a substring from a text value, starting at a specified position.

Example: Extracting “Query” from “Power Query is amazing”.

   Text.Middle([ColumnName], 6, 5)

This extracts the word “Query” starting at position 6 for 5 characters.

Text.Start

The Text.Start function extracts the first few characters from a text string.

Example: Extracting “Data” from “Data Transformation”.

   Text.Start([ColumnName], 4)

This extracts the first 4 characters, resulting in “Data”.

Text.End

The Text.End function extracts the last few characters from a text string.

Example: Extracting “Data” from “Extracting Data”.

   Text.End([ColumnName], 4)

This extracts the last 4 characters, resulting in “Data”.

Text.BetweenDelimiters

The Text.BetweenDelimiters function extracts text between two specified delimiters.

Example: Extracting “John Doe” from “Name: John Doe; Age: 30”.

   Text.BetweenDelimiters([ColumnName], "Name: ", ";")

This extracts “John Doe” found between “Name: ” and “;”.

Text.Split

The Text.Split function splits a text value into a list based on a delimiter.

Example: Splitting “apple,banana,cherry” into a list.

   Text.Split([ColumnName], ",")

This splits the string into {“apple”, “banana”, “cherry”}.

Extract Text using Columns from Example

We love using the Column From Examples feature of Power BI. The feature samples the data in a column, and will attempt to create a column based on examples of what you want the end result to look like. It’s available under Add Column, Column From Examples. By using this feature you can avoid writing Power Query or setting up complex delimiting steps.

Creating a column from example to extract text based on a text pattern

If we use our example of extracting text above, we will now use Column From Example on a similar column of data.

The interface is a bit odd.

Once you launch it, look to the right. There is an additional set of columns that you can type a text pattern into.

Power BI will allow you to type in as many “hints” as you need to in additional rows. The more data that you provide Power BI the better it will do at creating a formula to extract the data for you.

Power BI suggesting column contents based on a user provided example of a text pattern

You will see Column From Example auto-complete additional rows. When the answers seem like they are doing what you want, click okay and Power BI will generate the code for you to create a column with the extracted text.

There are many great ways to use Columns from Examples. Check out this video to learn about some additional interesting use cases to help you speed up your development.

Scroll to Top