cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NiblettS Frequent Visitor
Frequent Visitor

Converting Nested Arrays in JSON with Name/Value to a Table

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

1IdABCD
1ThingNameBERT
1ThingStateActive
1ThingSizeMedium
2IdEFGH
2ThingNameWILF
2ThingStateActive
2ThingSizeSmall
3IdIJKL
3ThingNameTERRY
3ThingStateInactive
3ThingSizeMedium
4IdMNOP
4ThingNameTailor
4ThingStateActive
4ThingSizeHuge

 

Required Output:

RowIdThingNameThingStateThingSize
1ABCDBERTActiveMedium
2EFGHWILFActiveSmall
3IJKLTERRYInactiveMedium
4MNOPTailorActiveHuge

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
ricardocamargos Established Member
Established Member

Re: Converting Nested Arrays in JSON with Name/Value to a Table

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"

 

2 REPLIES 2
ricardocamargos Established Member
Established Member

Re: Converting Nested Arrays in JSON with Name/Value to a Table

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"

 

Highlighted
NiblettS Frequent Visitor
Frequent Visitor

Re: Converting Nested Arrays in JSON with Name/Value to a Table

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.