Power Query: Create a Custom Function (Reusable Logic)

Home » pub » Tutorials » Power Query: Create a Custom Function (Reusable Logic)

Build reusable Power Query logic with this practical custom function tutorial. Learn how to turn repeated transformation steps into a custom function, apply it across multiple files or tables, pass parameters, clean data consistently, and reduce manual work in Excel. Ideal for Excel users, analysts, finance teams, data teams, and professionals who want to automate repetitive data preparation tasks.

The moment you apply the same transformation steps to more than one query, you are ready for Custom Functions. A custom function lets you write reusable logic once and call it from any query in the workbook. It works like a built-in Power Query function: call it by name, supply arguments, receive a result. Functions can return a single value — text, number, or date — or an entire transformed table. They are the foundation of any Power Query automation beyond simple single-table imports.

Custom Function Syntax

Every M function uses the same core syntax: a parameter list on the left of the arrow operator, and the expression to evaluate on the right. Functions are created as named queries in Power Query. They appear in the Queries pane with a function icon and are callable from any other query in the same workbook.

Core M function syntax patterns: Pattern 1 — Single-line function: (price as number, vatRate as number) as number => price * (1 + vatRate) Pattern 2 — Multi-step let...in function: (rawDate as text) as nullable date => let Parsed = try Date.FromText(rawDate) otherwise null in Parsed Pattern 3 — Table-returning function (for file import pipelines): (fileContent as binary) as table => let Source = Pdf.Tables(fileContent), First = Source{[Id="Table001"]}[Data] in First Calling from another query: = AddVAT(100, 0.20) -- returns 120.00 = Table.AddColumn(Orders, "Gross", each AddVAT([Net], 0.20))

Three Ways to Create a Custom Function

Three distinct creation methods exist, each suited to a different scenario. Start with the simplest method that achieves the result — only escalate to more complex patterns when the simpler approaches are insufficient for the task.

Blank Query (write from scratch) — Go to Data > Get Data > From Other Sources > Blank Query. Type the function syntax directly in the formula bar. Best for simple mathematical or text transformations where the pattern is already known.
Promote from existing query — Build a query that transforms one sample file or table. Right-click the query and choose "Create Function". Power Query wraps the entire query into a reusable function automatically. This is specifically best for file-processing pipelines — CSV importers, PDF parsers, and Excel file transformers.
Let expression (multi-step) — Write a full let...in block for functions involving intermediate variables, conditional logic, or table operations. This is the most powerful approach and handles functions returning tables or requiring complex input-type branching across many steps.

Example 1: VAT Calculator — Lookup Tax Rate from a Reference Table

A VAT calculation function takes a net price and a country code, looks up the correct rate from a reference table, and returns the gross price. Creating this once makes it reusable across all sales queries. Call it on every row of any orders table to apply the correct country-specific rate without repeating the lookup logic in each query. Furthermore, updating a rate in the reference table automatically applies the change to all queries that use the function — no per-query edits required.

Country VAT calculator function: // Named "ApplyVAT" — created as a Blank Query: (netPrice as number, countryCode as text) as number => let Rate = List.First( Table.SelectRows(VATRates, each [Country] = countryCode)[Rate], 0), Gross = netPrice * (1 + Rate) in Gross // Apply to Orders table: = Table.AddColumn(Orders, "Gross Price", each ApplyVAT([NetPrice], [Country]), Currency.Type) // ORD-001: £100 net → £120.00 gross (UK 20%) // ORD-002: €200 net → €239.00 gross (DE 19.5%)

Example 2: Multi-Format Date Parser — Normalise Inconsistent Date Strings

Data from different systems arrives with dates in different formats — "2025-01-15" from one database, "01-15-2025" from a US system, "15 January 2025" from a report export. A multi-format date parser function tries each format in a defined sequence, returning the first successful parse as a true date value. This single function normalises all date formats across any dataset, replacing a complex series of conditional columns with one clean function call per row.

Multi-format date parser: (rawDate as text) as nullable date => let Formats = {"yyyy-MM-dd", "dd/MM/yyyy", "MM/dd/yyyy", "d MMM yyyy", "MMMM d, yyyy", "yyyyMMdd"}, Attempts = List.Transform(Formats, each try Date.FromText(rawDate, [Format=_]) otherwise null), Result = List.First(List.Select(Attempts, each _ <> null), null) in Result // Apply to a column with inconsistent dates: = Table.TransformColumns(MyTable, {{"OrderDate", each ParseDate(Text.From(_)), type date}})

Example 3: Multi-File Importer — One Function for a Whole Folder

This is the most common real-world use of Power Query custom functions. You have 12 monthly CSV files in a folder, all with identical structures but each requiring the same cleaning steps. Create the function once from a sample file. Power Query applies it to every file automatically. Furthermore, adding a new month's file to the folder and clicking Refresh imports it without any query changes — a fully automated pipeline that requires zero maintenance once built.

Multi-file importer pattern: Step 1: Build the transform query for one sample file. Apply all cleaning steps. Name the query "SampleTransform". Step 2: Right-click "SampleTransform" > Create Function. Name the function "TransformFile". The function accepts one parameter: the binary file content. Step 3: Data > Get Data > From File > From Folder. Step 4: Add Custom Column = TransformFile([Content]). Step 5: Expand the column to combine all tables into one query. Result: all monthly files combined automatically. New files appear on the next Refresh — no query editing required.

Example 4: Row Validation Checker — Flag Records with Data Quality Issues

A validation function checks each row against a set of quality rules and returns a text status message. Running it on every row flags records with missing values, out-of-range amounts, or invalid dates before data reaches downstream models. Consequently, quality issues are caught at the import stage rather than discovered weeks later in a report. The validation column makes filtered views of problem records immediately accessible without any additional steps.

Row validation function: (orderID as text, amount as number, orderDate as date) as text => let Issues = List.RemoveNulls({ if Text.Length(orderID) <> 8 then "OrderID must be 8 chars" else null, if amount <= 0 then "Amount must be positive" else null, if amount > 999999 then "Amount exceeds maximum" else null, if orderDate < #date(2020,1,1) then "Date before 2020" else null }), Status = if List.Count(Issues) = 0 then "VALID" else Text.Combine(Issues, " | ") in Status

Example 5: Address Formatter — Standardise CRM Contact Addresses

Address data from CRM systems is notoriously inconsistent — extra spaces, mixed case, and null values in some fields. A formatting function takes raw address components and returns a standardised single-line address. It handles null values gracefully, trims whitespace from each component, and applies consistent capitalisation rules. Applying this function to every row of a contacts table produces clean, uniformly formatted addresses for mail merge, shipping labels, and segmentation analytics.

Address formatter function: (line1 as nullable text, city as nullable text, postcode as nullable text) as text => let Parts = List.RemoveNulls({ if line1 = null then null else Text.Proper(Text.Trim(line1)), if city = null then null else Text.Upper(Text.Trim(city)), if postcode = null then null else Text.Upper(Text.Trim(postcode)) }), Result = Text.Combine(Parts, ", ") in Result // " 123 main st", "london", "SW1A 1AA" → "123 Main St, LONDON, SW1A 1AA" // null, "bristol", "BS1 5TR" → "BRISTOL, BS1 5TR"

Example 6: Invoke Custom Function — Apply via the Ribbon

The "Invoke Custom Function" option applies your function to every row through the ribbon, without writing any M code manually. Select the table query in the editor. Go to Add Column > Invoke Custom Function. Select your function name from the dropdown and map each parameter to a table column. Power Query generates the corresponding M code automatically and adds a new result column. This approach makes custom functions accessible to users who are not comfortable writing M expressions directly in the formula bar.

Invoke Custom Function via ribbon: 1. Open the target table in Power Query editor. 2. Add Column tab > Invoke Custom Function. 3. New column name: "Gross Price" 4. Function query: ApplyVAT (select from dropdown) 5. Map parameters: netPrice → Column "NetPrice" countryCode → Column "Country" 6. Click OK. Power Query auto-generates: Table.AddColumn(PreviousStep, "Gross Price", each ApplyVAT([NetPrice], [Country]), type number)

Troubleshooting Custom Functions

Both issues below are the most common causes of custom function errors in production queries. Null handling accounts for the majority of row-level errors across real data.

The function returns an error for some rows but not others

Row-level errors almost always mean the function is not handling null values correctly. If any row has a null in a column the function expects as text or number, M throws a type error for that row. Wrap null-sensitive operations with null checks: if value = null then null else DoSomething(value). Alternatively, use try MyFunction(value) otherwise "ERROR" to catch all errors and return a fallback value. Red cells in the Power Query preview indicate error rows — click one to see the exact error message identifying which parameter caused the failure.

The function is very slow when applied to large tables

Custom functions applied row-by-row can be slow because Power Query evaluates each row sequentially. Check whether the function logic can be replaced by a native Power Query column transformation applied to the entire column at once — Table.TransformColumns is often significantly faster than a row-by-row function call for simple text or number operations. Also verify the function does not perform a table join inside the row iterator, as this multiplies the join cost by the number of rows in the table and can cause severe performance degradation.

"Create Function" is missing from the right-click menu

The "Create Function" option only appears when the query contains a parameterisable input. If the option is missing, the query source is likely hard-coded. Convert the hard-coded path to a query parameter first: Home > Manage Parameters > New Parameter. Reference the parameter in the Source step. Then right-click the query again — the "Create Function" option will now appear in the menu and you can promote the parameter to a function argument.

Frequently Asked Questions

How do I create a custom function in Power Query?+

Go to Data > Get Data > From Other Sources > Blank Query. In the formula bar, type the function syntax: (parameter as type) => expression. For example: (price as number) => price * 1.20. Press Enter — the preview changes to show "Function". Name the query a meaningful name in the Queries pane. Call the function by name from any other query in the workbook, supplying arguments in parentheses, or use Add Column > Invoke Custom Function from the ribbon to map parameters visually.
How do I apply a custom function to every row of a table?+

Use Table.AddColumn with an each clause: Table.AddColumn(MyTable, "Result", each MyFunction([ColumnName])). The each keyword iterates over every row and evaluates the function for each one. Alternatively, use the ribbon: Add Column > Invoke Custom Function, select the function name, and map its parameters to table columns. Power Query generates the M code automatically and adds a result column for every row without any manual formula writing.
Can a Power Query custom function return a table?+

Yes. A Power Query function can return any M value type including a full table. Table-returning functions are particularly useful for file-processing pipelines — pass a binary file as input and the function returns the extracted and cleaned table. After calling the function and adding the result as a column, expand the column using the expand icon in the column header to flatten the returned tables into the main query's rows. This is the standard pattern for combining multiple source files using a custom transform function.
Do Power Query custom functions update when source data changes?+

Yes. Custom functions are M expressions stored in the workbook and re-evaluated every time you refresh the queries that call them. When source data changes and you click Data > Refresh All, every query calling the function runs the function logic again against the new data. The function definition only needs updating when the transformation logic itself changes — not when the underlying data changes. Update the function once and all calling queries benefit automatically on the next refresh.