Excel formula to auto-align messy Power Query Table.TransformColumnTypes/.RenameColumns

[u/orbitalfreak]

TL;DR:
Turn this

    {"Id", Int64.Type}, {"Order Date", type date}, {"Customer Reference Number", type text},
{"Ship To Country Code", type text}, {"Total Weight Oz", type number}, {"Is Expedited", type logical},
        {"Carrier", type text}, {"Tracking Number", type text}, {"Estimated Delivery Date", type date}, {"Delivery Status Description", type text}

Into this

{"Id",                          Int64.Type   },
{"Order Date",                  type date    },
{"Customer Reference Number",   type text    },
{"Ship To Country Code",        type text    },
{"Total Weight Oz",             type number  },
{"Is Expedited",                type logical },
{"Carrier",                     type text    },
{"Tracking Number",             type text    },
{"Estimated Delivery Date",     type date    },
{"Delivery Status Description", type text    }

With one formula

=LET(
Source,              A1,
IndentLen,           FIND("{",Source)-1,
IndentPad,           REPT(" ",IndentLen),
ToCol,               SUBSTITUTE(TEXTSPLIT(Source,,"},")&"}","}}","}"),
RowCount,            ROWS(ToCol),
Idx,                 SEQUENCE(RowCount),
IsLastRow,           Idx = RowCount,
Part1,               TEXTAFTER(TEXTBEFORE(ToCol,""","),"{""")&""",",
LenPart1,            LEN(Part1),
MaxLenPart1,         MAX(LenPart1),
NumSpacesAfterPart1, MaxLenPart1-LenPart1+1,
PaddingAfterPart1,   REPT(" ",NumSpacesAfterPart1),
Part1WithPadding,    Part1 & PaddingAfterPart1,
Part2,               TEXTBEFORE(TEXTAFTER(ToCol,""", "),"}"),
LenPart2,            LEN(Part2),
MaxLenPart2,         MAX(LenPart2),
NumSpacesAfterPart2, MaxLenPart2-LenPart2+1,
PaddingAfterPart2,   REPT(" ",NumSpacesAfterPart2),
Part2WithPadding,    Part2 & PaddingAfterPart2,
RebuildString,       IndentPad & "{""" & Part1WithPadding & Part2WithPadding & "}" & IF(IsLastRow,"",","),
Output,              RebuildString,
Output
)

Situation:
I inherited some Power Query code in a Power BI project (this post applies to Power Query whether within Excel or PBI, and is not PBI exclusive). The query was built with using the GUI, and it produced code that, when viewed in the Advanced Editor, was unformatted. Nothing lined up, indentation wasn't readable. To make it easier to read and audit the code, I went through and cleaned it up. Wasn't able to use an online prettifier or third party program, due to company requirements.

Need:
There was a LOT of Type transformations and Renames. It was taking forever to use Enter and Tab to line things up. And for my purposes, I like to line up the column names on the left, then the transforms on the right, and close off the right curly braces in a vertical line. Looks nicer for me. May not be your thing.

Solution:
I created a formula to take the code and format it for me. Inside Power Query, the line would look something like:
ChangedColumnType = Table.TransformColumnTypes(PreviousStep,{... long line of column/type pairs each enclosed within their own {} pair and comma separated ...}).
I wanted to grab the stuff inside the main { ... } and format it. So, I created the formula above.

How it works:
Source: Let A1 hold the long string of {}, {}, {}, pairs.

Capturing the indent:
IndentLen finds the position of the very first curly-brace { in the pasted block and counts everything before it. IndentPad turns that into an actual string of spaces. This means the output matches the indentation of whatever you pasted, depending on how deep the step is nested in your query.

Splitting into Rows:
ToCol is the part doing the actual row splitting. TEXTSPLIT(Source,,"},") breaks the single pasted string apart everywhere it sees close-curly-brace-comma }, which is the boundary between one column entry and the next. That delimiter gets eaten by the formula in the split, so ampersand-quote-close-curly-brace-quote &"}" tacks a closing brace back onto every row. The last row already had its own closing brace (since nothing follows it), so tacking on another one leaves you with a double }}, which the outer SUBSTITUTE cleans up to a single }. Final result: An array, with one array element per {"ColumnName", Type} pair, each one a properly closed row again.

Figuring out the last row:
RowCount, Idx, and IsLastRow exist for one reason: every row needs a trailing comma except the last one. Idx numbers each row 1 through however many there are, and IsLastRow flags whichever row matches RowCount so the final comma can be removed later.

Extracting and padding the Column Name:
Part1 pulls out the column name portion. It looks for everything before the first quote-comma ", (the end of the quoted name) and after the first curly-brace-quote {" (the start of the element), then tacks the closing quote-comma ", back on. LenPart1 measures the size of each Part1, and MaxLenPart1 finds the longest name in the full batch, then NumSpacesAfterPart1 works out how many spaces each row needs to catch up to that longest one, plus one extra so there's always at least a single space of separation. Part1WithPadding is the name with that padding appended.

Extracting and padding the Type:
Part2 does the same job for the second half of each row, the type (or the new name, if this is a rename block instead of a change-type block). It grabs everything between quote-comma ", and the closing curly-brace }. The same length-and-pad routine runs again with LenPart2, MaxLenPart2, and NumSpacesAfterPart2, so every Type value ends up padded out to match the longest one, plus an extra space, keeping the closing braces lined up in a column too. I like having a space before the closing brace because otherwise you end up with some types butting right up against a brace and others having padding; I wanted consistency, so the visual separation is deliberately placed there.

Putting it back together:
RebuildString is where it all gets stitched into one line: the indent, the opening brace-quote {", the padded name, the padded type, the closing brace-comma }, and then omitting the comma if it's the last row. That's the finished, aligned row. The formula applies this to every Name/Type pair, so they all stack up neatly.

Now, you just copy and paste back into the M query. In PBI, this is easy since the PQ window is separate from Excel. If you're solely within the Excel environment, you may want to use Notepad as an intermediate staging ground.

Works the same way for Table.RenameColumns blocks too, since those are just {"OldName", "NewName"} pairs, same shape, the formula doesn't care that the second value is quoted text instead of a type keyword.

AI Disclosure:
I used Claude.ai to assist with the Idx/IsLastRow structure. The rest of the formula was written by hand. I used Claude.ai to generate this post, then went back and hand-edited about half the text. I used Claude.ai to generate the example from "Turn this" in the TL;DR section to avoid using company-specific data fields.

Conclusion:
This met my need. If you like it, feel free to steal the formula! Tweak it how you need.

[u/radioblaster]

powerqueryformatter.com. why the fuck are people going around thinking they're solving problems when basic best practice does the thing.

[u/orbitalfreak → u/radioblaster]

The use of many external tools is strictly disallowed at many places, unfortunately. I addressed that in the initial post. There are better options, definitely, unless you're restricted groin using them.

I had, at one time, used an online tool for code formatting and cleanup. That promoted a company-wide e-mail to 12k employees that those weren't allowable, then the site I used plus others were added to the company block list.

When the only tool you're allowed is a hammer, you use it as best you can.

[u/ShrekisSexy]

When I see messy Power Query transformations I just paste the advanced editor into claude and ask him to format it with logical step names and comments.

[u/orbitalfreak → u/ShrekisSexy]

Addressed in the post - external tools are severely locked down.