Power Query (M) Formatter

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.

Typical improvements over the single-line code the Advanced Editor produces after a few UI clicks:

• 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, comments and the order of steps are never changed.

Indentation, width and shortcuts

There are no M-specific options. The toolbar’s indent setting becomes the formatter’s indentation (spaces or a tab), and the line width tells it when to break a call or a record across lines. The Advanced Editor in Excel and Power BI Desktop uses a proportional font by default, so a slightly narrower width often reads better there than the 80 columns the toolbar starts with.

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. Ctrl/Cmd+Shift+M (minify) is not available for M.

Errors and practical notes

The parser is strict, which is useful: an error here is usually an error Power Query would report too, but with a clearer position. Messages name what the parser expected and what it found, for example a right parenthesis where the keyword in was found, which 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 — Power Query does not allow it. The second is a missing comma between two steps after editing code by hand. Both are reported at the right line.

Folder paths and URLs inside strings are left exactly as written, including backslashes. Comments written with // or /* */ are preserved. If you are pulling data from a CSV, the CSV formatter helps check the source file’s delimiter and quoting before you write the M to import it.

Examples

let Source=Json.Document(Web.Contents("https://api.example.com",[RelativePath="orders",Query=[status="paid"]])),Items=Source[data],AsTable=Table.FromRecords(Items),Renamed=Table.RenameColumns(AsTable,{{"created_at","Created"},{"total","Total"}}) in Renamed

let
    Source = Json.Document(
        Web.Contents(
            "https://api.example.com",
            [
                RelativePath = "orders",
                Query = [status = "paid"]
            ]
        )
    ),
    Items = Source[data],
    AsTable = Table.FromRecords(Items),
    Renamed = Table.RenameColumns(
        AsTable, {{"created_at", "Created"}, {"total", "Total"}}
    )
in
    Renamed

(start as date,optional days as nullable number)as list=>let n=if days=null then 7 else days,dates=List.Dates(start,n,#duration(1,0,0,0)) in List.Select(dates,each Date.DayOfWeek(_,Day.Monday)<5)

(start as date, optional days as nullable number) as list =>
    let
        n = if days = null then 7 else days,
        dates = List.Dates(start, n, #duration(1, 0, 0, 0))
    in
        List.Select(dates, each Date.DayOfWeek(_, Day.Monday) < 5)

let Source=Excel.CurrentWorkbook(){[Name="Sales"]}[Content],Grouped=Table.Group(Source,{"Region"},{{"Revenue",each List.Sum([Amount]),type number},{"Orders",each Table.RowCount(_),Int64.Type}}),Sorted=Table.Sort(Grouped,{{"Revenue",Order.Descending}}) in Sorted

let
    Source = Excel.CurrentWorkbook(){[Name = "Sales"]}[Content],
    Grouped = Table.Group(
        Source,
        {"Region"},
        {
            {"Revenue", each List.Sum([Amount]), type number},
            {"Orders", each Table.RowCount(_), Int64.Type}
        }
    ),
    Sorted = Table.Sort(Grouped, {{"Revenue", Order.Descending}})
in
    Sorted

Common errors and how to fix them

Related tools

• DAX (Power BI) formatter
• CSV formatter
• SQL formatter
• JSON formatter
• KQL (Kusto Query Language) formatter
• csv and excel encoding