power-query-m

name

power-query-m

description

Power Query M language for ETL, transformations, and connector authoring. PROACTIVELY activate for: (1) writing Power Query M code, (2) query folding and Native Query verification, (3) data transformations (merge, append, pivot, unpivot, split column), (4) custom connector development, (5) Power Query parameters and dynamic source patterns, (6) performance optimization (folding, query reduction, partition strategy), (7) Table.TransformColumns, List.Generate, Record.Field functions, (8) error handling in M (try/otherwise, error records), (9) mashup engine internals, (10) ETL pipelines from folder/SharePoint/database sources. Provides: M language reference, query-folding diagnosis, transformation recipes, dynamic source patterns, error-handling templates, and performance tuning techniques.

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):

Operation	SQL Translation
Remove columns	SELECT (column list)
Filter rows	WHERE clause
Sort rows	ORDER BY
Group by	GROUP BY
Rename columns	Column aliases
Change type (basic)	CAST
Merge queries (database)	JOIN
Top N rows	TOP / LIMIT
Remove duplicates	DISTINCT

Steps that break folding:

Operation	Why
Add custom column (complex)	M expression cannot translate to SQL
Pivot/Unpivot (sometimes)	Depends on source capability
Merge with non-foldable source	Cannot push cross-source joins
Table.Buffer	Explicitly materializes in memory
Reorder after custom step	Once broken, subsequent steps cannot fold
Date/time transforms (some)	Source-specific function differences

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

M Type	Description
type text	String/text
type number	Decimal number
Int64.Type	Whole number (64-bit integer)
type date	Date only
type datetime	Date and time
type datetimezone	Date, time, and timezone
type duration	Time duration
type logical	Boolean (true/false)
type binary	Binary data
type null	Null value
Currency.Type	Fixed decimal (4 places)
Percentage.Type	Percentage

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

Technique	Impact
Put foldable steps first	High -- pushes work to source
Remove unused columns early	High -- reduces data volume
Filter early, before joins	High -- reduces row count
Avoid Table.Buffer unless needed	Medium -- prevents unnecessary materialization
Use native queries when folding fails	High -- bypass mashup engine
Disable "Include in report refresh" for staging queries	Medium -- skips unnecessary refresh
Use Table.Partition for parallel loading	Medium -- parallelizes large tables
Set Privacy Levels correctly	Medium -- incorrect levels block folding

Additional Resources

Reference Files

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