Expand records to columns

[Anonymous]

Hello Community,

I have done a Table.Max Power Query on my Table.  After applying the previous step, columns got compressed into record that I want to re-expand to review details desired.

However, the expand button disappeared from the column. Wondering if there's a way to expand again. Below some figures to help you understand:

Thank you in advance!

Mobafa

[edhans · Accepted solution]

There is a good chance not all fields have the [Record] value, and that may be confusing the filter button. Try this:

Filter the table to only 1 record, the first one. See if that returns the expansion. If it does, great - expand that, then delete the filter for 1 record step.

If that doesn't work, you'll need to manually use the Table.ExpandRecordColumn function. See the sample code below to see how it works. It isn't difficult, just tedious to set up as you have to type in the field names of the record correctly, also paying attention to where you use parenthesis, square brackets, and squiggly brackets. And all of it is case sensitive. Column "TEST" is not the same at "Test" or "test"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added RecordField" = Table.AddColumn(Source, "RecordField", each [A=1, B=2, C=3]),
    #"Expanded RecordField" = Table.ExpandRecordColumn(#"Added RecordField", "RecordField", {"A", "B", "C"}, {"A", "B", "C"})
in
    #"Expanded RecordField"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.