Optimizing M Code: Best Practices for Faster Data Transformations

Image by Editor
 

Optimizing Power Query (M) code is essential when working with large datasets. M code powers the data transformations, which is used across Power BI, Excel, and other Microsoft data tools. While the visual interface makes building transformations easy, optimizing performance often requires understanding and refining the underlying M code. Properly structured M code ensures smoother, faster data transformations and efficient memory use.

In this tutorial, we will show the best practices and impactful optimization techniques to help you achieve faster data processing.

1. Limit Rows Early

When dealing with millions of rows, unnecessary data can quickly bog down memory and processing time. Always start by trimming your data to the essentials. Reduce the amount of data at the beginning of your query using Table.FirstN() or filtering functions.

M Code:

let
   Source = Excel.Workbook(File.Contents("Data.xlsx"), null, true),
   Data = Source{[Name="Sales"]}[Data],
   FirstRows = Table.FirstN(Data, 1000)
in
   FirstRows

 Benefit:

• Speeds up processing by reducing the volume of data loaded and transformed.
• It helps preview and debug a smaller dataset.

Tip: Use this during development or when you only need a specific subset for analysis (e.g., top 1000 recent records).

2. Filter Before Expanding Tables

One of the simplest and most effective optimization techniques is to reduce your data volume as early as possible. Nested tables (e.g., from JSON, APIs, or merged queries) often contain a lot of irrelevant data. Expanding before filtering means more rows, more memory, and slower performance.

 M Code:

