Extract Text in Power BI Power Query Using Add Column [With Various Examples]

While working on a client report, there was a requirement to clean and format text data before using it in visuals. The source file contained values such as codes, names, and descriptions combined into a single column. The client wanted only specific parts of the text, such as the first few characters, the last value, or the text before and after a symbol.

To solve this requirement, I used the Extract option in Power Query. This feature helps extract only the required part of the text and create a new column without changing the original data.

In this tutorial, I will explain how to use the Extract in the Power Query Add Column. I will cover:

• Extract Text Length in Power BI Power Query
• Get First Characters from Text in Power BI Power Query
• Extract Last Characters from a Text Column in Power BI
• Extract a Specific Text Range in Power BI Power Query
• Extract Text Before a Delimiter in Power BI Power Query
• Extract Text After a Delimiter in Power BI Power Query
• Extract Text Between Two Delimiters in Power BI Power Query

Extract Text in Power BI Power Query Using Add Column

In this example, I am using the Super Store sample data shown below. You can also download the sample data from the link provided and use it in your own report.

Extract Text Length in Power BI Power Query

For this example, I will extract the text length from the Product Name column using Power BI Power Query. The goal is to find how many characters are present in each product name and store the result in a new column.

To do this, follow the steps below:

1. Open Power BI Desktop. Load the Excel file (Super Store data) using the Get Data option.

2. Go to the Home tab and click Transform data to open Power Query Editor.

3. Select the text column for which you want to find the length (for example, Product Name). Go to the Add Column tab. Click Extract -> select Length.

4. Power Query will create a new column that shows the number of characters in each text value. Rename the new column if required.

This is how you can extract text length using Add Column -> Extract -> Length in Power BI Power Query.

Get First Characters From Text in Power BI Power Query

For this example, I will extract the first 3 characters from the Order ID column using Power BI Power Query, since those characters represent the product category.

To do this, follow the steps below:

1. Open Power BI Desktop. Load the Excel data using the Get Data option.
2. Go to the Home tab and click Transform data to open Power Query Editor.

3. Select the Order ID column. Go to the Add Column tab. Click Extract and select First Characters.

4. In the dialog box, enter three as the number of characters. Click OK.

Then you can see a new column will be created showing the first 3 characters from the Order ID.

This is how you can extract the first three characters from the Order ID column in Power BI Power Query.

Extract Last Characters From a Text Column in Power BI

In this example, I will extract the last four characters from the Order ID column using Power BI Power Query. The last characters of the Order ID represent the order number, which helps track and sort orders.

Follow the steps below:

1. Open Power BI Desktop and load the Excel data using the Get Data option. Go to the Home tab and click Transform data to open Power Query Editor. Then you can see the data in the Data Pane.

2. Select the column you want to extract, in my case, Order ID. Go to the Add Column tab, click Extract, and then select Last Characters.

3. In the dialog box, enter four as the number of characters and click OK.

Power Query will create a new column that shows the last four characters from each Order ID value.

This is how you can extract the last characters from a text column using Add Column -> Extract -> Last Characters in Power BI Power Query.

Extract a Specific Text Range in Power BI Power Query

While working on a client report, the Order ID column followed a fixed format. The year of the order was always stored in the middle of the Order ID (characters 4 to 7).

I wanted the order year in a separate column for filtering and reporting.

To do this, follow the steps below:

1. Open Power BI Desktop and load your data. Go to Home, click Transform data to open Power Query Editor.
2. Select the Order ID column. Go to the Add Column tab. Click Extract, select Range.

3. In the dialog box:
   • Start index: 3
   • Number of characters: 4
4. Click OK.

Then you can see a new column will be created showing the order year.

This is how you can extract a specific text range from a column using Power BI Power Query.

Extract Text Before a Delimiter in Power BI Power Query

In this example, I have a dataset where the Customer Email column contains values in this format: username@domain.com.

Here, I want to extract only the text before the @ symbol, because it represents the user name. To do this, I will use the Extract Text Before Delimiter option in Power BI Power Query.

Follow the steps below to Extract Text Before a Delimiter:

1. In Power BI Desktop, open Power Query Editor. Select the Customer Email column.
2. Go to the Add Column tab. Click Extract, select Text Before Delimiter.

3. In the dialog box, enter @ as the delimiter. Click OK.

A new column will be created showing the text before the @ symbol.

You need to replace the dot with a blank space to find the Customer’s name. This is how you can extract text before a delimiter using Power BI Power Query.

Extract Text After a Delimiter in Power BI Power Query

In this example, I am using the same data set, but I want to extract only the text after the @ symbol, since it represents the email domain (e.g., gmail.com or outlook.com).

Let’s see how to do:

1. Open the Power Query editor and select the column you want to extract, in my case, the Customer Email column.
2. Go to the Add Column tab. Click Extract, select Text After Delimiter.

3. In the dialog box, enter @ as the delimiter. Click OK.

You can see that a new column will be created showing the text after the @ symbol.

Extract Text Between Two Delimiters in Power BI Power Query

While working on a sales report, the required information was stored between two symbols inside a text value. The Order Reference column contained values where important details were placed inside square brackets.

In this example, I want to extract only the text between [ and ], such as Furniture, Technology, and Office Supplies.

To do this, follow the steps below:

1. Open Power BI Desktop and load the data using the Get Data option.
2. Go to the Home tab and click Transform data to open Power Query Editor.

3. Select the Order Reference column from the table. Go to the Add Column tab in the Power Query Editor. Click Extract, and then select Text Between Delimiters.

4. In the dialog box:
   • Enter [ as the starting delimiter.
   • Enter ] as the ending delimiter.
5. Click OK.

Power Query will create a new column that shows only the text between the square brackets. Rename the new column if required.

In this tutorial, I explained how to use the Extract option in Power Query Add Column to work with text data. I showed how to extract the text length, the first and last characters, and a specific text range from a column. I also covered how to extract text before a delimiter, text after a delimiter, and text between two delimiters.

You may like the following Power BI tutorials:

• Merge Tables in Power BI
• Remove Leading Zeros in Power BI
• Convert Number to Text in Power BI
• Count Rows and Distinct Values in Power BI Power Query