Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
The best result I can have ( by developping ID ) is this :
Solved! Go to Solution.
@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"
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"
Thank you, it works as I wish but I prefer the answer of smpa01 because I understand it better
@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"
Thank you, it works perfeclty and I understand well the different steps
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.