How to Create a Function in Power BI

Building reports can feel like you’re doing the same tedious data cleaning tasks over and over again. Power BI's custom functions are your secret weapon against this repetition, allowing you to bundle a series of transformation steps into a single, reusable tool. This guide will walk you through exactly how to create and use your first custom function in Power BI’s Power Query Editor to automate your work and save a ton of time.

What Exactly Is a Custom Power BI Function?

Think of a custom function like a recipe. Instead of manually grabbing the flour, sugar, and eggs and then following the same mixing and baking steps every time you want a cake, you just follow the "cake recipe." In Power BI, a custom function bundles a set of data transformation steps from Power Query - like cleaning text, splitting columns, or applying conditional logic - into one callable "recipe."

Once created, you can "invoke" this function on any column or value you want, instantly applying that entire sequence of steps. This is incredibly efficient.

The key benefits of using custom functions include:

• Reusability: Create a transformation process once and apply it to multiple tables or projects without rebuilding it from scratch.
• Consistency: Ensures that the same logic is applied in the exact same way every single time, reducing the risk of human error.
• Simplicity: It simplifies your list of applied steps in a query. Instead of seeing 10 different steps for cleaning up a single column, you see one step: "Invoked Custom Function."
• Maintainability: If you need to update the logic, you only have to change it in one place - the function itself. Any query that uses the function will automatically see the changes.

The Home of Functions: Power Query and M Language

Before we build, it's important to know where this all happens. Custom functions are not created using DAX (Data Analysis Expressions), which is the language for creating calculated columns and measures inside your data model. Instead, they are created in the Power Query Editor using its formula language, officially known as M language.

Power Query is Power BI’s data transformation engine. It’s where you connect to data, clean it up, reshape it, and get it ready for analysis. Every click you make in the Power Query user interface - splitting a column, replacing a value, changing a data type - writes a line of M code in the background. We are simply going to hijack that process to create a reusable function.

Our Scenario: Standardizing Inconsistent Country Names

Let's use a common and practical example. Imagine you have a dataset with a country column from different sales systems. The data is a mess:

• Some entries say "USA."
• Others say "United States."
• Some are written as "U.S.A."
• Maybe others are "Great Britain" and "UK."

You need to standardize all of these to a consistent format, for example, "USA" and "United Kingdom." Instead of using the "Replace Values" feature multiple times on every new dataset, we'll build a function to do it all in one go.

Step-by-Step: How to Create a Power BI Function

The easiest way to learn M is to let Power Query write the code for you first, then convert it into a function. Follow these steps, and you’ll have your first function running in minutes.

Step 1: Get a Sample and Create a Sandbox Query

First, we need a testing ground. Instead of working directly on our main data table, we'll start with a new, blank query where we can build and test our logic safely.

1. In the Power Query Editor, go to Home > New Source > Blank Query.
2. A new query will appear in the Queries pane on the left. Right-click it and rename it to something descriptive like Standardize Function Development.
3. Now, in the formula bar for this new blank query, type in a single example of the messy data you want to clean. Let's start with ="U.S.A." and press Enter. You should now see "U.S.A." as the output of this query.

This gives us a sample text value to work with as we build our transformation logic.

Step 2: Apply Your Transformations Manually

With our sample value in place, we will now apply the cleaning steps just like we normally would using the Power Query interface. Our goal is to transform "U.S.A." into "USA" and also have logic in place for other variations.

1. With the Standardize Function Development query selected, go to the Home tab and click Replace Values.
2. A dialog box will pop up. In "Value to Find," type U.S.A. In "Replace With," type USA. Click OK.
3. Let's add a few more replacements. Click Replace Values again. This time, find United States and replace it with USA.
4. Click Replace Values one last time. Find UK and replace it with United Kingdom.

Even though our starting value was "U.S.A.", Power Query has recorded all these transformation steps in order. If you look at the "Applied Steps" pane on the right, you'll see each replacement you added. Power Query generated the M code for us behind the scenes!

Step 3: Access the M Code in the Advanced Editor

Now it's time to see the code we just created.

1. Make sure your Standardize Function Development query is still selected.
2. Go to the Home tab and click Advanced Editor.

A new window will open, revealing the M code. It will look something like this:

let
    Source = "U.S.A.",
    #"Replaced Value" = Text.Replace(Source, "U.S.A.", "USA"),
    #"Replaced Value1" = Text.Replace(#"Replaced Value", "United States", "USA"),
    #"Replaced Value2" = Text.Replace(#"Replaced Value1", "UK", "United Kingdom")
in
    #"Replaced Value2"

Step 4: Convert the Query into a Reusable Function

This is where the magic happens. We're going to make two small edits to turn this static M script into a dynamic, reusable function.

1. Define a Parameter: At the very top of the script, before the word let, add this line:

(country_name as text) =>

This line tells Power BI, "This is a function that accepts one parameter, which we'll call country_name, and it will be a text value."

1. Swap the Hardcoded Value for the Parameter: Right now, the Source variable is "U.S.A.". We need to replace this with our new country_name parameter. Change this line:

Source = "U.S.A.",

to:

Source = country_name,

Now, your complete code should look like this:

(country_name as text) =>
let
    Source = country_name,
    #"Replaced Value" = Text.Replace(Source, "U.S.A.", "USA"),
    #"Replaced Value1" = Text.Replace(#"Replaced Value", "United States", "USA"),
    #"Replaced Value2" = Text.Replace(#"Replaced Value1", "UK", "United Kingdom")
in
    #"Replaced Value2"

Click Done to close the Advanced Editor. In the Queries pane, rename the query to fnStandardizeCountry. The fn prefix is a common naming convention for functions, making them easy to identify.

You'll notice the icon for your query has changed to an fx symbol. You've officially created a function!

Step 5: Invoke Your Function on Your Real Data

Now for the fun part: putting your function to work.

1. Select the query containing your real sales data (the table with the messy country column).
2. Go to the Add Column tab in the ribbon and click Invoke Custom Function.
3. In the "Invoke Custom Function" window:
4. Click OK.

Instantly, Power BI will add a new column to your table. It will take each value from your original country column, run it through your standardization function, and place the clean result in the new column.

Tips for Better Power BI Functions

Once you’re comfortable with the basics, keep these tips in mind:

• Keep Functions Focused: Try to make each function do one thing well. A function that standardizes country names shouldn't also be parsing dates. If you need to do both, create two separate functions.
• Use Descriptive Names: Just as we named our function fnStandardizeCountry and our parameter country_name, use clear names that explain what the function and its inputs do.
• Add Comments: In the Advanced Editor, you can add comments to your M code using //. Use these to explain complex steps for anyone who looks at your code later (including your future self!).
• Handle Errors Gracefully: Consider what should happen if a function gets an unexpected input (e.g., a number instead of text). You can add conditional logic with try and otherwise statements in M to manage potential errors.

Function vs. Calculated Column (M vs. DAX)

A common point of confusion is when to use a Power Query function (in M) versus a DAX calculated column.

Here’s a simple rule of thumb:

• Use a Power Query Function (M) for data preparation and transformation. If you are cleaning raw data, reshaping your table structure, or standardizing values before they are loaded into the Power BI data model, M is your tool. The result is a physically new column added to your table during the data refresh process.
• Use a DAX Calculated Column for analysis and modeling inside your data model. Use DAX when you need to perform calculations that rely on values from different rows or tables that are already loaded. DAX operates on the complete data model, whereas Power Query operates on tables one by one.

For our country standardization problem, a Power Query function is the ideal choice because it’s a repeatable data-shaping task that prepares the data for analysis.

Final Thoughts

You've now learned how to turn a series of manual, repetitive Power Query steps into a clean, reusable custom function. This practice is fundamental to building efficient and maintainable Power BI reports, freeing you from tedious data cleanup so you can focus more on creating meaningful insights.

At the end of the day, creating robust reports often requires wrangling many different data sources and performing countless transformations just like this one. We built Graphed because we believe there’s an even easier way. Our tools connect directly to your marketing and sales platforms (like Google Analytics, Shopify, Facebook Ads, and HubSpot) and let you build entire, real-time dashboards just by describing what you need in plain English. This eliminates the learning curve entirely, allowing you to get the answers you need in seconds, not hours.