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:

```m
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

```m
// 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

```m
// 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

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

### SQL Server with Query

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

### Excel File

```m
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

```m
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

```m
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)

```m
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

```m
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

```m
// 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

```m
// 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

```m
// 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

```m
// 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

```m
// 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

```m
// 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

```m
// 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

```m
// 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)

```m
// 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)

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

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

### Pivot/Unpivot

```m
// 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

```m
// 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

```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

```m
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

```m
// 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

```tmdl
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

```m
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

```m
// 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

```m
// 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

```m
// 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

```m
// 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

```m
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

```m
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

```m
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

More agent context in kpbray/power-bi-agent-skills

9 other files this repository gives its agents.