Power Query Functions Explained: From Simple Cleanup to Dynamic Transformations

[AnkitaaMishra]

Introduction

If you’ve ever found yourself repeating the same transformation across multiple queries, Power Query functions can save you hours of manual work.

Functions in Power Query let you bundle logic once and reuse it anywhere, just like a formula in Excel, but more powerful. They make your transformations modular, maintainable, and scalable.

What Are Power Query Functions?

A function in Power Query is like a reusable transformation block. It takes one or more inputs (called parameters), performs logic, and returns an output. You can create one by:
1. Opening Power Query Editor → Home > New Source > Blank Query
2. Renaming the query (e.g., fnStandardizeText)
3. Clicking Advanced Editor → pasting your M code

Think of it as a recipe, once you write it, you can 'serve' it to as many times as you want.

Use Case 1: Text Standardizer (Easy)

You receive inconsistent names, some have extra spaces or unwanted symbols. You need to clean them before loading to your model.

Sample data: 

CustomerID	CustomerName
101	" john doe "
102	"JANE DOE"
103	"michael#smith"
104	null

Power Query Function :

(inputText as nullable text) as nullable text =>
let
    cleaned = if inputText = null then null else Text.Trim(inputText),
    proper = if cleaned = null then null else Text.Proper(cleaned),
    removeExtras = Text.Replace(proper, "#", "")
in
    removeExtras

Steps:
1. Import sample table into Power Query.

2. Create a Blank Query → rename to fnStandardizeText.

3. Paste the function above into Advanced Editor.

4. Add a Invoke Custom Function → fnStandardizeText([CustomerName]).

Final Output :

Use Case 2: Currency Conversion to INR 

Your sales data is in multiple currencies, USD, EUR, GBP, and INR. You want to convert everything automatically to INR using static exchange rates.

Sample data: 

TransactionID	Amount	Currency
1	100	USD
2	50	EUR
3	75	GBP
4	500	INR

Power Query Function :
(amount as number, fromCurrency as text) as nullable number =>
let
    Rates = [
        USD = 83.1,
        EUR = 90.2,
        GBP = 104.5,
        INR = 1
    ],
    rate = Record.FieldOrDefault(Rates, fromCurrency, null),
    converted = if rate <> null then amount * rate else null
in
    converted

Steps:
1. Import CurrencyConversion table into Power Query

2. Create a Blank Query → rename to fnConvertCurrency.

3. Paste the code above.

4. Add a Custom Column: fnConvertCurrency([Amount], [Currency]).

Output:

Tips & Best Practices

- Use parameters for configurable rates or folder paths.
- Document functions clearly for reuse by your BI team.
- Store all utility functions in a dedicated 'Functions' folder.

Conclusion

Power Query functions are not just for coders, they’re for anyone who wants cleaner, faster, and more consistent transformations.

Whether you’re standardizing text or converting currencies, functions turn repetitive work into reusable assets.

Start small, experiment, and you’ll soon be building your own Power Query toolkit.