Power Query

Power Query for Semantic Models

Author, validate, and test Power Query M expressions in semantic model import partitions. Covers writing correct M code, preserving query folding, validating expressions, and testing them by executing against real data sources.

Partition Expressions

Each import table in a semantic model has a partition with an M expression defining what data gets loaded during refresh. The expression typically connects to a data source, navigates to a table/view, and applies transformations.

Structure of a Partition Expression

let
    Source = Sql.Database(#"SqlEndpoint", #"Database"),
    Data = Source{[Schema="dbo", Item="Orders"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(Data, {"InternalId"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns", {{"Amount", Currency.Type}})
in
    #"Changed Type"

Key elements:

• Parameters: #"SqlEndpoint", #"Database" are shared M parameters defined at the model level
• Navigation: Source{[Schema="dbo", Item="Orders"]}[Data] navigates to a specific table
• Steps: Each step is a named variable in the let...in chain
• Quoted identifiers: Step names with spaces use #"Step Name" syntax

Extracting Expressions

# Get partition expression from TMDL via fab
fab get "<Workspace>.Workspace/<Model>.SemanticModel" -f \
  -q "definition.parts[?path=='definition/tables/<Table>.tmdl'].payload"

# Get shared M parameters
fab get "<Workspace>.Workspace/<Model>.SemanticModel" -f \
  -q "definition.parts[?path=='definition/expressions.tmdl'].payload"

Writing M Expressions

Query Folding

Query folding is the most important performance concept. The M engine translates compatible steps into native data source queries (e.g., SQL). When folding breaks, subsequent steps run in the mashup engine, pulling all data into memory first.

Steps that typically fold (for SQL sources):

• Table.SelectColumns / Table.RemoveColumns -> SELECT
• Table.SelectRows -> WHERE
• Table.Sort -> ORDER BY
• Table.FirstN -> TOP
• Table.Group -> GROUP BY
• Table.RenameColumns -> AS aliases

Steps that may or may not fold (source-dependent):

• Table.TransformColumnTypes -- frequently breaks folding for text-to-numeric/date conversions on SQL Server sources. Use Table.TransformColumns with explicit conversion functions (e.g., Number.From) as a more reliable foldable alternative.

Steps that break folding:

• Table.AddColumn with custom M functions (not translatable to SQL)
• Table.Buffer (forces materialization; prefer Table.StopFolding to stop folding without the memory overhead)
• Table.LastN (no SQL equivalent without subquery)
• Table.Combine across different data sources (cross-database folding within the same SQL Server is possible via EnableCrossDatabaseFolding)
• Complex each expressions with M-specific logic
• Any step after a fold-breaking step

Best practice: Apply folding-compatible steps (filter, select, type) early; add custom columns and M-only transforms after all foldable work is done.

Column Pruning and Row Filtering

Remove unused columns and filter rows as early as possible:

let
    Source = Sql.Database(SqlEndpoint, Database),
    Data = Source{[Schema="dbo", Item="Orders"]}[Data],
    // Filter and select BEFORE any custom transforms
    #"Filtered" = Table.SelectRows(Data, each [Status] <> "Cancelled"),
    #"Selected" = Table.SelectColumns(#"Filtered", {"OrderId", "Date", "Amount", "CustomerId"})
in
    #"Selected"

These steps fold to SQL: SELECT OrderId, Date, Amount, CustomerId FROM dbo.Orders WHERE Status <> 'Cancelled'

Type Handling

• Apply Table.TransformColumnTypes early (folds to CAST in SQL)
• Use explicit M types: Int64.Type, type text, type date, Currency.Type, type logical
• Avoid implicit type inference on large datasets

Naming Conventions

• Step names should describe the transformation: #"Removed Duplicates", #"Filtered Active"
• Avoid generic names like #"Custom1" or #"Step1"
• Use quoted identifiers #"Name" for steps with spaces (Power Query convention)

Validating M Expressions

Two approaches to validate that an M expression is syntactically correct and produces expected results:

1. Execute via the Power Query API (Recommended)

Test the expression by running it against real data. This validates syntax, data source connectivity, and transformation correctness in one step.

The full workflow, run by the bundled examples/execute_m.py:

1. Create or reuse a runner dataflow in the workspace 2. Bind the data source connection to the runner 3. Wrap the expression in a section document, inline parameters 4. Execute via POST /v1/workspaces/{wsId}/dataflows/{dfId}/executeQuery 5. Parse the Arrow response to verify data

MASHUP='section Section1;
shared SqlEndpoint = "myserver.database.windows.net";
shared Database = "MyDB";
shared Result = let
    Source = Sql.Database(SqlEndpoint, Database),
    Data = Table.FirstN(Source{[Schema="dbo",Item="Orders"]}[Data], 10)
in Data;'

curl -s -o result.bin -X POST ".../executeQuery" \
  -H "Authorization: Bearer ${TOKEN}" -H "Content-Type: application/json" \
  -d "$(jq -n --arg m "$MASHUP" '{queryName:"Result",customMashupDocument:$m}')"

See `references/validation.md` for step-by-step instructions and error handling.

2. Save the Partition via XMLA / TOM

Write the expression back to the model; Analysis Services validates the M syntax on save. This doesn't execute the query but catches structural errors:

• Missing or mismatched let/in
• Undefined step references
• Invalid function calls
• Type mismatches in TransformColumnTypes

# Edit the TMDL partition source directly and deploy via fab import,
# or use the XMLA endpoint with Tabular Editor or SSMS to modify
# the partition expression on the deployed model.

AS returns an error if the expression is malformed. This is faster than a full execute but doesn't catch runtime errors (wrong column names, data source issues).

Choosing a Validation Approach

Previewing Partition Steps

See the data at any point in the transformation chain by truncating the let...in:

-- See raw source data (all columns)
in Data;

-- See after column removal
in #"Removed Columns";

-- See final result
in #"Changed Type";

Add Table.FirstN(stepName, 100) before the in to limit rows for large tables. See `references/validation.md` for the complete procedure.

Common Patterns

Incremental Refresh Partitions

Incremental refresh partitions use RangeStart and RangeEnd parameters:

let
    Source = Sql.Database(#"SqlEndpoint", #"Database"),
    Data = Source{[Schema="dbo", Item="Orders"]}[Data],
    #"Filtered" = Table.SelectRows(Data, each
        [OrderDate] >= #"RangeStart" and [OrderDate] < #"RangeEnd")
in
    #"Filtered"

When testing, inline concrete date values for RangeStart and RangeEnd.

Lakehouse Sources

let
    Source = Lakehouse.Contents(null),
    Data = Source{[Id="lakehouse-guid"]}[Data],
    Table = Data{[Id="table-name", ItemKind="Table"]}[Data]
in
    Table

SQL with Native Query

For complex SQL that can't be expressed in M:

let
    Source = Sql.Database("server", "db"),
    Data = Value.NativeQuery(Source, "SELECT * FROM dbo.MyView WHERE Year = 2024", null, [EnableFolding=true])
in
    Data

Value.NativeQuery with EnableFolding=true allows subsequent M steps to fold on top of the native query.

References

• `references/validation.md` -- Detailed validation workflow with executeQuery API, step preview, error handling
• `references/best-practices.md` -- Query folding guidance, fold-breaker list, anti-patterns, performance tips
• `examples/execute_m.py` -- Python script to execute M expressions via the Fabric API (CLI tool)
• `examples/preview_partition.py` -- Python script to preview partition data at any step (uses fab get + execute_m.py)
• Power Query M Reference
• Query Folding Guidance

Power Query Best Practices for Semantic Models

Practical guidance for writing performant, maintainable M expressions in semantic model partitions.

Safe Pattern for Writing M

When writing or generating M expressions for import partitions, follow this order to maximize query folding:

let
    Source = Sql.Database(SqlEndpoint, Database),
    Data = Source{[Schema="dbo", Item="MyTable"]}[Data],
    -- 1. Filter rows (folds to WHERE)
    Filtered = Table.SelectRows(Data, each [IsActive] = true),
    -- 2. Select columns (folds to SELECT)
    Selected = Table.SelectColumns(Filtered, {"Id", "Date", "Amount"}),
    -- 3. Set types (folds to CAST)
    Typed = Table.TransformColumnTypes(Selected, {{"Amount", Currency.Type}}),
    -- 4. Sort if needed (folds to ORDER BY)
    Sorted = Table.Sort(Typed, {{"Date", Order.Descending}}),
    -- 5. Non-foldable transforms LAST
    Added = Table.AddColumn(Sorted, "Category", each if [Amount] > 1000 then "High" else "Low")
in
    Added

If the transform logic is too complex for M, use Value.NativeQuery to pass native SQL directly:

let
    Source = Sql.Database(SqlEndpoint, Database),
    Data = Value.NativeQuery(Source,
        "SELECT Id, Date, Amount FROM dbo.MyTable WHERE IsActive = 1",
        null, [EnableFolding=true])
in
    Data

EnableFolding=true allows subsequent M steps to fold on top of the native query result.

Query Folding

Query folding translates M steps into native data source queries (SQL, OData, etc.). When folding works, the data source does the heavy lifting. When it breaks, the mashup engine pulls all data into memory and processes it locally.

Why It Matters

• A folded query against a 10M row table sends SELECT TOP 1000 ... WHERE ... to SQL Server; fast
• A non-folded query pulls all 10M rows into the mashup engine, then filters locally; slow and memory-heavy
• For large tables, broken folding often causes refresh timeouts or out-of-memory errors

Steps That Fold (SQL Sources)

Operations That Break Folding

Once folding breaks, all subsequent steps also run locally. This list applies primarily to SQL Server via Sql.Database; other sources may differ.

Table construction / materialization:

• Table.Buffer -- forces full data load to memory
• List.Buffer -- forces full list load to memory
• Table.StopFolding -- explicitly stops folding
• #table constructor, Table.FromList, Table.FromRecords, Table.FromRows, Table.FromValue, Table.FromColumns -- creates table locally

Row position / index operations:

• Table.AddIndexColumn -- no SQL row index equivalent
• Table.LastN / Table.RemoveLastN -- no SQL BOTTOM N
• Table.Range (mid-range), Table.Repeat, Table.AlternateRows -- no SQL equivalent
• Table.InsertRows, Table.RemoveRows (by position) -- positional, not predicate-based
• Table.ReverseRows -- no SQL row-reverse
• Table.FindText -- full-text search not translatable

Text functions (inside `Table.TransformColumns` or `Table.AddColumn`):

• Text.Proper / "Capitalize Each Word"
• Text.Combine (multi-column merge), Text.Insert, Text.Remove, Text.RemoveRange
• Text.Select, Text.Split, Text.SplitAny
• Text.BeforeDelimiter, Text.AfterDelimiter, Text.BetweenDelimiters
• Text.PadStart, Text.PadEnd, Text.Reverse, Text.Format
• Text.ToList, Text.Clean
• Text.From with format/culture arguments

Column splitting / combining:

• Table.SplitColumn, Table.CombineColumns, all Splitter.* functions

Pivot / transpose / structure:

• Table.Transpose, Table.DemoteHeaders, Table.PromoteHeaders

Fill / imputation:

• Table.FillDown, Table.FillUp -- requires stateful row scanning

Error handling:

• Table.RemoveRowsWithErrors, Table.SelectRowsWithErrors
• try...otherwise in row context

Schema / metadata:

• Table.Schema, Table.ColumnNames, Value.Type, Type.Is

Custom functions / iteration:

• User-defined (x) => ... lambdas in row context
• Table.TransformRows -- arbitrary M function per row
• List.Generate, List.Accumulate -- iterative; no SQL equivalent
• List.Transform with complex logic

Record / list / structured columns:

• Table.ExpandListColumn, Table.ExpandRecordColumn (except after same-source NestedJoin)
• Record.* functions, Table.ToRecords, Table.ToRows, Table.ToList, Table.Column

Date/time in row context:

• Date.ToText / DateTime.ToText / Duration.ToText with format strings
• Date.IsInCurrentMonth, Date.IsInCurrentWeek and similar relative date filters
• Date.DayOfWeekName, Date.MonthName -- locale-dependent

Miscellaneous:

• Table.Profile -- statistical summary; local only
• Table.Max, Table.Min (returning row) -- returns record not table
• Table.Contains, Table.ContainsAll, Table.ContainsAny, Table.IsDistinct -- returns boolean

Operations That Sometimes Fold

These fold under certain conditions:

• Table.AddColumn -- folds if expression uses only SQL-translatable functions (arithmetic, Text.Upper); breaks with complex M logic
• Table.TransformColumns -- folds for Text.Upper, Text.Lower, Text.Trim, Number.Round; breaks for Text.Proper, complex lambdas
• Table.TransformColumnTypes -- folds for compatible casts (int to decimal); breaks for locale-specific or M-only types
• Table.ReplaceValue -- folds with simple literal replacement; breaks with patterns
• Table.Pivot / Table.Unpivot -- folds on SQL Server (PIVOT/UNPIVOT support); breaks on other sources
• Table.NestedJoin -- folds when both sources are the same SQL connection; breaks across different sources
• Table.Combine / append -- folds as UNION ALL when all inputs are same SQL source
• Table.SelectRows with Text.Contains -- folds as LIKE '%value%' on SQL Server
• Table.Group -- folds with standard aggregations (List.Sum, List.Count, List.Average); breaks with custom functions
• Value.NativeQuery -- subsequent steps fold only if EnableFolding=true is set
• Text.Start / Text.End -- often fold as LEFT() / RIGHT(); Text.Middle often does not
• Date.Year, Date.Month, Date.Day -- fold as YEAR(), MONTH(), DAY()
• Date.AddDays / Date.AddMonths -- fold as DATEADD()

Environmental Fold-Breakers

Not functions, but conditions that prevent folding:

• Merging/appending queries from different data sources
• Incompatible data privacy levels between sources (Data Privacy Firewall intervenes)
• Source is a flat file (CSV, Excel, JSON, XML) -- no query engine
• Source is Web.Contents / API -- no SQL engine
• Custom SQL without EnableFolding=true
• Any step after a fold-breaking step (chain is broken; cannot re-fold)

Folding Strategy

Rule: Do all foldable work first, then do non-foldable work.

let
    Source = Sql.Database(SqlEndpoint, Database),
    Data = Source{[Schema="dbo", Item="Orders"]}[Data],

    -- FOLDABLE: These translate to SQL
    #"Filtered Rows" = Table.SelectRows(Data, each [Year] >= 2023),
    #"Selected Columns" = Table.SelectColumns(#"Filtered Rows",
        {"OrderId", "Date", "Amount", "CustomerId", "Status"}),
    #"Set Types" = Table.TransformColumnTypes(#"Selected Columns", {
        {"Amount", Currency.Type}, {"Date", type date}}),

    -- NON-FOLDABLE: These run in the mashup engine
    #"Added Category" = Table.AddColumn(#"Set Types", "AmountBucket",
        each if [Amount] > 10000 then "Large" else "Small", type text)
in
    #"Added Category"

Verifying Folding

In Power Query Online or Desktop, right-click a step and check "View Native Query". If greyed out, the step doesn't fold.

Programmatically: execute the expression via the executeQuery API. If a query on a large table completes well within the timeout, folding is likely working. If it times out or is slow, folding may be broken.

Column Pruning

Remove columns as early as possible. Every column not removed travels through every subsequent step.

-- Good: remove columns immediately after navigation
Data = Source{[Schema="dbo", Item="Orders"]}[Data],
#"Selected" = Table.SelectColumns(Data, {"OrderId", "Date", "Amount"}),
...

-- Bad: remove columns at the end after all transforms
...
#"Final" = Table.RemoveColumns(#"Transformed", {"Col1", "Col2", "Col3", ...})

Early column pruning folds to SQL SELECT, reducing data transfer from the source.

Row Filtering

Filter rows early for the same reason. A Table.SelectRows immediately after navigation folds to WHERE:

Data = Source{[Schema="dbo", Item="Orders"]}[Data],
#"Filtered" = Table.SelectRows(Data, each [IsActive] = true and [Year] >= 2023),

This is especially important for incremental refresh, where RangeStart/RangeEnd filters must fold to be effective.

Type Handling

Apply Types Early

Table.TransformColumnTypes folds to CAST in SQL. Apply it right after column selection:

#"Selected" = Table.SelectColumns(Data, {"OrderId", "Date", "Amount"}),
#"Typed" = Table.TransformColumnTypes(#"Selected", {
    {"OrderId", Int64.Type},
    {"Date", type date},
    {"Amount", Currency.Type}
}),

