JSON conversion in Power Query - how automatically get list of object property names

This is a question based on comment in Excel: Power Query - how to repeat block of code for each row in a table.

Let's say we have a JSON with array of object each with N properies:

{ "data": [ { "data1": 1, "data2": 2, //... "dataN": N }, { "data1": 11, "data2": 12, //... "dataN": 1N }, { "data1": 21, "data2": 22, //... "dataN": 2N } ]
}

I want to get a table with column for each data property.

+---------+---------+---+--------+
| data1 | data2 | … | DataN |
+---------+---------+---+--------+
| 1 | 2 | … | N |
| 11 | 12 | … | 1N |
| 21 | 22 | … | 2N |
+---------+---------+---+--------+

Initial steps are

Source = Json.Document(...),
data = Source[data],
ToTable = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Then Table.ExpandRecordColumn syntax is

Table.ExpandRecordColumn(ToTable, "Column1", {"data1", "data2" ... "dataN"}, {"data1", "data2" ... "dataN"})

This is really tedious to write it manually. How to obtain a lists containing names of JSON object properties ({"data1", "data2" ... "dataN"})?

1 Answer

This is a little embarrassing, I figured it out only a moment after I spend some time and effort to formulate my question. So I hope it will help to others.

The solution is Record.FieldNames.

If all object in JSON data Array have same set of properties, we can just extract FieldNames from any Record.

Source = Json.Document(...),
data = Source[data],
ToTable = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Original code end
// Gets list of FieldNames from the first row in Column1
Headers = Record.FieldNames(ToTable[Column1]{0}),
// Creates table with columns acc. to List in Head
Result = Table.ExpandRecordColumn(ToTable, "Column1", Headers, Headers)

Or you can just click to button in the GUI. For details see Ron Rosenfeld's answer to my other answer mentioned in the original question.