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: