Forum Discussion

NiblettS's avatar
NiblettS
New Member
8 years ago
Solved

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 ...
  • ricardocamargos's avatar
    ricardocamargos
    8 years ago

    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"

     

ricardocamargos's avatar
ricardocamargos
Continued Contributor
8 years ago

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"

 

NiblettS's avatar
NiblettS
New Member
8 years ago

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.