Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |