Power Query M

Power Query (M Language)

Overview

Power Query is the data transformation engine in Power BI, using the M functional language. It handles ETL (Extract, Transform, Load) from sources to the data model. Understanding query folding, step optimization, and M syntax is critical for performant data refresh.

Query Folding

Query folding translates M steps into native source queries (SQL, OData, etc.), pushing computation to the source instead of the mashup engine.

How to check folding: 1. Right-click a step in Applied Steps > "View Native Query" -- if grayed out, folding broke 2. Use Query Diagnostics (Tools > Start Diagnostics) to see what queries are sent

Steps that fold (common):

Steps that break folding:

Golden rule: Put foldable steps BEFORE non-foldable steps. Once folding breaks, all subsequent steps run in the mashup engine.

M Language Essentials

Let Expression (Query Structure)

Every Power Query query is a let...in expression:

let
    Source = Sql.Database("server", "database"),
    Filtered = Table.SelectRows(Source, each [Status] = "Active"),
    Renamed = Table.RenameColumns(Filtered, {{"OldName", "NewName"}}),
    Typed = Table.TransformColumnTypes(Renamed, {{"Amount", type number}})
in
    Typed

Data Types

Common Table Functions

// Filter rows
Table.SelectRows(table, each [Column] > 100)

// Add column
Table.AddColumn(table, "NewCol", each [Col1] * [Col2], type number)

// Remove columns
Table.RemoveColumns(table, {"Col1", "Col2"})

// Select columns (keep only these)
Table.SelectColumns(table, {"Col1", "Col2", "Col3"})

// Rename columns
Table.RenameColumns(table, {{"Old1", "New1"}, {"Old2", "New2"}})

// Change types
Table.TransformColumnTypes(table, {{"Col1", type number}, {"Col2", type text}})

// Replace values
Table.ReplaceValue(table, "old", "new", Replacer.ReplaceText, {"Column"})

// Group by
Table.Group(table, {"GroupCol"}, {
    {"Sum", each List.Sum([Amount]), type number},
    {"Count", each Table.RowCount(_), Int64.Type}
})

// Merge (JOIN)
Table.NestedJoin(left, {"KeyCol"}, right, {"KeyCol"}, "Merged", JoinKind.LeftOuter)

// Expand merged columns
Table.ExpandTableColumn(merged, "Merged", {"Col1", "Col2"})

// Pivot
Table.Pivot(table, List.Distinct(table[PivotCol]), "PivotCol", "ValueCol")

// Unpivot
Table.UnpivotOtherColumns(table, {"KeepCol1", "KeepCol2"}, "Attribute", "Value")

// Sort
Table.Sort(table, {{"Col1", Order.Ascending}, {"Col2", Order.Descending}})

// Remove duplicates
Table.Distinct(table, {"KeyCol1", "KeyCol2"})

// Combine/Append tables
Table.Combine({table1, table2, table3})

// Buffer (force materialization)
Table.Buffer(table)

List Functions

// Generate a sequence
{1..100}
List.Numbers(1, 100)
List.Dates(#date(2024,1,1), 365, #duration(1,0,0,0))

// Transform
List.Transform({1,2,3}, each _ * 2)

// Filter
List.Select({1,2,3,4,5}, each _ > 3)

// Aggregate
List.Sum(list), List.Average(list), List.Min(list), List.Max(list)

// Generate with custom logic (pagination pattern)
List.Generate(
    () => [Page = 0, Data = GetPage(0)],
    each [Data] <> null,
    each [Page = [Page] + 1, Data = GetPage([Page] + 1)],
    each [Data]
)

Parameters and Dynamic Sources

Create parameters for environment-specific connections:

// Define parameter in Power Query UI or M:
// Name: ServerName, Type: Text, Current Value: "prod-server.database.windows.net"

// Use in query:
let
    Source = Sql.Database(ServerName, DatabaseName),
    ...

Dynamic source pattern:

let
    BaseUrl = "https://api.example.com/v2/",
    Endpoint = BaseUrl & "data?page=",
    GetPage = (pageNum as number) =>
        let
            url = Endpoint & Number.ToText(pageNum),
            response = Json.Document(Web.Contents(url))
        in
            response[results],
    AllPages = List.Generate(
        () => [i = 1, res = GetPage(1)],
        each List.Count([res]) > 0,
        each [i = [i] + 1, res = GetPage([i] + 1)],
        each [res]
    ),
    Combined = List.Combine(AllPages),
    AsTable = Table.FromList(Combined, Record.FieldValues,
        type table [id = Int64.Type, name = text, value = number])
in
    AsTable

Error Handling

// Try/otherwise pattern
let
    result = try SomeRiskyOperation() otherwise "default"
in
    result

// Try with error record inspection
let
    attempt = try Number.FromText("abc"),
    output = if attempt[HasError]
        then "Error: " & attempt[Error][Message]
        else attempt[Value]
in
    output

// Replace errors in a column
Table.ReplaceErrorValues(table, {{"Column1", null}, {"Column2", 0}})

// Remove error rows
Table.RemoveRowsWithErrors(table, {"Column1", "Column2"})

Custom Connectors

Build custom Power Query connectors using the Power Query SDK:

1. Install Power Query SDK (VS Code extension) 2. Create a .mproj project with DataConnector.pq file 3. Implement the data source function with authentication 4. Package as .mez file 5. Deploy to Documents\Power BI Desktop\Custom Connectors or gateway

Basic connector structure:

section MyConnector;

[DataSource.Kind="MyConnector", Publish="MyConnector.Publish"]
shared MyConnector.Contents = (url as text) =>
    let
        source = Web.Contents(url),
        json = Json.Document(source)
    in
        json;

MyConnector = [
    Authentication = [
        Key = [],
        OAuth = [...]
    ],
    Label = "My Custom Connector"
];

MyConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = {"My Connector", "Connect to My Service"}
];

Performance Optimization

Additional Resources

Reference Files

• `references/m-patterns-cookbook.md` -- Common M patterns: web API pagination, incremental load, JSON flattening, CSV handling, SharePoint folder combine

M Language Patterns Cookbook

1. REST API Pagination

Offset-Based Pagination

let
    BaseUrl = "https://api.example.com/data",
    PageSize = 100,
    GetPage = (offset as number) =>
        let
            url = BaseUrl & "?limit=" & Number.ToText(PageSize)
                & "&offset=" & Number.ToText(offset),
            response = Json.Document(Web.Contents(url)),
            data = response[results]
        in
            data,
    AllPages = List.Generate(
        () => [i = 0, res = GetPage(0)],
        each List.Count([res]) > 0,
        each [i = [i] + PageSize, res = GetPage([i] + PageSize)],
        each [res]
    ),
    Combined = List.Combine(AllPages),
    AsTable = Table.FromRecords(Combined)
in
    AsTable

Cursor/Token-Based Pagination

let
    BaseUrl = "https://api.example.com/data",
    GetPage = (cursor as nullable text) =>
        let
            queryParams = if cursor = null then "" else "?cursor=" & cursor,
            url = BaseUrl & queryParams,
            response = Json.Document(Web.Contents(url)),
            data = response[items],
            nextCursor = try response[next_cursor] otherwise null
        in
            [Data = data, NextCursor = nextCursor],
    AllPages = List.Generate(
        () => GetPage(null),
        each [Data] <> null and List.Count([Data]) > 0,
        each GetPage([NextCursor]),
        each [Data]
    ),
    Combined = List.Combine(AllPages),
    AsTable = Table.FromRecords(Combined)
in
    AsTable

OData NextLink Pagination

let
    GetPage = (url as text) as table =>
        let
            source = Json.Document(Web.Contents(url)),
            data = Table.FromRecords(source[value]),
            nextLink = try source[#"@odata.nextLink"] otherwise null,
            result = if nextLink <> null
                then Table.Combine({data, @GetPage(nextLink)})
                else data
        in
            result,
    Output = GetPage("https://graph.microsoft.com/v1.0/users?$top=999")
in
    Output

2. JSON Flattening Patterns

Nested JSON Records

let
    Source = Json.Document(Web.Contents("https://api.example.com/orders")),
    AsTable = Table.FromRecords(Source),
    // Expand nested record column
    ExpandAddress = Table.ExpandRecordColumn(AsTable, "address",
        {"street", "city", "state", "zip"}),
    // Expand nested list of records
    ExpandItems = Table.ExpandListColumn(ExpandAddress, "items"),
    ExpandItemDetails = Table.ExpandRecordColumn(ExpandItems, "items",
        {"product", "quantity", "price"})
in
    ExpandItemDetails

Dynamic Column Expansion (Unknown Schema)

let
    Source = Json.Document(Web.Contents(url)),
    AsTable = Table.FromRecords(Source),
    // Get all column names from the nested record
    SampleRecord = AsTable{0}[nestedColumn],
    ColumnNames = Record.FieldNames(SampleRecord),
    Expanded = Table.ExpandRecordColumn(AsTable, "nestedColumn", ColumnNames)
in
    Expanded

Deeply Nested JSON

let
    Source = Json.Document(Web.Contents(url)),
    // Navigate to the data: response.data.results[*]
    Data = Source[data][results],
    AsTable = Table.FromRecords(Data),
    // Flatten level by level
    Level1 = Table.ExpandRecordColumn(AsTable, "details",
        Record.FieldNames(AsTable{0}[details])),
    Level2 = Table.ExpandListColumn(Level1, "tags"),
    Level3 = Table.ExpandRecordColumn(Level2, "metadata",
        Record.FieldNames(Level1{0}[metadata]))
in
    Level3

3. SharePoint Folder Combine Pattern

let
    Source = SharePoint.Files("https://tenant.sharepoint.com/sites/Team", [ApiVersion = 15]),
    // Filter to specific folder and file type
    Filtered = Table.SelectRows(Source, each
        Text.Contains([Folder Path], "Shared Documents/Data/")
        and [Extension] = ".xlsx"),
    // Add custom function to load each file
    AddContent = Table.AddColumn(Filtered, "Data", each
        let
            workbook = Excel.Workbook([Content], true),
            sheet = workbook{[Name="Sheet1"]}[Data]
        in
            sheet),
    // Remove file metadata, keep data
    RemoveCols = Table.SelectColumns(AddContent, {"Name", "Data"}),
    // Expand all tables
    Expanded = Table.ExpandTableColumn(RemoveCols, "Data",
        Table.ColumnNames(RemoveCols{0}[Data]))
in
    Expanded

4. Incremental Load Pattern (File-Based)

let
    // Parameters: LastRefreshDate (type date)
    Source = Folder.Files("\\server\share\data"),
    // Only load files modified since last refresh
    Filtered = Table.SelectRows(Source, each [Date modified] > LastRefreshDate),
    // Load each CSV
    LoadCSV = Table.AddColumn(Filtered, "Data", each
        Csv.Document([Content], [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])),
    Combined = Table.ExpandTableColumn(
        Table.SelectColumns(LoadCSV, {"Data"}),
        "Data",
        Table.ColumnNames(LoadCSV{0}[Data])
    ),
    Typed = Table.TransformColumnTypes(Combined, {
        {"Date", type date}, {"Amount", type number}
    })
in
    Typed

5. Handling Multiple CSV Formats

When CSV files in a folder have different schemas:

let
    Source = Folder.Files("C:\Data\CSVFiles"),
    FilterCSV = Table.SelectRows(Source, each [Extension] = ".csv"),
    LoadWithSchema = Table.AddColumn(FilterCSV, "Data", each
        let
            csv = Csv.Document([Content], [Delimiter=",", Encoding=65001]),
            promoted = Table.PromoteHeaders(csv, [PromoteAllScalars=true]),
            // Standardize column names across different formats
            standardized =
                if Table.HasColumns(promoted, "Revenue") then promoted
                else if Table.HasColumns(promoted, "Sales Amount") then
                    Table.RenameColumns(promoted, {{"Sales Amount", "Revenue"}})
                else promoted
        in
            standardized),
    Combined = Table.Combine(LoadWithSchema[Data])
in
    Combined

6. Web Scraping with HTML Parsing

let
    Source = Web.Page(Web.Contents("https://example.com/table-page")),
    // Web.Page returns a table of HTML tables found on the page
    DataTable = Source{0}[Data],  // First table on the page
    Promoted = Table.PromoteHeaders(DataTable, [PromoteAllScalars=true]),
    Cleaned = Table.TransformColumnTypes(Promoted, {
        {"Column1", type text}, {"Column2", type number}
    })
in
    Cleaned

7. Relative Date Filtering

let
    Source = ...,
    // Last N days
    LastNDays = Table.SelectRows(Source, each
        [Date] >= Date.AddDays(DateTime.Date(DateTime.LocalNow()), -30)),

    // Current month
    CurrentMonth = Table.SelectRows(Source, each
        Date.Year([Date]) = Date.Year(DateTime.Date(DateTime.LocalNow()))
        and Date.Month([Date]) = Date.Month(DateTime.Date(DateTime.LocalNow()))),

    // Rolling 12 months
    Rolling12M = Table.SelectRows(Source, each
        [Date] >= Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -12))
in
    Rolling12M

8. Custom Function Definition and Invocation

// Define a reusable function
let
    CleanText = (input as text) as text =>
        let
            trimmed = Text.Trim(input),
            lower = Text.Lower(trimmed),
            replaced = Text.Replace(lower, "  ", " ")
        in
            replaced,

    Source = ...,
    Applied = Table.TransformColumns(Source, {{"Name", CleanText}})
in
    Applied

9. Conditional Column with Complex Logic

let
    Source = ...,
    AddCategory = Table.AddColumn(Source, "Category", each
        if [Amount] > 10000 and [Region] = "US" then "High Value US"
        else if [Amount] > 10000 then "High Value International"
        else if [Amount] > 1000 then "Medium Value"
        else if [Amount] > 0 then "Low Value"
        else if [Amount] = 0 then "Zero"
        else "Credit/Return",
        type text)
in
    AddCategory

10. Cross-Join / Calendar Generation

let
    StartDate = #date(2020, 1, 1),
    EndDate = #date(2026, 12, 31),
    DayCount = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    Typed = Table.TransformColumnTypes(DateTable, {{"Date", type date}}),
    AddYear = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type),
    AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date]), Int64.Type),
    AddMonthName = Table.AddColumn(AddMonth, "MonthName", each Date.MonthName([Date]), type text),
    AddQuarter = Table.AddColumn(AddMonthName, "Quarter", each "Q" & Number.ToText(Date.QuarterOfYear([Date])), type text),
    AddWeekday = Table.AddColumn(AddQuarter, "Weekday", each Date.DayOfWeekName([Date]), type text),
    AddYearMonth = Table.AddColumn(AddWeekday, "YearMonth", each Date.ToText([Date], "yyyy-MM"), type text),
    AddFiscalYear = Table.AddColumn(AddYearMonth, "FiscalYear", each
        if Date.Month([Date]) >= 7 then Date.Year([Date]) + 1 else Date.Year([Date]), Int64.Type),
    AddIsWeekend = Table.AddColumn(AddFiscalYear, "IsWeekend", each
        Date.DayOfWeek([Date], Day.Monday) >= 5, type logical)
in
    AddIsWeekend

11. Handling Authentication Headers

let
    // API Key in header
    Source = Json.Document(Web.Contents("https://api.example.com/data", [
        Headers = [
            #"Authorization" = "Bearer " & ApiKey,
            #"Content-Type" = "application/json",
            #"X-Custom-Header" = "value"
        ],
        ManualStatusHandling = {400, 401, 404, 500}
    ])),
    // Check for errors
    StatusCode = Value.Metadata(Source)[Response.Status],
    Result = if StatusCode = 200 then Source else error "API returned " & Number.ToText(StatusCode)
in
    Result

12. Privacy Levels and Query Folding

Privacy levels can block query folding between sources:

Fix folding issues: Set appropriate privacy levels in Data Source Settings, or set "Ignore Privacy Levels" (development only, not recommended for production) in Options > Privacy.