Power Query

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

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:

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

Non-Foldable Operations

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