Avoid Implicit Type Detection

Never use Table.TransformColumnTypes with Replacer.ReplaceValue or locale-dependent conversions on large datasets. These don't fold and can introduce unexpected nulls.

Common Type Mappings

Anti-Patterns

Pulling Entire Tables Then Filtering

-- Anti-pattern: filter after all transforms
Data = Source{[Schema="dbo", Item="BigTable"]}[Data],
#"Added Column" = Table.AddColumn(Data, ...),  -- breaks folding
#"Filtered" = Table.SelectRows(#"Added Column", each [Year] >= 2023)
-- Filter runs locally on ALL rows

Using Table.Buffer Unnecessarily

Table.Buffer forces the entire table into memory. Only use when the same table is referenced multiple times and re-evaluation would be expensive.

Referencing Other Queries

Cross-query references (accessing a column from a different query) break folding and can cause cascading performance issues.

Excessive Step Count

Each step adds overhead. Combine related operations where natural; don't create a separate step for each individual column rename when Table.RenameColumns handles multiples:

-- Good: one step for all renames
#"Renamed" = Table.RenameColumns(Data, {
    {"OldName1", "NewName1"},
    {"OldName2", "NewName2"}
})

-- Bad: separate step per rename
#"Renamed1" = Table.RenameColumns(Data, {{"OldName1", "NewName1"}}),
#"Renamed2" = Table.RenameColumns(#"Renamed1", {{"OldName2", "NewName2"}})