let
   Source = Excel.Workbook(File.Contents("Sales.xlsx")),
   Data = Source{[Name="Sales"]}[Data],

   // Remove unnecessary columns early
   SelectedColumns = Table.SelectColumns(Data, {"Date", "Product", "Quantity", "Price"}),

   // Filter early to reduce data volume
   FilteredRows = Table.SelectRows(SelectedColumns, each [Date] >= #date(2023, 1, 1))
in
   FilteredRows

Every subsequent transformation step will process less data, resulting in significant performance improvements.

 Benefit:

• Prevents expansion of irrelevant rows, which reduces memory and time complexity.
• Keeps query logic cleaner.

3. Minimize Column Operations

Loading more columns than needed results in bloated memory use and redundant transformations. Always select only the necessary fields. Select only the necessary columns early in your query using the Table.SelectColumns.

 M Code:

let
   ReducedColumns = Table.SelectColumns(PreviousStep, {"Date", "Sales", "Profit"})
in
   ReducedColumns

 Benefit:

• Reduces data size and improves performance.
• Makes the query easier to read and maintain.
• Reduces RAM usage.
• Speeds up data model refresh.
• Helps maintain data privacy by excluding sensitive fields.

4. Avoid Redundant Calculations

Calculating the same value multiple times across steps slows performance and increases chances of error. Avoid repeating expensive computations by storing results once and reusing them.

 M Code:

let
   CalculatedSales = Table.AddColumn(PreviousStep, "Total", each [Units] * [Price]),
   Filtered = Table.SelectRows(CalculatedSales, each [Total] > 1000)
in
   Filtered

 Benefit:

• Prevents duplicated computation.
• Improves readability and reusability of the logic.
• Optimizes processing time.

5. Maximize Query Folding

Query folding is the process where Power Query translates your M code operations into native queries that can be executed directly by the data source. This is the single most important concept for optimization. You can use query folding-compatible steps (like Table.SelectRows, Table.Group) that can be translated into SQL or backend operations.

Maximizing Query Folding:

let
   Source = Sql.Database("server", "database"),

   // Use native SQL instead of multiple transformation steps
   Query = Value.NativeQuery(Source, "SELECT * FROM Sales WHERE Year = 2023")
in
   Query

 Benefit:

• Pushes computation to the data source, making transformations faster and more efficient.
• Reduces load on local memory.

Pro Tips:

• Right-click on a step in Power Query Editor >> select View Native Query to check if folding is occurring.
• Keep foldable steps like filtering, grouping, and selecting columns as early as possible before applying steps like AddColumn or Custom Functions.
• For SQL sources, prioritize operations that translate well to SQL, like filtering, column selection, and grouping.

6. Avoid Complex Custom Functions

Custom M functions are powerful, but when overused or deeply nested, they break folding and degrade performance. Avoid overly complex or nested custom functions. Use built-in alternatives where possible.

 M Code:

let
   Transformed = Table.TransformColumns(PreviousStep, {{"Date", Date.From}})
in
   Transformed

 Benefit:

• Improves execution time and simplifies debugging.
• Built-in functions are optimized and usually foldable.

7. Prefer Set Operations Over Row Operations

Set-based operations process the whole table efficiently, avoiding row-by-row logic like List.Generate where unnecessary. Row-by-row operations like List.Generate or List.Accumulate is computationally expensive. You can use table-wide (set-based) transformations instead.

 M Code:

let
   TransformDates = Table.TransformColumns(PreviousStep, {{"Date", Date.Year}})
in
   TransformDates

 Benefit:

• Significantly faster performance with large datasets.
• Avoids unnecessary iteration overhead.

8. Efficiently Handle Errors

Unchecked errors can crash queries or silently fail. Use lightweight conditional logic to gracefully handle exceptions. Avoid slowdowns due to unhandled errors.

M Code:

let
   SafeDivide = Table.AddColumn(PreviousStep, "Ratio", each if [Denominator] <> 0 then [Numerator] / [Denominator] else null)
in
   SafeDivide

 Benefit:

• Prevents query failure due to division by zero or null values.
• Improves reliability and user trust in reports.

Tip: Avoid wrapping every step in try…otherwise unless necessary, it slows down performance.

9. Optimize Data Types

Delaying type detection forces Power Query to infer types, which is slower and often inaccurate. Set data types early in the query to help Power Query process and store data efficiently.

 M Code:

let
   TypedTable = Table.TransformColumnTypes(PreviousStep, {{"Sales", type number}, {"Date", type date}})
in
   TypedTable

 Benefit:

• Enhances performance by reducing type inference overhead.
• Prevents unexpected errors in downstream steps.

10. Avoid Common Performance Pitfalls

Avoid Unnecessary Sorting: Sorting is expensive and often breaks query folding. Only sort when needed for your final output.

Expand Tables at the Right Time: It is more efficient to filter before expanding the table.

let
   Source = Excel.Workbook(File.Contents("Sales.xlsx")),
   Orders = Source{[Name="Orders"]}[Data],

   // Filter the smaller table first
   FilteredOrders = Table.SelectRows(Orders, each [Date] >= #date(2023, 1, 1)),

  // Then expand only the filtered rows
  ExpandedOrders = Table.ExpandTableColumn(FilteredOrders, "Products", {"Product", "Quantity", "Price"})
in
   ExpandedOrders

Limit Custom Function Calls in Row Operations: Custom functions called for each row can dramatically slow down processing. Try to restructure your logic to use table operations instead.

11. Disable Load for Intermediate Queries

Queries that serve only as inputs for other queries should not load into the model. Intermediate queries used solely in other queries should have load disabled to avoid memory waste.

 Steps:

• Right-click on the query in the Queries pane.
• Uncheck “Enable Load”.

 Benefit:

• Frees up memory and improves workbook performance.
• Keeps the data model clean and lightweight.

Test Performance Improvements

You can measure the impact of your optimizations by following the above optimizations.

let
   StartTime = DateTime.LocalNow(),
   // Your query steps
   Source = /* your query logic here */,

   EndTime = DateTime.LocalNow(),
   Duration = Duration.TotalSeconds(EndTime - StartTime),
  // Add execution time as metadata
  Result = Value.Metadata(Source, [ExecutionSeconds = Duration])
in
   Result

Conclusion

Optimizing M code involves limiting data as early as possible, minimizing redundancy, leveraging query folding, and using set-based operations efficiently. By adopting these best practices, your Power Query transformations will run faster, handling larger datasets with ease and efficiency. This approach ensures your M code performs well even under heavy datasets or in enterprise-level BI environments.