Power BI Advanced — Introduction to the M Language: Power Query Behind the Scenes

Power BI Advanced Series · Power Query & M Language · by Raushan Ranjan, MCT

At its heart, M programming is built on Microsoft's Power Query technology, powered by the flexible Power Query Mashup Engine. This engine is designed for ETL (extraction, transform, load) logic, whether you're pushing data into a Power BI dataset or a data flow. Its versatility means it works seamlessly across various platforms, including Power BI Desktop, Power BI Service, gateways, and Excel.

You'll often interact with M in the Power Query Editor. While the formula bar can be toggled on or off, it's where you'll see the M code generated by your clicks. Queries are essentially a sequential list of "steps," each created by interacting with the UI. For better organization, queries can even be grouped. You'll get your first glimpse of M when creating conditional columns, and thankfully, the Advanced Query Editor now boasts color coding and IntelliSense, significantly improving the coding experience.

Why Learn M Programming?

You might wonder, "Why bother with M when the Query Editor does so much?" Here's why:

• Beyond the UI: M allows you to accomplish tasks not possible with the Query Editor alone, such as creating reusable query functions, performing complex calculations across rows, or handling dynamic SharePoint list GUIDs.
• Independent Versioning: Saving M code in .m files and checking them into source control enables independent versioning of your queries, a crucial practice for collaborative development.
• Code Reusability: M code is highly portable; you can easily copy and paste it between different projects, saving time and ensuring consistency.
• Expert Insights: For deep dives into Power Query and M code, Ted highly recommends Chris Webb's blog, a renowned authority in the field.

M Programming Language Fundamentals

M is a functional language, meaning lines of code evaluate expressions rather than performing operations that change data in place. It works with immutable data structures, so each step's evaluation depends on the previous step's evaluation without modifying the original data. Every query in M ultimately returns a single value of a particular type.

Keep in mind that M is case-sensitive, similar to languages like C#. Also, query expressions can reference other queries by name, allowing for modular and interconnected data flows.

The let Statement

A core construct in M is the let statement. It's a single expression that returns a single value. Each line within the let block represents a separate expression assigned to a variable. A crucial syntax rule is that every line in a let block, except the last one, must end with a comma (,).

Variable Names and Comments

Comments are essential for code readability. In M, you can use // for single-line comments or /* ... */ for multi-line comments.

When naming variables (steps), it's best practice to avoid spaces. If a variable name contains spaces, it requires special handling (e.g., #"..."), which can make the M code appear "ugly." Sticking to names without spaces keeps your M code cleaner and more readable.

Query Evaluation Flow

The Mashup Engine evaluates queries from the bottom up. It starts with the expression specified after the in keyword and then triggers the evaluation of any dependent variables. While M allows backward references in let statements, they can sometimes confuse the Power Query designer, so it's generally advisable to avoid them for clarity.

Error Handling

Errors are inevitable in data processing. M provides the try ... otherwise block as the primary mechanism for catching and handling errors gracefully. This allows you to define fallback behavior, such as returning null instead of letting an error halt your query.

Concatenation Operator (&)

The ampersand (&) acts as a flexible "combination operator" in M. It's used for concatenating text strings, combining lists, and merging records, making it a versatile tool for various data manipulation tasks.

Richer Data Structures in M

Lists: The Single-Column Collection

Created using curly braces {}. Elements are accessed using curly braces with a zero-based index (e.g., ListName{0} for the first element). A useful trick is ListName{index}?, which returns null if the index is out of range, preventing errors. Lists are excellent for data cleansing, such as defining allowed characters to strip from text.

let
    MyNumbers = {1, 2, 3},
    FirstNumber = MyNumbers{0}
in
    FirstNumber

Records: The Single-Row Structure

Created using square brackets [] with field names and values (e.g., [FieldName = Value]). Field values are accessed using square brackets (e.g., RecordName[FieldName]). Records are frequently required as parameters for M function calls.

let
    MyRecord = [Name = "Raushan", City = "Noida"],
    CityValue = MyRecord[City]
in
    CityValue

Tables

While you primarily work with tables in Power Query, you can create them from a list of records using Table.FromRecords. It's highly beneficial to strongly type columns using type table [Column1 = type text, ...], especially when dealing with inconsistent data sources like Excel files with varying column counts.

Adding an "Index Column" to a table is a common technique to enable calculations across rows, such as running totals. This allows you to reference rows based on their index, which can even be used to stop query execution when a certain threshold is met.

let
    MyTable = Table.FromRecords({
        [Name = "Alice", Age = 30],
        [Name = "Bob", Age = 24]
    }),
    TypedTable = Table.TransformColumnTypes(MyTable, {
        {"Name", type text},
        {"Age", type number}
    })
in
    TypedTable

The each Keyword: Simplifying Functions

The each keyword is a powerful shorthand that simplifies writing unary functions (functions with one parameter), particularly when passing a function to operations like Table.SelectRows. When you use each, the parameter is implicitly named _ (underscore), representing the current record or item being processed.

• For records, you can directly reference a field name (e.g., [FieldName]) or explicitly use _ (e.g., _.FieldName).
• For lists of non-record types (e.g., lists of strings), _ refers directly to the current item itself.

let
    SourceTable = Table.FromRecords({
        [Name = "Alice", Age = 30],
        [Name = "Bob", Age = 24],
        [Name = "Charlie", Age = 35]
    }),
    FilteredRows = Table.SelectRows(SourceTable, each [Age] > 25)
in
    FilteredRows

Query Folding: Optimizing Performance

Query folding is a critical concept for performance optimization in Power Query. Its goal is to push as much work as possible back to the data source, reducing the amount of data transferred to the Mashup Engine. This includes pushing operations like WHERE, ORDER BY, SELECT, and RENAME clauses to the source database.

Query folding is primarily supported for relational databases, tabular/multi-dimensional databases, and OData web services. Factors like M code structure, data source privacy levels, and native query execution can affect whether folding occurs. To enable folding, place steps like Table.SelectRows, Table.SelectColumns, and Table.RenameColumns close to your initial data source step.

While using native SQL queries (e.g., Sql.Database(...) with a Query parameter) typically bypasses automatic query folding, recent updates have introduced special techniques to allow folding even with native queries.

M Function Library & Web Data Sources

M boasts a comprehensive function library covering a wide array of operations, including list functions, date/time functions, and table functions.

When dealing with web data sources, you often choose between OData.Feed and Web.Contents:

• OData.Feed: Simplifies data ingestion from OData services by automatically discovering metadata and table structures. However, it can make multiple metadata calls at runtime, potentially leading to less efficient data retrieval.
• Web.Contents: Allows direct calls to web services. While it doesn't support query folding, it can be more efficient for RESTful services by letting you construct URLs with query parameters (e.g., $select, $filter) to retrieve specific data in a single call. When using Web.Contents with JSON, you'll typically need to manually parse the JSON document and convert it into tables and expand columns. Tools like Fiddler can be invaluable for observing the network calls made by Power Query.

Function Queries: Reusability and Modularity

Function queries are a powerful feature that allows you to parameterize existing queries, making them reusable across different queries or projects. To create one, open the Advanced Editor, add a parameter list (e.g., (Input as text) =>), and then include your let statement. These can be invoked by going to "Add Column" > "Invoke Custom Function" and passing a column as the input. Function queries are excellent for modularizing and reusing your M logic.

// Example of a simple M function query
(InputText as text) =>
let
    ConvertedText = Text.Upper(InputText)
in
    ConvertedText

Designing Query Parameters: Dynamic Data Solutions

Query parameters (or dataset parameters) are essential for creating flexible Power BI solutions that can adapt to different environments or deployments. Parameters are managed in the Power Query window via "Manage Parameters."

Key practices for parameters:

• Strong Typing: Always strongly type your parameters instead of leaving them as "Any."
• Direct Referencing: Parameters can be referenced directly in M code and within dialogues in the Power Query designer.
• Common Uses: They are commonly used for dynamically setting database names, server names, or filter criteria.
• Updating Parameters: Parameters can be updated in the Power BI Service, via PowerShell, or through the Power BI Service REST API.
• Report Integration: Parameters can even be used directly within Power BI reports (e.g., in cards) or in DAX code by enabling "Enable Load" for the parameter.
• PBIT Files: A .pbit (Power BI Template) file is a parameterized project exported for reuse. When opened, a .pbit file with parameters prompts the user to select parameter values before creating a new .pbix file. This is incredibly useful for creating many .pbix files from a single template by simply swapping out parameter values.

Conclusion: Elevate Your Power BI Skills

Understanding the M programming language is a game-changer for anyone working with Power BI. It provides the depth and flexibility needed to tackle complex data challenges, optimize performance, and build truly robust and reusable data solutions. By mastering these fundamentals, you'll not only become a more efficient data professional but also unlock new possibilities in your Power BI journey.