Power Query is a powerful tool for transforming and shaping data, but when you perform repetitive tasks, manually applying the same transformations repeatedly can be time-consuming. That’s where custom functions come in. By creating custom functions in Power Query, you can automate repetitive tasks, simplify your queries, and improve overall efficiency.

In this blog, we’ll explore the steps to create, implement, and use custom functions in Power Query, with examples to illustrate how they can streamline your workflow.

1. What Are Custom Functions in Power Query?

Custom functions in Power Query are user-defined functions that you can create to encapsulate specific logic or transformations. These functions can then be reused across multiple queries or datasets, making your data preparation process more efficient.

For example, instead of applying the same column transformation to multiple tables manually, you can create a custom function that automatically applies the desired transformation whenever needed.

2. Why Create Custom Functions in Power Query?

Custom functions are useful for:

  • Reusability: Write a function once and use it multiple times.
  • Efficiency: Reduce the need for repetitive manual transformations.
  • Consistency: Apply consistent logic across different datasets.
  • Simplified Queries: Reduce the number of steps in the Query Editor.

3. Creating a Custom Function: Step-by-Step Guide

Follow these steps to create and use a custom function in Power Query.

Step 1: Write the Logic in Power Query

Start by performing the transformation logic manually on a sample dataset. For example, suppose you want to remove leading and trailing spaces from a text column.

  1. Open Power Query Editor (in Power BI or Excel).
  2. Load a sample table.
  3. Apply the desired transformation (e.g., trimming spaces from a text column).
  4. Take note of the M code generated in the Advanced Editor for that transformation.
mCopyEdit= Table.TransformColumns(YourTable, {{"Column1", Text.Trim}})

Step 2: Convert the Logic into a Function

Once you’ve created the desired transformation, turn it into a reusable function.

  1. Go to Home > Advanced Editor in Power Query.
  2. Modify the M code to make it a parameterized function.
    Example:
mCopyEditlet  
    TrimText = (inputTable as table, columnName as text) =>  
    let  
        TransformedTable = Table.TransformColumns(inputTable, {{columnName, Text.Trim}})  
    in  
        TransformedTable  
in  
    TrimText

Explanation:

  • TrimText is the name of the custom function.
  • inputTable is a parameter representing the table you want to apply the transformation to.
  • columnName is a parameter representing the column name to be trimmed.
  • Table.TransformColumns performs the actual transformation using these parameters.

Step 3: Invoke the Custom Function

To use your new custom function:

  1. Load a new dataset in Power Query.
  2. Go to Add Column > Invoke Custom Function.
  3. Select the function you created and specify the required parameters (e.g., table and column name).

The custom function will apply the transformation logic automatically!

4. Real-Life Examples of Custom Functions

Here are some examples of how custom functions can be used in real-world scenarios:

Example 1: Standardizing Date Formats

Create a custom function to standardize date formats across multiple tables.

mCopyEditlet  
    StandardizeDateFormat = (inputTable as table, columnName as text) =>  
    let  
        TransformedTable = Table.TransformColumns(inputTable, {{columnName, each Date.ToText(_, "yyyy-MM-dd")}})  
    in  
        TransformedTable  
in  
    StandardizeDateFormat

Example 2: Removing Special Characters from Text

Create a custom function to remove special characters from text columns.

mCopyEditlet  
    RemoveSpecialCharacters = (inputTable as table, columnName as text) =>  
    let  
        TransformedTable = Table.TransformColumns(inputTable, {{columnName, each Text.Remove(_, {"@", "#", "$", "%", "&"})}})  
    in  
        TransformedTable  
in  
    RemoveSpecialCharacters

5. Best Practices for Creating Custom Functions

To get the most out of your custom functions, follow these best practices:

Use Descriptive Names

Choose function names that clearly describe their purpose, e.g., NormalizeTextCase or RemoveDuplicatesFromTable.

Test with Sample Data

Before using the function on large datasets, test it on smaller sample tables to ensure it works as expected.

Document Your Functions

Add comments in the Advanced Editor to explain what the function does, especially if it involves complex logic.

mCopyEdit// This function removes leading and trailing spaces from the specified text column  
let  
    TrimText = (inputTable as table, columnName as text) => ...

Avoid Hardcoding Values

Use parameters to make your functions flexible and adaptable to different datasets.

6. Saving and Sharing Custom Functions

To share your custom functions across different projects or users:

  1. Save them in a Power BI Template (.pbit) – This allows other users to reuse the custom functions when they create reports from the template.
  2. Save them in a Power Query file – You can copy and paste the M code into other Power Query projects.
  3. Create Power Query Custom Function Queries – These can be saved as part of your query file and shared as needed.

Conclusion

Creating custom functions in Power Query can greatly enhance your productivity by automating repetitive tasks, ensuring consistent data transformations, and improving query efficiency. Whether you’re standardizing text, cleaning data, or applying complex logic, custom functions give you the flexibility and reusability needed for efficient data preparation.

Start creating your own custom functions today and transform the way you work with data in Power BI and Excel!