Writing custom functions in M Language (used in Power Query) allows users to automate repetitive tasks, simplify complex transformations, and improve query efficiency. Custom functions help in modularizing logic, making Power Query more powerful and flexible for data transformation in Power BI and Excel.
Writing Custom Functions in M
Custom functions in Power Query help automate repetitive tasks and improve data processing efficiency. This guide explains how to create and use custom functions in M Language.1. Understanding M Language Functions
M functions in Power Query follow a structured format:
(Value1 as type, Value2 as type) => let result = Value1 + Value2 in result Key Components:
- Parameters: Input values for the function.
- Let Expression: Defines the function logic.
- Return Value: The final computed result.
2. Creating a Simple Custom Function
A basic function to multiply two numbers:
(MultiplyBy as number) => let result = MultiplyBy * 2 in result How to Use It
Open Power Query Editor.
Click Home > Advanced Editor and paste the function.
Call the function in a query:
MultiplyByTwo = MyFunction(5) // Returns 10 3. Creating a Function for String Formatting
A function to capitalize the first letter of a text:
(CapitalizeText as text) => let FirstLetter = Text.Upper(Text.Start(CapitalizeText, 1)), RemainingText = Text.Lower(Text.Middle(CapitalizeText, 1)), Result = FirstLetter & RemainingText in Result Example Usage:
CapitalizeText("power query") // Returns "Power query" 4. Using Functions in Table Transformations
Applying a function to a column in a table:
Table.AddColumn(Source, "NewColumn", each MyFunction([OldColumn])) 5. Creating a Date Transformation Function
A function to calculate the number of days between two dates:
(Date1 as date, Date2 as date) => let DaysDifference = Duration.Days(Date2 - Date1) in DaysDifference Example Usage:
DaysBetween(#date(2024,1,1), #date(2024,2,1)) // Returns 31 6. Error Handling in Custom Functions
Using try…otherwise to handle errors:
(SafeDivide as number, Divisor as number) => let Result = try SafeDivide / Divisor otherwise "Error: Division by Zero" in Result Example Usage:
SafeDivide(10, 0) // Returns "Error: Division by Zero" 7. Reusing Functions in Multiple Queries
To reuse a function in multiple queries:
Save it as a separate query in Power Query Editor.
Reference it in other queries using:
NewQuery = FunctionName(Parameter) Conclusion
Custom functions in M Language enhance Power Query by automating tasks, simplifying transformations, and improving efficiency. By writing reusable functions for calculations, text processing, and data cleaning, users can streamline workflows and optimize Power BI and Excel reports.
