power-query

name	power-query
description	Writes Power Query (M language) for data transformation, connections, and ETL. Use for data sources, transformations, parameters, and query optimization.

Power Query Skill

This skill helps write Power Query (M language) for data transformation in Power BI semantic models.

When to Use This Skill

• Connecting to data sources (SQL, Excel, APIs, etc.)
• Transforming data (filtering, merging, pivoting)
• Creating parameters for dynamic queries
• Optimizing query performance (query folding)
• Setting up incremental refresh
• Error handling in data loads

M Language Fundamentals

Basic Structure

Every M query follows this pattern:

let
    // Step 1: Connect to source
    Source = ...,

    // Step 2: Transform
    Transformed = ...,

    // Step 3: More transformations
    Final = ...
in
    Final

Key Concepts

Concept	Description
let...in	Defines a query with steps
Step names	Descriptive, PascalCase
#"Name"	Names with spaces/special chars
each	Shorthand for (_) => _
_	Current row/value in each

Data Types

// Primitive types
type text
type number
type date
type datetime
type datetimezone
type time
type duration
type logical
type null
type binary

// Complex types
type list
type record
type table
type function

// Nullable types
type nullable text

Type Conversion

// Convert to types
Text.From(123)                    // "123"
Number.From("123")                // 123
Date.From("2024-01-15")           // #date(2024, 1, 15)
DateTime.From("2024-01-15 10:30") // #datetime(2024, 1, 15, 10, 30, 0)
Logical.From(1)                   // true

// Parse with format
Date.FromText("15/01/2024", [Format="dd/MM/yyyy"])

Data Source Connections

SQL Server

let
    Source = Sql.Database("server.database.windows.net", "DatabaseName"),
    Schema = Source{[Schema="dbo", Item="TableName"]}[Data]
in
    Schema

SQL Server with Query

let
    Source = Sql.Database("server.database.windows.net", "DatabaseName", [
        Query = "SELECT * FROM dbo.Sales WHERE Year >= 2023"
    ])
in
    Source

Excel File

let
    Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), null, true),
    Sheet = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true])
in
    PromotedHeaders

CSV File

