Power Query Problems

How to Combine Multiple Power Query Text Transformations into One Function

Emma Brown Oct 15, 2026 868 views

Question details

The user wants to combine several text operations into a single reusable custom function in Power Query to efficiently clean text values.

How to Combine Multiple Power Query Text Transformations into One Function

Product: Microsoft Excel / Power Query
Device & OS: not provided
Scenario: Cleaning text values in a dataset where multiple specific text manipulation steps are required repeatedly.
Observed behavior: The goal state is a functional custom M query function that applies all text transformation steps in sequence and can be invoked easily on any column.

Before you start

Ensure you have your workbook open in Excel and the Power Query Editor launched. You will need to use the Advanced Editor to write and test your custom M code.

Solution 1Recommended

Create a Reusable Custom Function in Power Query

By using the Advanced Editor, you can define a custom function that processes text step-by-step and can be invoked on any column in your dataset.

Power Query allows you to write custom functions using the M formula language. By chaining operations using a let...in block, you can execute multiple text transformations sequentially without cluttering your main query with repetitive steps.

Open the Advanced Editor for a New Query

In the Power Query Editor, right-click in the Queries pane on the left side, select 'New Query', and then click 'Blank Query'. Navigate to the Home tab and click 'Advanced Editor'.

Define the Custom Function Logic

In the Advanced Editor window, delete the default code and paste the following M code: (t as text) => let Step1 = Text.Remove(t,{"0".."9","#"}), Step2 = if Text.StartsWith(Step1,";") then Text.AfterDelimiter(Step1,";") else Step1, Step3 = Text.Replace(Step2,";;",";") in Step3

Save and Rename the Function

Click 'Done' to close the Advanced Editor. In the Query Settings pane on the right, rename the query to 'fnCleanText'. You will now see an 'fx' icon next to it indicating it is a custom function.

Invoke the Function on a Column

Go to the query containing your dataset. Click on 'Add Column' in the top ribbon and select 'Invoke Custom Function'. Choose 'fnCleanText' from the Function query dropdown and select the column you wish to clean as the input argument.

Create a Reusable Custom Function in Power Query

Successful Consolidation: Your query is now highly reusable. You can apply fnCleanText to any text column in any query within the same workbook, significantly streamlining your data cleaning process.

Free Microsoft Office alternative

Try WPS Office for Your Spreadsheet Data Needs

While Power Query (M code) is an advanced tool specific to Microsoft Excel and Power BI, WPS Office Spreadsheets offers a lightweight, highly compatible, and free alternative for standard data cleaning using built-in formulas like SUBSTITUTE, REPLACE, and TRIM. It perfectly opens and edits Microsoft Excel files without altering your layout.

1. Download and Install WPS Office: Visit the official WPS Office website to download the free application and install it on your device.
2. Open Your Excel Workbook: Launch WPS Spreadsheets and click 'Open' to browse and open your existing .xlsx or .csv data files.
3. Apply Built-In Text Functions: Use standard spreadsheet functions like SUBSTITUTE and TRIM directly in your worksheet to clean and transform your data without needing to write custom M code.

Fully compatible with Microsoft Excel formats (.xls, .xlsx, .csv)Feature-rich spreadsheet editor for complex data cleaning with standard functionsLightweight application that runs smoothly on almost all devicesFamiliar user interface requiring zero learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

How do I apply a custom Power Query function to an entire column?

Go to the 'Add Column' tab in the Power Query Editor, click on 'Invoke Custom Function', select your created function from the dropdown list, and choose the target column as the input parameter.

Why is my custom text function returning an error on empty cells?

If your target column contains null values, an M function expecting a text argument will return an error. You can fix this by adding a null check in your function code, such as: if t = null then null else ...

Can I edit the custom function after I create it?

Yes. Select the function (e.g., fnCleanText) in the Queries pane, then click 'Advanced Editor' on the Home tab. You can safely modify the transformation logic, and any column invoking this function will update automatically.

Excel & Data Problems