Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic Column list in Table.FromColumns

Hi,   I have been trying to automate the ingestion of a json file. The file may have different column names. The structure of the file will be like this: { "results":{ "coldata": { "field1": [...
Need Help
  • artemus's avatar
    artemus
    6 years ago

    How about this:

        Source = (ColumnData as any) => let
        Source = ColumnData,
        #"Parsed JSON" = Json.Document(Source),
        results = #"Parsed JSON"[results],
        coldata = results[coldata],
        Custom1 = Table.FromColumns(Record.FieldValues(coldata), Record.FieldNames(coldata))
    in
        Custom1

    Where ColumnData is your raw Json text

artemus's avatar
artemus
Microsoft Employee
6 years ago

How about this:

    Source = (ColumnData as any) => let
        Source = ColumnData,
        #"Parsed JSON" = Json.Document(Source),
        results = #"Parsed JSON"[results],
        coldata = results[coldata],
        Custom1 = Table.FromColumns(Record.FieldValues(coldata), Record.FieldNames(coldata))
    in
        Custom1

Where ColumnData is your raw Json text

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks a ton. It works brilliantly and with minimal code. Unnecessarily, expanding the lists in the record was my mistake