Power Query field guide

Troubleshooting matrix

Start here when something is wrong; the sections below explain each fix.

The shape of a query

Every query is a let … in expression. Each step is a name bound to a value; the result is whatever follows in.

let
    Source   = Csv.Document(File.Contents(DataFolder & "FactSales.csv"), [Delimiter = ",", Encoding = 65001]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Typed    = Table.TransformColumnTypes(Promoted, {{"OrderDate", type date}, {"Qty", Int64.Type}, {"UnitPrice", type number}}),
    Filtered = Table.SelectRows(Typed, each [Qty] <> 0)
in
    Filtered

The values you meet most:

Table operations

Always give Table.AddColumn a type as its fourth argument. Untyped columns load as text or "any" and quietly break sorting, relationships and DAX.

List operations

List.Sum({1, 2, 3})                                  // 6
List.Transform({1, 2, 3}, each _ * 10)               // {10, 20, 30}
List.Select({1, 2, 3, 4}, each Number.IsEven(_))     // {2, 4}
List.Distinct(t[Region])                             // unique values of a column
List.Dates(#date(2026, 1, 1), 365, #duration(1, 0, 0, 0))
List.Generate(() => 1, each _ <= 5, each _ + 1)      // {1, 2, 3, 4, 5}

List.Generate is the loop of M: an initial state, a condition to continue, and a step. It is how you page through APIs (see Pagination).

Joins

Merge = join. Pick the join kind for the question, not by habit:

Merged   = Table.NestedJoin(Sales, {"ProductKey"}, Products, {"ProductKey"}, "P", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "P", {"Category"}, {"Category"})

Anti joins are the cheapest data-quality test you have: one merge tells you how many facts have no dimension row. The testing track's data quality tests are built on this idea.

Append

Table.Combine({Jan, Feb, Mar}) stacks tables. Columns are matched by name, not position; a column missing from one table becomes null for its rows. Rename to a common schema before appending, and add a SourceFile or Period column so you can trace rows back.

Errors

Errors in Power Query are per cell, so one bad value doesn't stop the column loading but does leave an error in that cell, and refresh can fail on them.

SafeQty = Table.AddColumn(t, "QtyClean", each try Number.From([Qty]) otherwise null, type nullable number),

// keep the reason, not just a null
Checked = Table.AddColumn(t, "QtyCheck", each
    let r = try Number.From([Qty])
    in if r[HasError] then "Bad: " & r[Error][Message] else "OK", type text)

• try x otherwise y returns y when x errors.
• try x alone returns a record with HasError, Value and Error (with Reason, Message, Detail).
• Find error rows before loading: View → Column quality, or Table.SelectRowsWithErrors(t, {"Qty"}).
• Remove them on purpose with Table.RemoveRowsWithErrors, never by accident.

Functions and parameters

A custom function is a query whose value is a function. Write the logic once, call it per row or per file.

// fnCleanPrice: "$1,299.00 USD" → 1299
(raw as nullable text) as nullable number =>
let
    stripped = if raw = null then null else Text.Remove(raw, {"$", ",", " "}),
    noCode   = if stripped = null then null else Text.Replace(stripped, "USD", ""),
    value    = try Number.From(noCode, "en-US") otherwise null
in
    value

Parameters (Manage parameters) hold values that differ by environment, such as a server name or folder path. Deployment pipelines and fabric-cicd can swap parameter values between Dev, Test and Prod; hard-coded paths can't be swapped.

Dates

// a calendar from the data's first to last date
let
    Start = List.Min(Sales[OrderDate]),
    End   = List.Max(Sales[OrderDate]),
    Days  = List.Dates(Start, Duration.Days(End - Start) + 1, #duration(1, 0, 0, 0)),
    T     = Table.FromList(Days, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    Typed = Table.TransformColumnTypes(T, {{"Date", type date}}),
    Year  = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type),
    // fiscal year starting 1 July
    FY    = Table.AddColumn(Year, "FiscalYear", each Date.Year(Date.AddMonths([Date], 6)), Int64.Type)
in
    FY

Locale matters when converting text to dates: Date.FromText("03/04/2026", [Culture = "en-GB"]) is 3 April; with "en-US" it is 4 March. Set the culture explicitly; never rely on the machine that happens to refresh.

Folder of files

The Folder connector plus Combine files creates a sample query, a function and a combined query. It works when every file has the same columns. When they don't:

1. Normalise inside the function: promote headers, rename to a standard schema, select only the standard columns with MissingField.UseNull.
2. Add the file name as a column (it is already in the folder listing as Name) so you can trace a bad row to its file.
3. Filter the folder listing on Extension and on a naming pattern, so a stray ~$draft.xlsx doesn't break refresh.

APIs and Web.Contents

let
    Base   = "https://api.example.com",
    Result = Json.Document(Web.Contents(Base, [
        RelativePath = "v1/orders",
        Query        = [from = "2026-01-01", page = "1"],
        Headers      = [Accept = "application/json"]
    ]))
in
    Result

Keep the base URL a constant and put everything that changes in RelativePath and Query. The Power BI Service validates data sources before refresh, and a URL concatenated from data ("https://api.example.com/orders?page=" & Text.From(n)) becomes a dynamic data source that the Service won't schedule. Never put a key or token in the query text: use the connector's credential dialog, or a gateway or Key Vault-backed connection.

Pagination

Most APIs return a page at a time with a "next" link or a page number. Loop with List.Generate until the API says stop:

let
    GetPage = (page as number) =>
        Json.Document(Web.Contents("https://api.example.com", [RelativePath = "v1/orders", Query = [page = Text.From(page), pageSize = "500"]])),
    Pages = List.Generate(
        () => [p = 1, r = GetPage(1)],
        each List.Count([r][data]) > 0,
        each [p = [p] + 1, r = GetPage([p] + 1)],
        each [r][data]),
    Rows  = List.Combine(Pages),
    T     = Table.FromRecords(Rows)
in
    T

If the API returns a nextLink, loop on that instead (each [r][nextLink] <> null), passing only the changing part through RelativePath or Query.

Query folding

Folding means Power Query translates your steps into the source's language (usually SQL) so the source does the work. A refresh that folds reads only the rows and columns it needs; one that doesn't drags everything across the network first.

Check it: right-click a step → View Native Query (enabled means that step folds), or look at the folding indicators in the Applied Steps pane.

Combining sources safely

Privacy levels (Private, Organizational, Public) stop data from one source being sent to another. When a query mixes sources, Power Query may refuse with Formula.Firewall. Fix it by design, not by switching privacy off:

1. Set the right privacy level on each source (File → Options → Data source settings).
2. Separate "staging" queries that read one source each from the query that combines them.
3. Only use "Ignore privacy levels" for sources you'd happily send to each other anyway, and know that the Service enforces its own settings.

Schema drift

Sources change: a column is renamed, a new one appears, a type changes. Defend at the edges:

• Select columns by name early with MissingField.UseNull, so a dropped column becomes null rather than a refresh failure.
• Rename with MissingField.Ignore.
• Type explicitly once, after selection.
• Add a test query that compares Table.ColumnNames(Source) with the list you expect and fails loudly with error "Schema changed: …" when they differ. A failed refresh with a clear message beats a successful one with wrong numbers.
• Agree the schema with the source owner in a data contract.

Performance

• Reference, don't duplicate, but know that each referencing query re-evaluates its source. A staging query referenced by five queries can be read five times.
• Table.Buffer holds a table in memory so it's read once. Use it on small lookup tables used repeatedly, never on a big fact table, and never before steps you want to fold.
• Disable load on staging queries that only feed others.
• Remove columns early. Every column you load costs memory in the model too.
• Push heavy work upstream. Aggregating 300 million rows belongs in SQL or a warehouse, not in the mashup engine (see SQL for BI developers).

Dataflows: when the work belongs upstream

Use a Dataflow Gen2 (or a pipeline) instead of a query inside one semantic model when the same cleaned table feeds several models, when the transformation is expensive and should run once, or when another team owns the source logic. Keep presentation-only shaping in the model. The Fabric architecture guide covers where each kind of work lives.