Forum Discussion
Automatically expand record columns ?
I am getting a JSON response from a web source that I convert into a table and then execute Table.ExpandRecordColumn.
example:
"metaData": {
"A": "1",
"B": "2",
"C": "3",
.
.
},
I have found issues with this approach.
1. I sometimes miss some Columns in the expansion because the Table contains 10K rows and the metaData key has 15-20 subkeys (I depicted only "A", "B", "C" above). I end up checking the JSON response and finding the missing subkeys and changing the ExpandRecordColumn for the missing sub-key.
2. A separate team is modifying the metaData object and dynamically adding and removing subkeys in the meData object. I want to be able to get the subkecys without changing ExpandRecordColumn every time.
Any suggestions to the above issues would help.
Depending on if you have a Table or a Record in your JSON response, you can use the Table.ColumnNames or Record.FieldNames functions to dynamically return the Column/Field names as a List you can then use in your Table.ExpandColumns functions (to replace the List of hardcoded names).
Pat
3 Replies
Depending on if you have a Table or a Record in your JSON response, you can use the Table.ColumnNames or Record.FieldNames functions to dynamically return the Column/Field names as a List you can then use in your Table.ExpandColumns functions (to replace the List of hardcoded names).
Pat
I figured out that the following code works
Table.ExpandRecordColumn(
#"Table From Previous Step",
"metaData",
//Return Field Names
Record.FieldNames(
//Combine the records
Record.Combine(
//Select items in list which are not null, and return a list
List.Select(
//Get a list for the metaData column
Table.Column(
#"Table From Previous Step", "metaData"
),
(x) => x <> null
)
)
)
),, and performance seems to be OK