How to Pivot nested name/value data when values are multiple sized arrays

Hi GrantBrunton ,

You need to follow the steps below:

• Group rows:
  • ID
  • Name
  • Class
  • items.name
  • Select items values with all the value
• Now redo the last part of the group rows step with the following syntax (ther part after the each:

Text.Combine([items.values], ","), type text

•  Pivot by the column ITEMVALUES
• Add custom column with the following code:

Table.RenameColumns(


Table.FromColumns ( { Text.Split([Name], ","),Text.Split([Description], ","),Text.Split([Serial], ",") }),

 {{"Column1", "Name"},{"Column2", "Description"},{"Column3", "Serial"}},
 
 
    MissingField.Ignore
)

• Remove the Name, Description and Serial columns
• Expand the new created column.

Check code below:

let
    Source = Json.Document(File.Contents("C:\Data.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Removed Columns" = Table.RemoveColumns(#"Converted to Table",{"Name"}),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Removed Columns", "Value", {"objects"}, {"objects"}),
    #"Expanded objects" = Table.ExpandRecordColumn(#"Expanded Value", "objects", {"edges"}, {"edges"}),
    #"Expanded edges" = Table.ExpandListColumn(#"Expanded objects", "edges"),
    #"Expanded edges1" = Table.ExpandRecordColumn(#"Expanded edges", "edges", {"node"}, {"node"}),
    #"Expanded node" = Table.ExpandRecordColumn(#"Expanded edges1", "node", {"id", "name", "deviceClass", "configData"}, {"id", "name", "deviceClass", "configData"}),
    #"Expanded deviceClass" = Table.ExpandRecordColumn(#"Expanded node", "deviceClass", {"class"}, {"class"}),
    #"Expanded configData" = Table.ExpandRecordColumn(#"Expanded deviceClass", "configData", {"edges"}, {"edges"}),
    #"Expanded edges2" = Table.ExpandListColumn(#"Expanded configData", "edges"),
    #"Expanded edges3" = Table.ExpandRecordColumn(#"Expanded edges2", "edges", {"node"}, {"node"}),
    #"Expanded node1" = Table.ExpandRecordColumn(#"Expanded edges3", "node", {"groups"}, {"groups"}),
    #"Expanded groups" = Table.ExpandListColumn(#"Expanded node1", "groups"),
    #"Expanded groups1" = Table.ExpandRecordColumn(#"Expanded groups", "groups", {"items"}, {"items"}),
    #"Expanded items" = Table.ExpandListColumn(#"Expanded groups1", "items"),
    #"Expanded items1" = Table.ExpandRecordColumn(#"Expanded items", "items", {"name", "values"}, {"items.name", "items.values"}),
    #"Expanded values" = Table.ExpandListColumn(#"Expanded items1", "items.values"),
    #"Grouped Rows" = Table.Group(#"Expanded values", {"id", "name", "class", "items.name"}, {{"ITEMVALUES", each Text.Combine([items.values], ","), type text }}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[items.name]), "items.name", "ITEMVALUES"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each Table.RenameColumns(


Table.FromColumns ( { Text.Split([Name], ","),Text.Split([Description], ","),Text.Split([Serial], ",") }),

 {{"Column1", "Name"},{"Column2", "Description"},{"Column3", "Serial"}},
 
 
    MissingField.Ignore
)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Name", "Description", "Serial"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Name", "Description", "Serial"}, {"Name.1", "Description", "Serial"})
in
    #"Expanded Custom"

Be aware that if you have more columns you need to keep, then the grouping must be done by all of them.