Graphed LogoGraphed
Login

What is Invoke Custom Function in Power BI?

Cody Schneider•

Tired of repeating the same data cleaning steps over and over in Power BI? A feature called "Invoke Custom Function" is your ticket to automating those tedious, repetitive tasks for good. This article will show you how to create your own reusable data transformation 'recipes' and apply them with a few clicks, saving you tons of time and making your queries cleaner.

What is a Custom Function in Power BI?

Before we can "invoke" a function, we need to understand what it is. Think of a custom function in Power BI's Power Query Editor like a recipe for a kitchen task you do all the time. Instead of pulling out the ingredients, measuring cup, and mixer every single time you want to make a simple sauce, you write down the exact recipe once. From then on, you can just say, "Make the sauce with these tomatoes," and get a perfect result every time.

In Power BI, that "recipe" is a block of M code that performs a specific set of steps. You might create a function to:

  • Trim leading and trailing spaces from a text field and convert it to title case.

  • Calculate a company-specific fiscal quarter from a standard date.

  • Combine a first name and a last name into a "Full Name" field.

  • Clean up inconsistent product SKUs by removing special characters.

Once you've created this "recipe," the Invoke Custom Function feature is how you can tell Power BI: "Hey, run that recipe on every single row in this column."

Using custom functions is a game-changer for a few key reasons:

  • Efficiency: It saves you from having to repeat the same clicks and transformations multiple times across different columns or tables. Do it once, then reuse it everywhere.

  • Consistency: It ensures that the exact same logic is applied every single time, reducing the risk of human error or slight variations in your data cleaning process.

  • Readability: It condenses a dozen complex cleaning steps into a single, understandable step in your Power Query list: "Invoked Custom Function." This makes it much easier to debug and manage your queries later on.

Creating Your First Custom Function (Step-by-Step)

The best way to learn is by doing. Let's build a practical function that solves a common business problem: calculating fiscal quarters. Many businesses don't start their year on January 1st. For this example, we'll imagine a fiscal year that starts on October 1st.

This means:

  • October, November, December = FQ1

  • January, February, March = FQ2

  • April, May, June = FQ3

  • July, August, September = FQ4

Power BI doesn't have a built-in button for this specific logic, making it a perfect candidate for a custom function.

Step 1: Start with a Parameter to Test Your Logic

It’s easiest to build the logic for one single value before turning it into a function. We'll use a parameter for this.

  1. In Power BI, open the Power Query Editor by clicking "Transform data."

  2. In the Home tab, go to "Manage Parameters" > "New Parameter."

  3. Set it up as follows:

    • Name: SampleDateInput

    • Type: Date

    • Current Value: Choose any date, like 11/15/2023.

  4. Click "OK." You now have a placeholder value to build your logic against.

This gives us a playground to write and test our M code without messing with our real data.

Step 2: Build the Transformation Logic on the Parameter

Now, let's build the query that will transform our SampleDateInput into a fiscal quarter.

  1. Right-click on your new parameter in the Queries pane (on the left) and select "Reference." This creates a new query that uses the parameter as its source.

  2. Rename this new query to something descriptive, like Fiscal Quarter Logic.

  3. With this new query selected, go to the "Add Column" tab in the ribbon. Click on "Date" > "Month" > "Month." This adds a new step that extracts the month number (in our example, it would return 11).

  4. Now, we need to add the if/then/else logic. Go to "Add Column" > "Conditional Column."

  5. Configure the Conditional Column as follows:

    • New column name: FiscalQuarter

    • If Column Name (select the Month column) is greater than or equal to 10, then Output FQ1

    • Add Clause: If Column Name is greater than or equal to 7, then Output FQ4

    • Add Clause: If Column Name is greater than or equal to 4, then Output FQ3

    • Else: Output FQ2