Naming Conventions

• Use descriptive step names: #"Filtered Active Orders" not #"Custom1"
• Use #"Quoted Identifiers" for steps with spaces (standard Power Query convention)
• Parameters: PascalCase without spaces (SqlEndpoint, DatabaseName)
• Keep step names consistent with what the Power Query UI would generate

Error Handling

For production partitions, avoid try...otherwise patterns that silently swallow errors. A failed refresh is better than silently loading wrong data.

If error handling is necessary (e.g., optional columns), make it explicit and narrow:

#"Safe Amount" = Table.TransformColumns(Data, {
    {"Amount", each try Number.FromText(_) otherwise null, type number}
})

Validating Power Query Expressions

Two complementary approaches: execute against real data (comprehensive validation) or save to the model (quick syntax check).

Approach 1: Execute via Power Query API

Full validation that runs the expression and returns actual data. Catches syntax errors, missing columns, data source issues, and type problems.

Prerequisites

• A runner dataflow in the workspace with the data source connection bound
• See the Power Query API section in SKILL.md for creating the runner dataflow and executing expressions

Step 1: Extract the Expression and Parameters

# Get partition expression from TMDL
fab get "<Workspace>.Workspace/<Model>.SemanticModel" -f \
  -q "definition.parts[?path=='definition/tables/<Table>.tmdl'].payload"

