Power Query Problems

How to Use Text Matching and Conditional Replacement in Power Query

John Wilson Oct 23, 2026 868 views

Question details

The user needs to conditionally replace specific characters (like commas) with line breaks in Power Query, specifically when the cell's text contains or begins with a targeted word such as 'Annex'.

How to Use Text Matching and Conditional Replacement in Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Transforming and cleaning text data in Power Query where replacements should only occur if a specific logical text-matching condition is met.
Observed behavior: The goal is to apply a targeted text replacement operation based on logical conditions like Text.Contains or Text.StartsWith without affecting rows that do not match the criteria.

Before you start

Before you begin writing your M code, ensure your dataset is loaded into the Power Query Editor and clearly identify the name of the column you intend to transform.

Solution 1Recommended

Use Table.TransformColumns with Text.StartsWith

Apply this solution if the target word always appears at the very beginning of the cell's text string.

The Table.TransformColumns function allows you to apply an operation to a specific column. By combining it with an 'if' statement and Text.StartsWith, you can restrict the Text.Replace function to only trigger when the condition is met.

Open the Advanced Editor

In the Power Query Editor, go to the 'Home' tab and click on 'Advanced Editor' to access the M code for your query.

Write the transformation logic

Locate your previous step name (e.g., #"Changed Type") and add a new step using Table.TransformColumns.

Apply the M formula

Insert the following code: = Table.TransformColumns(#"Changed Type", {{"Column1", each if Text.StartsWith(_, "Annex") then Text.Replace(_, ", ", "#(lf)") else _, type text}}). Replace 'Column1' with your actual column name.

Save and close

Click 'Done' in the Advanced Editor. The commas in cells starting with 'Annex' will now be replaced with line breaks.

Use Table.TransformColumns with Text.StartsWith

Understanding the Underscore (_): In the Table.TransformColumns syntax, the underscore (_) serves as a shorthand to represent the current column value being evaluated in each row.

Solution 2

Use Table.ReplaceValue with Text.Contains

Use this method when the target word can appear anywhere within the text string, not just at the start.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Data Processing?

While Power Query is a robust tool in Microsoft Excel, it can sometimes be resource-heavy and complex for standard spreadsheet tasks. If you need a fast, free, and highly compatible office suite for everyday data analysis and formatting, WPS Office is an excellent alternative.

1. Download and Install: Get the free WPS Office suite from the official website and install it on your device.
2. Open Your Data File: Launch WPS Spreadsheet and easily open any existing Excel files.
3. Process Your Data: Utilize powerful built-in text functions like REPLACE and SUBSTITUTE directly within the spreadsheet environment.

Free to use with a familiar, user-friendly interface.Seamlessly open, edit, and save Microsoft Excel (.xlsx, .xls) files with complete format compatibility.Built-in advanced formulas and data processing tools without heavy resource demands.Lightweight software that runs smoothly on Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

What does '#(lf)' mean in Power Query M code?

In the Power Query M language, '#(lf)' represents a Line Feed. It is the syntax used to insert a carriage return or line break inside a text string during operations like Text.Replace.

What is the difference between Text.StartsWith and Text.Contains?

Text.StartsWith checks strictly if a string begins with a specific substring, whereas Text.Contains scans the entire string to see if the substring exists anywhere within it.

How do I reference the current column value in a Table.TransformColumns step?

In the Table.TransformColumns function, you can use the underscore character (_) as a shorthand variable. It automatically represents the current row's value for the specific column being transformed.

Can I replace multiple conditions at once in Power Query?

Yes, you can use nested 'if...then...else' statements within your each clause, or utilize custom functions and lists to handle multiple replacement conditions sequentially.

Excel & Data Problems