Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |