Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 stepCapture.PNG

 

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

 

1 ACCEPTED SOLUTION
artemus
Microsoft Employee
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

View solution in original post

2 REPLIES 2
artemus
Microsoft Employee
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
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.