# Get shared M parameters
fab get "<Workspace>.Workspace/<Model>.SemanticModel" -f \
  -q "definition.parts[?path=='definition/expressions.tmdl'].payload"

The partition expression is in the partition block of the TMDL. Shared parameters are expression declarations in expressions.tmdl.

Step 2: Build the Mashup Document

Wrap the expression in a section document, inlining parameter values as shared declarations:

section Section1;
shared SqlEndpoint = "myserver.database.windows.net";
shared Database = "MyDatabase";
shared Result = let
    Source = Sql.Database(SqlEndpoint, Database),
    Data = Source{[Schema="dbo",Item="Orders"]}[Data],
    #"Select Columns" = Table.SelectColumns(Data, {"OrderId", "Amount"}),
    Limited = Table.FirstN(#"Select Columns", 100)
in Limited;

Key points:

• Replace #"SqlEndpoint" references with SqlEndpoint (the shared declaration)
• The shared Result = ... name must match the queryName in the API call
• Add Table.FirstN to limit rows for large tables
• For incremental refresh, inline RangeStart and RangeEnd with concrete date values

Step 3: Execute

TOKEN=$(az account get-access-token \
  --resource https://api.fabric.microsoft.com --query accessToken -o tsv)

curl -s -o /tmp/pq_result.bin -X POST \
  "https://api.fabric.microsoft.com/v1/workspaces/${WS_ID}/dataflows/${DF_ID}/executeQuery" \
  -H "Authorization: Bearer ${TOKEN}" -H "Content-Type: application/json" \
  -d "$(jq -n --arg m "$MASHUP" '{queryName:"Result",customMashupDocument:$m}')"

Step 4: Read and Validate Results

uv run --with pyarrow python3 -c "
import pyarrow.ipc as ipc, io, json

with open('/tmp/pq_result.bin', 'rb') as f:
    table = ipc.open_stream(io.BytesIO(f.read())).read_all()
    df = table.to_pandas()

if 'PQ Arrow Metadata' in df.columns:
    meta = df['PQ Arrow Metadata'].dropna()
    if len(meta) > 0 and len(df.columns) == 1:
        error = json.loads(meta.iloc[0])
        print('ERROR:', error.get('Error', error))
    else:
        cols = [c for c in df.columns if c != 'PQ Arrow Metadata']
        print(f'Columns: {cols}')
        print(df[cols].head(10).to_string(index=False))
        print(f'({len(df)} rows, {len(cols)} columns)')
        print(f'Types: {dict(df[cols].dtypes)}')
else:
    print(f'Columns: {list(df.columns)}')
    print(df.head(10).to_string(index=False))
    print(f'({len(df)} rows)')
"

Common Errors

Approach 2: Save to Model via XMLA/TOM

Analysis Services validates M syntax when a partition expression is saved. This is faster than executing but only catches structural errors; it won't detect wrong column names or data source issues.

Using TMDL editing or Tabular Editor

Edit the partition expression in the TMDL file directly, or use Tabular Editor to modify and deploy:

# Edit the partition expression in the TMDL file
# Open: <Model>.SemanticModel/definition/tables/Orders.tmdl
# Modify the partition expression under the "partition" block, then deploy:
fab import "<Workspace>.Workspace/<Model>.SemanticModel" -i ./<Model>.SemanticModel -f

If the expression has syntax errors, AS returns an error like:

Token Eof expected.
Expression.SyntaxError: Token Literal expected.

Using TMDL Files

Edit the partition source = block in the .tmdl file and deploy. The deployment process validates the expression.

What XMLA Validation Catches

• Missing or mismatched let/in blocks
• Undefined step references (e.g., referencing #"Step3" that doesn't exist)
• Invalid M function names
• Syntax errors (missing commas, unbalanced brackets)
• Invalid type names in TransformColumnTypes

What XMLA Validation Misses

• Wrong column names (the expression is syntactically valid but the column doesn't exist at the source)
• Data source connectivity issues
• Runtime errors (division by zero, type conversion failures on actual data)
• Performance issues (broken query folding)

Step-by-Step Debugging

When an expression fails or produces unexpected results, preview intermediate steps by changing the in clause:

section Section1;
shared SqlEndpoint = "myserver.database.windows.net";
shared Database = "MyDB";
shared Result = let
    Source = Sql.Database(SqlEndpoint, Database),
    Data = Source{[Schema="dbo",Item="Orders"]}[Data],
    #"Filtered" = Table.SelectRows(Data, each [Status] <> "Cancelled"),
    #"Selected" = Table.SelectColumns(#"Filtered", {"OrderId", "Amount"})
in Data;  -- Change this to inspect different steps

For each step, check:

• Column names and count (did a rename/select work?)
• Row count (did a filter apply correctly?)
• Data types (df.dtypes in Python)
• Null counts (df.isnull().sum())
• Sample values (do they look right?)

Validation Checklist

Before deploying a new or modified partition expression:

1. Syntax: Save to model (XMLA) to catch structural errors 2. Data: Execute via API with Table.FirstN(_, 100) to verify correct columns and values 3. Types: Check df.dtypes matches expected semantic model column types 4. Nulls: Check df.isnull().sum() for unexpected nulls from type casting 5. Row count: Execute without Table.FirstN (or with a large limit) to verify filter logic 6. Folding: For large tables, verify the query completes within 90 seconds (indicates folding is working)