Power Query (M) Formatter

For Excel and Power BI users facing a wall of #"Changed Type" steps in the Advanced Editor, or a hand-edited query that no longer parses. Microsoft's M formatter runs inside this page, so connection strings, server names and file paths stay with you.

Input

Settings

History

Load from URL

Microsoft's formatter, in the browser

The engine is @microsoft/powerquery-formatter, the formatter Microsoft publishes alongside its Power Query language services for Visual Studio Code and the Power Query SDK. It parses M with Microsoft’s own parser, so it understands the complete language: let ... in expressions, records ([Name = "Sales"]), lists ({1, 2, 3}), each and _, custom functions with typed parameters, try ... otherwise, #date and #duration literals, quoted identifiers like #"Changed Type" and meta metadata.

What changes in code the Applied Steps pane has generated:

  • every step of a let expression starts on its own line, indented under let
  • in and the returned step are placed on separate lines
  • long function calls are broken so each argument, record field or list item is readable
  • nested records such as the options of Csv.Document or Web.Contents expand when they do not fit

Step names, string contents and the order of steps are never changed, so a formatted query keeps its Applied Steps list and refreshes the same way.

Indentation, width and shortcuts

There are no M-specific options. Indentation starts at 4 spaces, the toolbar can change it, and the line width (80 by default) tells the formatter when to break a call or a record.

Press Ctrl/Cmd+Enter to format, then Ctrl/Cmd+Shift+C to copy the result back into the Advanced Editor. Ctrl/Cmd+K opens the command palette if you also need the DAX formatter for the model side. Minify is not available for M.

Reading the errors

The parser is strict, which is useful: an error here is usually one Power Query would report too, with a clearer position. Messages name what was expected and what was found. A right parenthesis expected where the keyword in was found means a call was not closed before the end of the let block.

Two mistakes account for most failures. The first is a comma after the last step, just before in. The second is a missing comma between two steps after a hand edit, which the Advanced Editor reports as Expression.SyntaxError: Token Comma expected. Here it reads Expected to find either a comma <','> or keyword <'in'>., pointing at the step after the gap. Remember that M is case-sensitive, so table.selectrows parses but fails at evaluation.

Folder paths and URLs inside strings are left exactly as written, including backslashes. Comments are kept, but they can affect layout: a trailing // note moves to its own line below the step, and a comment above let or between two steps on one line can leave that block unbroken. Put comments on their own line above the step for the cleanest result. If you are importing a CSV, the CSV formatter helps check the source file’s delimiter and quoting first.

Patterns worth keeping readable

Three things are much easier to review once a query is laid out.

Web.Contents with RelativePath and Query. Build the URL through these options with a static base address, as in the paged example below. A URL concatenated from values at run time is a dynamic data source, which in most cases cannot be refreshed in the Power BI service.

Which steps fold. Formatting has no effect on query folding, because only whitespace changes. A tidy step list does make it easier to find the first step that stops folding, such as a custom column, and to move filters above it.

Queries that combine sources. A step that references another query and also calls a data source is the shape behind most Formula.Firewall errors, and laying out the steps shows where the two meet.

Examples

Applied Steps from a CSV import

The code the Query Editor generates: quoted step names are kept, the CSV options expand field by field, and the column type list takes one pair per line.

Input
let Source = Csv.Document(File.Contents("C:\Finance\Exports\invoices_2026.csv"),[Delimiter=",", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.Csv]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"InvoiceID", type text}, {"InvoiceDate", type date}, {"Amount", type number}, {"Region", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Amount] > 0 and [Region] <> null), #"Added Quarter" = Table.AddColumn(#"Filtered Rows", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([InvoiceDate])), type text) in #"Added Quarter"
Output
let
    Source = Csv.Document(
        File.Contents("C:\Finance\Exports\invoices_2026.csv"),
        [
            Delimiter = ",",
            Columns = 6,
            Encoding = 65001,
            QuoteStyle = QuoteStyle.Csv
        ]
    ),
    #"Promoted Headers" = Table.PromoteHeaders(
        Source, [PromoteAllScalars = true]
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Promoted Headers",
        {
            {"InvoiceID", type text},
            {"InvoiceDate", type date},
            {"Amount", type number},
            {"Region", type text}
        }
    ),
    #"Filtered Rows" = Table.SelectRows(
        #"Changed Type", each [Amount] > 0 and [Region] <> null
    ),
    #"Added Quarter" = Table.AddColumn(
        #"Filtered Rows",
        "Quarter",
        each "Q" & Text.From(Date.QuarterOfYear([InvoiceDate])),
        type text
    )
in
    #"Added Quarter"
Open this example in the tool

Paged REST API with List.Generate

The four List.Generate arguments (start, condition, next, selector) each take a line, and the Web.Contents options sit in one block where the static base URL is easy to confirm.