Note: We structured our if/then statements from largest to smallest number to get the correct output. If we had started with ">= 4", every month from 4-12 would have become FQ3.

  1. Click "OK." You'll see the logic works - for November (month 11), it correctly outputs "FQ1."

  2. Finally, in the Queries pane, right-click the last column you created (FiscalQuarter) and select "Drill Down." This tells Power Query that this single value is the only output we want from this query. The query result should now just show "FQ1".

Step 3: Convert the Query to a Function

You’ve done the hard part! Now, let’s turn that logic into a reusable tool.

  1. Right-click on your Fiscal Quarter Logic query.

  2. Select "Create Function..."

  3. A dialog box will pop up. Give your new function a clear name, like fnGetFiscalQuarter. The "fn" prefix is a common practice to make it easy to identify functions.

  4. Click "OK."

Just like that, Power Query packages your steps into a reusable function! You’ll see a new folder in your Queries pane with your function, marked with an fx symbol. It automatically identified that your logic depends on the SampleDateInput parameter and made that a required input for the function.

How to "Invoke Custom Function" on Your Data

With our fnGetFiscalQuarter function ready to go, we can now apply it to an entire column of data. Let's assume you have a Sales table with an Order Date column.

  1. Navigate to your main data query (e.g., Sales). This should be the table that contains the column you want to transform. In our case, it's the Order Date column.

  2. Select the "Add Column" tab in the ribbon. You’ll see a button labeled "Invoke Custom Function." Click it.

  3. The "Invoke Custom Function" dialog box will appear. This is where you connect your data to your function's "recipe:"

    • New column name: Give your new column a name, like Fiscal Quarter.

    • Function query: On the dropdown, select the function you just made: fnGetFiscalQuarter.

    • SampleDateInput (date): The dialog asks you to provide the input for the function. Choose "Column Name" from the dropdown, and then select the column you want to process - in this case, your Order Date column.

  4. Click "OK."

Presto! Power Query executes your fnGetFiscalQuarter function for every single row of the Order Date column. A new Fiscal Quarter column is added to your table, instantly calculating the correct value for thousands or even millions of rows in one step.

More Practical Use Cases

Once you get the hang of it, you'll see opportunities to use custom functions everywhere.

1. Standardizing Text Data

Imagine a column with manually entered names, full of inconsistent capitalization and random spaces. You can create a function called fnCleanText that:

You can then invoke this function on your 'First Name', 'Last Name', and 'City' columns to clean them all consistently with one click each.

2. Calling a Web API

Here’s a more advanced but powerful use. Say you have a list of ZIP codes and you want to get the city and state for each one using a free public API.

You could create a function fnGetLocationFromZip that takes a ZIP code, makes a web request to the API, and pulls out the city name from the response. By invoking this function on your ZIP code column, you can enrich your entire dataset with location details without having to manually look up a thing.

Tips for Success

  • Organize with Folders: When your report gets complex, group your custom functions into a dedicated folder in the Power Query pane to keep them separate from your data tables.

  • Use Descriptive Names: Name your functions (fnCleanNames) and parameters (dateToConvert) clearly. It's much easier to understand six months from now than fn1 and x.

  • Start Simple: Your first function doesn't need to be complex. Start with something small, like trimming spaces. You'll build confidence and quickly see the value.

  • Add Comments: In the Advanced Editor, you can use // to add comments to your M code. Explain what a complex step does so you can remember why you did it when you come back to it later.

Final Thoughts

Learning to create and invoke custom functions in Power BI is like gaining a superpower. It transforms tedious, manual data prep into an efficient, reliable, and automated process. By turning your most common transformation steps into reusable "recipes," you can clean and prepare data faster and with more confidence.

While Power BI's custom functions are a huge step up from manual steps, the process still requires getting comfortable with the Power Query interface and eventually writing some M code. When creating Graphed, we wanted to remove that layer of complexity entirely. We let you simply connect your data sources and describe the transformations or dashboards you need in plain English. Instead of building functional logic yourself, you can ask in seconds for a report that shows your business performance, and our AI builds it by handling all the complex data wrangling in the background, keeping your dashboards updated in real-time.