let
    Source = Csv.Document(
        File.Contents("C:\Data\Sales.csv"),
        [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    PromotedHeaders

SharePoint

let
    Source = SharePoint.Files("https://company.sharepoint.com/sites/data", [ApiVersion=15]),
    File = Source{[Name="data.xlsx"]}[Content],
    Workbook = Excel.Workbook(File, null, true),
    Sheet = Workbook{[Item="Data", Kind="Sheet"]}[Data]
in
    Sheet

Web API (REST)

let
    Source = Json.Document(
        Web.Contents("https://api.example.com/data", [
            Headers = [
                #"Authorization" = "Bearer " & Token,
                #"Content-Type" = "application/json"
            ]
        ])
    ),
    Data = Source[data]
in
    Data

OData

let
    Source = OData.Feed("https://services.odata.org/V4/Northwind/Northwind.svc/"),
    Products = Source{[Name="Products", Signature="table"]}[Data]
in
    Products

Common Transformations

Filter Rows

// Filter by condition
FilteredRows = Table.SelectRows(Source, each [Amount] > 100)

// Filter by multiple conditions
FilteredRows = Table.SelectRows(Source, each [Amount] > 100 and [Status] = "Active")

// Filter by list
FilteredRows = Table.SelectRows(Source, each List.Contains({"A", "B", "C"}, [Category]))

// Filter nulls
FilteredRows = Table.SelectRows(Source, each [Column] <> null)

// Filter by date range
FilteredRows = Table.SelectRows(Source, each [Date] >= #date(2024, 1, 1))

Select/Remove Columns

// Select specific columns
SelectedColumns = Table.SelectColumns(Source, {"Column1", "Column2", "Column3"})

// Remove columns
RemovedColumns = Table.RemoveColumns(Source, {"UnwantedColumn"})

// Reorder columns
ReorderedColumns = Table.ReorderColumns(Source, {"First", "Second", "Third"})

Rename Columns

// Rename single column
Renamed = Table.RenameColumns(Source, {{"OldName", "NewName"}})

// Rename multiple columns
Renamed = Table.RenameColumns(Source, {
    {"old_name_1", "New Name 1"},
    {"old_name_2", "New Name 2"}
})

Add Columns

// Add calculated column
AddedColumn = Table.AddColumn(Source, "Profit", each [Revenue] - [Cost], type number)

// Add conditional column
AddedColumn = Table.AddColumn(Source, "Category", each
    if [Amount] >= 1000 then "High"
    else if [Amount] >= 100 then "Medium"
    else "Low", type text)

// Add index column
AddedIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type)

// Add column from date
AddedYear = Table.AddColumn(Source, "Year", each Date.Year([Date]), Int64.Type)

Change Types

// Change single column type
ChangedType = Table.TransformColumnTypes(Source, {{"Amount", type number}})

// Change multiple column types
ChangedType = Table.TransformColumnTypes(Source, {
    {"Date", type date},
    {"Amount", type number},
    {"Name", type text},
    {"IsActive", type logical}
})

Replace Values

// Replace in column
Replaced = Table.ReplaceValue(Source, "old", "new", Replacer.ReplaceText, {"Column"})

// Replace null with value
Replaced = Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {"Amount"})

// Replace errors
Replaced = Table.ReplaceErrorValues(Source, {{"Column", null}})

Sort Rows

// Sort ascending
Sorted = Table.Sort(Source, {{"Date", Order.Ascending}})

// Sort descending
Sorted = Table.Sort(Source, {{"Amount", Order.Descending}})

// Sort by multiple columns
Sorted = Table.Sort(Source, {
    {"Category", Order.Ascending},
    {"Amount", Order.Descending}
})

Group By

// Group with aggregation
Grouped = Table.Group(Source, {"Category"}, {
    {"Total", each List.Sum([Amount]), type number},
    {"Count", each Table.RowCount(_), Int64.Type},
    {"Average", each List.Average([Amount]), type number}
})

// Group keeping all rows
Grouped = Table.Group(Source, {"Category"}, {
    {"AllRows", each _, type table}
})

Merge (Join)

// Left join
Merged = Table.NestedJoin(
    Source1, {"Key"},
    Source2, {"Key"},
    "Joined",
    JoinKind.LeftOuter
)

// Expand merged columns
Expanded = Table.ExpandTableColumn(
    Merged,
    "Joined",
    {"Column1", "Column2"},
    {"Joined.Column1", "Joined.Column2"}
)

Join Types:

JoinKind	Description
JoinKind.Inner	Only matching rows
JoinKind.LeftOuter	All left + matching right
JoinKind.RightOuter	All right + matching left
JoinKind.FullOuter	All rows from both
JoinKind.LeftAnti	Left rows without match
JoinKind.RightAnti	Right rows without match

Append (Union)

// Append two tables
Appended = Table.Combine({Table1, Table2})

// Append multiple tables
Appended = Table.Combine({Table1, Table2, Table3})

Pivot/Unpivot

// Pivot column
Pivoted = Table.Pivot(
    Source,
    List.Distinct(Source[Category]),
    "Category",
    "Amount",
    List.Sum
)

// Unpivot columns
Unpivoted = Table.UnpivotOtherColumns(
    Source,
    {"ID", "Name"},
    "Attribute",
    "Value"
)

// Unpivot specific columns
Unpivoted = Table.Unpivot(
    Source,
    {"Jan", "Feb", "Mar"},
    "Month",
    "Amount"
)

Split Column

// Split by delimiter
Split = Table.SplitColumn(
    Source,
    "FullName",
    Splitter.SplitTextByDelimiter(" ", QuoteStyle.None),
    {"FirstName", "LastName"}
)

// Split by position
Split = Table.SplitColumn(
    Source,
    "Code",
    Splitter.SplitTextByPositions({0, 3}),
    {"Prefix", "Number"}
)

Parameters

Create Parameter in TMDL

expression ServerName = "server.database.windows.net" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

expression DatabaseName = "ProductionDB" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

expression StartDate = #date(2023, 1, 1) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]

Use Parameters in Query

let
    Source = Sql.Database(ServerName, DatabaseName),
    Filtered = Table.SelectRows(Source, each [Date] >= StartDate)
in
    Filtered

Query Folding

Query folding pushes transformations to the data source for better performance.

Foldable Operations

Operation	Foldable
Table.SelectRows (simple filters)	✓
Table.SelectColumns	✓
Table.Sort	✓
Table.Group	✓
Table.Join (same source)	✓
Table.TransformColumnTypes	✓

Non-Foldable Operations

Operation	Foldable
Table.AddColumn (custom)	✗
Table.Buffer	✗
Custom functions	✗
Cross-source joins	✗

Check Query Folding

Right-click a step in Power Query Editor and look for "View Native Query" - if available, the query folds.

Optimize for Folding

// GOOD: Filter early (folds)
let
    Source = Sql.Database(Server, Database),
    Filtered = Table.SelectRows(Source, each [Year] = 2024),
    Selected = Table.SelectColumns(Filtered, {"ID", "Name", "Amount"})
in
    Selected

// BAD: Custom column before filter (breaks folding)
let
    Source = Sql.Database(Server, Database),
    Added = Table.AddColumn(Source, "Custom", each [A] & [B]),
    Filtered = Table.SelectRows(Added, each [Year] = 2024)
in
    Filtered

Incremental Refresh

Required Parameters

expression RangeStart = #datetime(2020, 1, 1, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true]

expression RangeEnd = #datetime(2025, 12, 31, 23, 59, 59) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true]

Query Pattern

let
    Source = Sql.Database(Server, Database),
    Sales = Source{[Schema="dbo", Item="Sales"]}[Data],
    // Filter using RangeStart and RangeEnd (REQUIRED for incremental refresh)
    Filtered = Table.SelectRows(Sales, each
        [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
    )
in
    Filtered

Error Handling

Try...Otherwise

// Handle errors in expression
Result = try Number.From([Value]) otherwise 0

// Handle errors with details
Result = try Number.From([Value])
    otherwise error Error.Record("Conversion Error", "Could not convert value")

Error Records

// Check for error
if try [Expression] is error then "Error" else "OK"

// Get error details
ErrorInfo = try [Expression]
if ErrorInfo[HasError] then ErrorInfo[Error][Message] else ErrorInfo[Value]

Replace Errors in Column

// Replace errors with null
Cleaned = Table.ReplaceErrorValues(Source, {{"Column", null}})

// Replace errors with specific value
Cleaned = Table.ReplaceErrorValues(Source, {{"Amount", 0}})

Custom Functions

Simple Function

// Define function
CalculateMargin = (Revenue as number, Cost as number) as number =>
    (Revenue - Cost) / Revenue

// Use function
AddedColumn = Table.AddColumn(Source, "Margin", each CalculateMargin([Revenue], [Cost]))

Function with Optional Parameter

FormatDate = (InputDate as date, optional Format as text) as text =>
    let
        FormatToUse = if Format = null then "yyyy-MM-dd" else Format
    in
        Date.ToText(InputDate, FormatToUse)

Table-Valued Function

GetSalesForYear = (Year as number) as table =>
    let
        Source = Sql.Database(Server, Database),
        Sales = Source{[Schema="dbo", Item="Sales"]}[Data],
        Filtered = Table.SelectRows(Sales, each Date.Year([Date]) = Year)
    in
        Filtered

Date Table Generation

let
    StartDate = #date(2020, 1, 1),
    EndDate = #date(2030, 12, 31),

    // Generate date list
    NumberOfDays = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),

    // Convert to table
    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    ChangedType = Table.TransformColumnTypes(TableFromList, {{"Date", type date}}),

    // Add date columns
    AddDateKey = Table.AddColumn(ChangedType, "Date Key", each Number.From(Date.ToText([Date], "yyyyMMdd")), Int64.Type),
    AddYear = Table.AddColumn(AddDateKey, "Year", each Date.Year([Date]), Int64.Type),
    AddQuarter = Table.AddColumn(AddYear, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
    AddMonth = Table.AddColumn(AddQuarter, "Month Number", each Date.Month([Date]), Int64.Type),
    AddMonthName = Table.AddColumn(AddMonth, "Month", each Date.MonthName([Date]), type text)
in
    AddMonthName

Boundaries and Constraints

DO

• Use descriptive step names
• Filter early for query folding
• Use parameters for connections
• Handle errors explicitly
• Test query folding with "View Native Query"
• Use Table.Buffer sparingly (only when needed)

DO NOT

• Never hardcode credentials in queries
• Avoid complex transformations before filters
• Don't use each when simple column reference works
• Avoid excessive nested let expressions
• Never use Table.Buffer on large tables without reason

Workflow Integration

After creating queries:

1. Add to model - Use the semantic-model skill for TMDL partitions
2. Create measures - Use the dax skill for calculations
3. Validate - Use the best-practices skill to check quality