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": ["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

 

  • 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

2 Replies

  • artemus's avatar
    artemus
    Microsoft Employee

    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

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