Dynamic Column list in Table.FromColumns

[Anonymous]

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": ["X","Y","Z"],
"field2":["M","N","O"],
"field3":[1,2,3]
}
}
}

the final table should have Field1... as column names and the values in the rows. I can automate up to the step, where it shows like this. But struggling with the next step

The next step is to use Table.fromcolumns to get the result I need in a custom column and UI generates the below :

#"Added Custom" = Table.AddColumn(#"Expanded Value", "Custom", each Table.FromColumns({[Value.field1],[Value.field2],[Value.field3]}))

No matter whatever I have tried , I am unable to dynamically generate a list like the one marked in red. If I use string manimulation to generate this, the function fails as it construes that as a text list and not a list of columns. The function which I am trying to use is below:

(SourceTable as table) =>
let

columnnames = Table.ColumnNames(SourceTable),
ColumnName =Table.ToColumns(SourceTable),
x=Table.ColumnCount(SourceTable),

ColumnsOfType = List.Select(columnnames, (name) =>
List.AllTrue(List.Transform(Table.Column(SourceTable, name), (cell) => Type.Is(Value.Type(cell), List.Type)))),



ak = Table.AddColumn(SourceTable,"Custom", each Table.FromColumns(Table.ToList({Table.SelectColumns(SourceTable, ColumnsOfType)})))

in
ak

Appreciate any help.

 Cheers,

Amit

[artemus · Accepted solution]

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]

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