Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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
Thanks a ton. It works brilliantly and with minimal code. Unnecessarily, expanding the lists in the record was my mistake