Top 5 Reusable M Code Functions for Power BI/Excel (Power Query)

Power BI’s Power Query (M language) allows creating custom functions and reusable query steps to simplify common tasks. Below are five versatile M-code solutions – a mix of data transformation helpers and analysis utilities – that you can copy into your Power BI queries with minimal adjustments. Each function includes the full M code, an explanation of its purpose, and an example use case in real-world projects.

Explore the Top 5 essential M-Code functions to enhance your data processing skills in Power Query, featuring useful icons for quick reference.

1. Excel-Style TRIM Function (Remove Extra Spaces)

Purpose: This custom function removes excess whitespace (or any specified character) from a text string. It splits the text by the target character, filters out empty segments, then rejoins them, effectively collapsing multiple spaces into one. This mimics Excel’s TRIM function, which removes leading/trailing spaces and reduces consecutive internal spaces to a single space. (By contrast, Power Query’s built-in Text.Trim removes only leading/trailing spaces, and Text.Clean removes non-printable/control characters but doesn’t address extra internal spaces.)

Real-World Use Case: Use ExcelTrim to clean up messy text data such as names or addresses. For example, " Acme Corp " would be transformed to "Acme Corp", standardizing your text fields for analysis (e.g. ensuring consistent company names in a dimension table).

2. Dynamic Unpivot Transformation

Purpose: This function unpivots a table dynamically, converting all non-key columns into attribute-value rows. In Power Query, the UI’s Unpivot tool generates code with a hard-coded list of column names to unpivot. That static approach breaks when new columns appear (e.g. a new year in your data) – they remain unpivoted in the refresh. DynamicUnpivot avoids this by retrieving column names at runtime and removing the specified key columns from the list. The remaining columns are then unpivoted, so your query adapts to schema changes automatically.

Real-World Use Case: Suppose you import a sales forecast table with columns for each month (“Jan”, “Feb”, etc.). Using DynamicUnpivot, you can convert month columns into rows (e.g. a column “Month” and a column “SalesValue”) without listing every month. If next year’s data adds a “Jan-NextYear” column, it will be unpivoted automatically, ensuring no data is left behind when you refresh the report.

3. Adding a Conditional Flag Column (e.g. Missing Data Flag)

Purpose: This transformation creates a flag column (binary indicator 0/1) based on a condition – in this case, whether a row has any missing values. It uses Record.FieldValues to turn the row record into a list of values, then checks if any value in that list is null or an empty text (after trimming) using List.AnyTrue. If any field is blank, the flag is set to 1, otherwise 0. You can modify the condition logic to flag other scenarios (e.g. values above/below a threshold or custom business rules).

Real-World Use Case: Use a missing-data flag to quickly identify incomplete records. For example, in a customer info table, add HasMissingData = 1 for any customer record with any null or empty field. This flag can drive visuals or filters in Power BI (highlighting records that need data cleanup) or be used in subsequent query steps to remove or fix incomplete rows before loading the data.

4. Identifying Rows with Errors

Purpose: This helper isolates error-containing rows in your query. Power Query’s Table.SelectRowsWithErrors function returns only those rows that have an error in at least one cell. You can optionally specify a list of columns to check (if not provided, it checks all columns). By filtering your dataset to just the problematic rows, you can inspect or log these errors, or apply remedial transformations.

Real-World Use Case: Imagine you’re importing a CSV and some rows have “Error” in a converted column (e.g. a text value in a numeric field causing a conversion error). By using FindErrorRows, you can pull out all erroring rows for review. For instance, you might output them to a separate Power BI table or CSV to investigate data issues, ensuring data quality before performing further analysis.

5. Adding a Rank Column to a Table

Purpose: This function appends a rank number to each row of a table, based on one or more sorting criteria. It leverages Power Query’s Table.AddRankColumn (available since the June 2022 update) to rank the data. You provide a list of columns with sort directions (e.g. { {"Revenue", Order.Descending} } to rank by Revenue highest to lowest). By default, the example above uses RankKind.Competition, meaning ties get the same rank and the next rank skips ahead (e.g. 1, 1, 3, 4). Other ranking methods like Dense (no gaps in sequence) are available as well. If you include multiple sort keys (e.g. {"Region", Order.Ascending}, {"Sales", Order.Descending}), the ranking will be partitioned by the first keys – effectively giving a rank within each Region in this example.

Real-World Use Case: Use ranking to create top-N reports or flags. For example, add a RevenueRank column to rank customers by sales amount, so you can easily filter to the top 10 customers. Similarly, by ranking within groups (e.g. rank sales by product within each category), you can identify the best-selling products in each category. This pre-calculation in Power Query can simplify DAX or visualization logic when you need to highlight leaders/laggards in your data.

Check these products for better querying.

Power Query Toolkit

Buy Now

Advanced Calendar (reusable function)

Buy Now