Input
let BaseUrl="https://api.example.com",GetPage=(page as number) as record=>Json.Document(Web.Contents(BaseUrl,[RelativePath="v2/orders",Query=[page=Text.From(page),per_page="100"],Headers=[Accept="application/json"]])),Pages=List.Generate(()=>[Page=1,Result=GetPage(1)],each List.Count([Result][data])>0,each [Page=[Page]+1,Result=GetPage([Page]+1)],each Table.FromRecords([Result][data])),Combined=Table.Combine(Pages) in Combined
Output
let
    BaseUrl = "https://api.example.com",
    GetPage = (page as number) as record =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    RelativePath = "v2/orders",
                    Query = [page = Text.From(page), per_page = "100"],
                    Headers = [Accept = "application/json"]
                ]
            )
        ),
    Pages = List.Generate(
        () => [Page = 1, Result = GetPage(1)],
        each List.Count([Result][data]) > 0,
        each [Page = [Page] + 1, Result = GetPage([Page] + 1)],
        each Table.FromRecords([Result][data])
    ),
    Combined = Table.Combine(Pages)
in
    Combined
Open this example in the tool

Custom function with try ... otherwise

The typed signature stays on the first line with the let body indented beneath it, which keeps the error handling path visible.

Input
(rawDate as nullable text) as nullable date=>let Cleaned=if rawDate=null then null else Text.Trim(rawDate),Parsed=try Date.FromText(Cleaned,[Format="dd/MM/yyyy",Culture="en-GB"]) otherwise null in Parsed
Output
(rawDate as nullable text) as nullable date =>
    let
        Cleaned = if rawDate = null then null else Text.Trim(rawDate),
        Parsed =
            try
                Date.FromText(
                    Cleaned, [Format = "dd/MM/yyyy", Culture = "en-GB"]
                ) otherwise null
    in
        Parsed
Open this example in the tool

Merge, expand and filter from SQL Server

Join keys and the expanded column lists are split one argument per line, which makes a mismatched key list easy to notice.

Input
let Orders=Sql.Database("sql-prod-01","Sales"){[Schema="dbo",Item="Orders"]}[Data],Merged=Table.NestedJoin(Orders,{"CustomerID"},Customers,{"CustomerID"},"Customer",JoinKind.LeftOuter),Expanded=Table.ExpandTableColumn(Merged,"Customer",{"Segment","Country"},{"Customer Segment","Customer Country"}),Paid=Table.SelectRows(Expanded,each [Status]="paid" and [OrderDate]>=#date(2026,1,1)) in Paid
Output
let
    Orders = Sql.Database(
        "sql-prod-01", "Sales"
    ){
        [Schema = "dbo", Item = "Orders"]
    }[Data],
    Merged = Table.NestedJoin(
        Orders,
        {"CustomerID"},
        Customers,
        {"CustomerID"},
        "Customer",
        JoinKind.LeftOuter
    ),
    Expanded = Table.ExpandTableColumn(
        Merged,
        "Customer",
        {"Segment", "Country"},
        {"Customer Segment", "Customer Country"}
    ),
    Paid = Table.SelectRows(
        Expanded, each [Status] = "paid" and [OrderDate] >= #date(2026, 1, 1)
    )
in
    Paid
Open this example in the tool

Common errors and how to fix them

ErrorCauseFix
Expected to find a right parenthesis <')'>, but a keyword <'in'> was found insteadA function call inside a step was not closed before the in keyword.Add the missing ) at the end of the step the error points to.
A comma cannot proceed an 'in'The last step before in ends with a comma. (The wording is the parser’s own.)Delete the comma after the final step.
Expected to find either a comma <','> or keyword <'in'>.Two steps have no comma between them, a step ends with a semicolon, or a stray ) closes the step early. The Advanced Editor reports a missing comma as Expression.SyntaxError: Token Comma expected.Look at the end of the step before the reported position and add the comma, or remove the extra bracket.
Unterminated stringA text literal or a quoted step name such as #"Changed Type is missing its closing double quote.Close the string. Quotes inside M strings are escaped by doubling them (“”).
Expected to find one of the following, but the end-of-stream was reached insteadThe query stops in the middle of an expression, for example in with nothing after it.Add the name of the step to return after in, or finish the incomplete expression.
Formula.Firewall: Query 'Search' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.Reported by Power Query when the query runs, never by this page. One step both references another query and calls a data source.Update Power BI Desktop: since July 2026 this pattern is allowed by default when privacy levels are compatible. Otherwise combine the logic into one query or separate data access from the step that references the other query.

Frequently asked questions

Where do I get the M code to format?

In Excel or Power BI Desktop open Power Query, select the query and choose Advanced Editor. Copy everything, format it here and paste it back.

Will formatting change what my query does or break query folding?

No. Only whitespace and line breaks change, so step names, their order, every literal and the folding behaviour stay the same.

Does it support Power Query in Dataflows and Fabric?

Yes. Dataflows, Fabric and Excel all use the same M language, so code from any of them formats the same way.

Is this the formatter used in VS Code?

It is the same @microsoft/powerquery-formatter package that Microsoft’s Power Query tooling uses.

Why do I get Expression.Error: The column 'X' of the table wasn't found?

A step refers to a column that is no longer in the data, usually because the source file changed a header or an earlier step renamed it. The formatter cannot see your data, so check the step named in the error against the source.

Why can my query not be refreshed in the Power BI service?

A URL or path built from values at run time makes a dynamic data source, which usually cannot be refreshed there. For web sources, keep the base URL static and pass the variable parts through the RelativePath and Query options of Web.Contents.

Related tools