Automatically expand record columns ?

[Anonymous]

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.

[mahoneypat · Accepted solution]

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

[Anonymous]

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 

[Anonymous]

Couple of questions:

1. Do you have sample M code ?

2. Will this degrade power query performance (assume 30K rows and 20 fields for each metaData record)?