Forum Discussion
Extracting data from lists and combining data
Hi everyone.
So I have a JSON file ( need to be ) with the specific format :
{
"Id":[0,1,2],
"Data1":[[5,1],[4,81,52,6,42,8,1,4,2,5],[18,36]],
"Date2":[["u","j","a","e"],["q","s","d","f","p"],["n","b"]],
"Date3":[[10,13],[10,25],[10,58]]
}
When I load my json file as a data source, I have :
The best result I can have ( by developping ID ) is this :
But I need to have data being like this :
So my question is : Can I have the result I want and if yes, how ?
Anonymous try this
let Source = Json.Document(File.Contents("C:\Users\user\Desktop\Daily\codebeautify.json")), #"Converted to Table" = Table.FromRecords({Source}), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Id", type any}, {"Data1", type any}, {"Date2", type any}, {"Date3", type any}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({[Id],[Data1],[Date2],[Date3]})), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}), #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Column2", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Column3", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Column4", each Text.Combine(List.Transform(_, Text.From), ","), type text}) in #"Extracted Values2"Here is another possible solution:
let Source = Json.Document(File.Contents("C:\Users\aolson\Downloads\Sample.json")), #"Converted to Table" = Table.FromColumns(Record.FieldValues(Source), Record.FieldNames(Source)), #"Expand Lists" = Table.TransformColumns(#"Converted to Table", List.Transform(List.Skip(Table.ColumnNames(#"Converted to Table")), (col) => {col, each Text.Combine(List.Transform(_, Text.From), ","), type text})) in #"Expand Lists"
4 Replies
Anonymous try this
let Source = Json.Document(File.Contents("C:\Users\user\Desktop\Daily\codebeautify.json")), #"Converted to Table" = Table.FromRecords({Source}), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Id", type any}, {"Data1", type any}, {"Date2", type any}, {"Date3", type any}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({[Id],[Data1],[Date2],[Date3]})), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}), #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Column2", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Column3", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Column4", each Text.Combine(List.Transform(_, Text.From), ","), type text}) in #"Extracted Values2"Here is another possible solution:
let Source = Json.Document(File.Contents("C:\Users\aolson\Downloads\Sample.json")), #"Converted to Table" = Table.FromColumns(Record.FieldValues(Source), Record.FieldNames(Source)), #"Expand Lists" = Table.TransformColumns(#"Converted to Table", List.Transform(List.Skip(Table.ColumnNames(#"Converted to Table")), (col) => {col, each Text.Combine(List.Transform(_, Text.From), ","), type text})) in #"Expand Lists"