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.
Having read through many of the related posts/ I still can't get from A to B... I seem to get partially there (e.g. Table of RowNum, or columns of Name-Vaue but can't get it in to the table format I want. It seems really basic but I have missed something.
JSON Sample source
{ "dataset": [{"rowNum":"1","row": [{"name":"Id","value":"ABCD"}, {"name":"ThingName","value":"BERT"}, {"name":"ThingState","value":"Active"}, {"name":"ThingSize","value":"Medium"} ]}, {"rowNum":"2","row": [{"name":"Id","value":"EFGH"}, {"name":"ThingName","value":"WILF"}, {"name":"ThingState","value":"Active"}, {"name":"ThingSize","value":"Small"} ]}, {"rowNum":"3","row": [{"name":"Id","value":"IJKL"}, {"name":"ThingName","value":"TERRY"}, {"name":"ThingState","value":"Inactive"}, {"name":"ThingSize","value":"Medium"} ]}, {"rowNum":"4","row": [{"name":"Id","value":"MNOP"}, {"name":"ThingName","value":"Tailor"}, {"name":"ThingState","value":"Active"}, {"name":"ThingSize","value":"Huge"} ]} ] }
Nearest output:
Value.rowNumValue.row.nameValue.row.value
1 | Id | ABCD |
1 | ThingName | BERT |
1 | ThingState | Active |
1 | ThingSize | Medium |
2 | Id | EFGH |
2 | ThingName | WILF |
2 | ThingState | Active |
2 | ThingSize | Small |
3 | Id | IJKL |
3 | ThingName | TERRY |
3 | ThingState | Inactive |
3 | ThingSize | Medium |
4 | Id | MNOP |
4 | ThingName | Tailor |
4 | ThingState | Active |
4 | ThingSize | Huge |
Required Output:
Row | Id | ThingName | ThingState | ThingSize |
1 | ABCD | BERT | Active | Medium |
2 | EFGH | WILF | Active | Small |
3 | IJKL | TERRY | Inactive | Medium |
4 | MNOP | Tailor | Active | Huge |
It has to be easier than the way I am doing this; this should be bread&butter to Power Query
let Source = Json.Document(File.Contents("H:\DATASYNC\CyberArchitecture\Domains\Domain-Summary-Upload\sample.json")), #"Converted to Table" = Record.ToTable(Source), #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"), #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"rowNum", "row"}, {"Value.rowNum", "Value.row"}), #"Expanded Value.row" = Table.ExpandListColumn(#"Expanded Value1", "Value.row"), #"Expanded Value.row1" = Table.ExpandRecordColumn(#"Expanded Value.row", "Value.row", {"name", "value"}, {"Value.row.name", "Value.row.value"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Value.row1",{"Name"}) in #"Removed Columns"
l
Solved! Go to Solution.
Hi @NiblettS,
Please try this code:
let
Source = Json.Document(File.Contents("H:\DATASYNC\CyberArchitecture\Domains\Domain-Summary-Upload\sample.json")),
dataset = Source[dataset],
#"Converted to Table" = Table.FromList(dataset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"rowNum", "row"}, {"rowNum", "row"}),
#"Expanded row" = Table.ExpandListColumn(#"Expanded Column1", "row"),
#"Expanded row1" = Table.ExpandRecordColumn(#"Expanded row", "row", {"name", "value"}, {"name", "value"}),
#"Pivoted Column" = Table.Pivot(#"Expanded row1", List.Distinct(#"Expanded row1"[name]), "name", "value")
in
#"Pivoted Column"
Hi @NiblettS,
Please try this code:
let
Source = Json.Document(File.Contents("H:\DATASYNC\CyberArchitecture\Domains\Domain-Summary-Upload\sample.json")),
dataset = Source[dataset],
#"Converted to Table" = Table.FromList(dataset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"rowNum", "row"}, {"rowNum", "row"}),
#"Expanded row" = Table.ExpandListColumn(#"Expanded Column1", "row"),
#"Expanded row1" = Table.ExpandRecordColumn(#"Expanded row", "row", {"name", "value"}, {"name", "value"}),
#"Pivoted Column" = Table.Pivot(#"Expanded row1", List.Distinct(#"Expanded row1"[name]), "name", "value")
in
#"Pivoted Column"
That is perfect Many Thanks @ricardocamargos- I was able to expand it to my real data set and it worked like a dream.... now my task is to reverse engineer and establish why I could not do it myself.
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.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |