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

[GrantBrunton]

I have some data I'm getting from a GraphQL API that has a number of objects which can have groups of items linked to the objects.

There could be any number of items linked to the objects from 0 or more so the attributes of the items are recorded as name value pairs where the values are arrays of data for the items and the names are the attributes the items have.

Here is an example of the source JSON data:

{
  "data": {
    "objects": {
      "edges": [
        {
          "node": {
            "id": "4",
            "name": "ghi123",
            "deviceClass": {
              "id": "456",
              "class": "Class 2",
            },
            "configData": {
              "edges": [
                {
                  "node": {
                    "groups": [
                      {
                        "label": "Item 1",
                        "items": [
                          {
                            "name": "Name",
                            "values": [
                              "alpha",
                              "beta",
                            ]
                          },
                          {
                            "name": "Description",
                            "values": [
                              "stuff1",
                              "stuff2",
                            ]
                          },
                          {
                            "name": "Serial",
                            "values": [
                              "01FD6D30FCA3CA51A81BBC640E35032D",
                              "1B0EBF6E2B2C2FAF59693BE697855A29",
                            ]
                          }
                        ]
                      }
                    ]
                  }
                }
              ]
            }
          }
        },
        {
          "node": {
            "id": "5",
            "name": "jkl456",
            "deviceClass": {
              "id": "456",
              "class": "Class 2",
            },
            "configData": {
              "edges": [
                {
                  "node": {
                    "groups": [
                      {
                        "label": "Item 1",
                        "items": [
                          {
                            "name": "Name",
                            "values": [
                              "james",
                              "simon",
                            ]
                          },
                          {
                            "name": "Description",
                            "values": [
                              "thing1",
                              "thing2",
                            ]
                          },
                          {
                            "name": "Serial",
                            "values": [
                              "0089FF209EEDBFED7089B5E1714B171948",
                              "1B0EBF6E2B2C2FAF59693BE697855A29",
                            ]
                          }
                        ]
                      }
                    ]
                  }
                }
              ]
            }
          }
        },
        {
          "node": {
            "id": "6",
            "name": "mno789",
            "deviceClass": {
              "id": "789",
              "class": "Class 3",
            },
            "configData": {
              "edges": [
                {
                  "node": {
                    "groups": [
                      {
                        "label": "Item 1",
                        "items": [
                          {
                            "name": "Name",
                            "values": [
                              "sarah",
                              "Sally",
                              "Jane",
                              "Melissa",
                            ]
                          },
                          {
                            "name": "Description",
                            "values": [
                              "something1",
                              "something2",
                              "something3",
                              "something4"
                            ]
                          },
                          {
                            "name": "Serial",
                            "values": [
                              "1B0EBF6E2B2C2FAF59693BE697855A29",
                              "CCFD8BCBBCB4098D40383B993AE6C762",
                              "4DEBF92BED9C14E884D8AD59E00F693B",
                              "0089FF209EEDBFED7089B5E1714B171948",
                            ]
                          }
                        ]
                      }
                    ]
                  }
                }
              ]
            }
          }
        }
      ]
    }
  }
}

I've managed to input the data and get it into table form like so:

id	name	class	items.name	items.value
4	ghi123	Class 2	Name	alpha
4	ghi123	Class 2	Name	beta
4	ghi123	Class 2	Description	stuff1
4	ghi123	Class 2	Description	stuff2
4	ghi123	Class 2	Serial	01FD6D30FCA3CA51A81BBC640E35032D
4	ghi123	Class 2	Serial	1B0EBF6E2B2C2FAF59693BE697855A29
5	jkl456	Class 2	Name	james
5	jkl456	Class 2	Name	simon
5	jkl456	Class 2	Description	thing1
5	jkl456	Class 2	Description	thing2
5	jkl456	Class 2	Serial	0089FF209EEDBFED7089B5E1714B171948
5	jkl456	Class 2	Serial	1B0EBF6E2B2C2FAF59693BE697855A29
6	mno789	Class 3	Name	sarah
6	mno789	Class 3	Name	Sally
6	mno789	Class 3	Name	Jane
6	mno789	Class 3	Name	Melissa
6	mno789	Class 3	Description	something1
6	mno789	Class 3	Description	something2
6	mno789	Class 3	Description	something3
6	mno789	Class 3	Description	something4
6	mno789	Class 3	Serial	1B0EBF6E2B2C2FAF59693BE697855A29
6	mno789	Class 3	Serial	CCFD8BCBBCB4098D40383B993AE6C762
6	mno789	Class 3	Serial	4DEBF92BED9C14E884D8AD59E00F693B
6	mno789	Class 3	Serial	0089FF209EEDBFED7089B5E1714B171948

I'd like to pivot the items and end up with an output like below:

id	name	class	Item Name	Item Description	Item Serial
4	ghi123	Class 2	alpha	stuff1	01FD6D30FCA3CA51A81BBC640E35032D
4	ghi123	Class 2	beta	stuff2	1B0EBF6E2B2C2FAF59693BE697855A29
5	jkl456	Class 2	james	thing1	0089FF209EEDBFED7089B5E1714B171948
5	jkl456	Class 2	simon	thing2	1B0EBF6E2B2C2FAF59693BE697855A29
6	mno789	Class 3	sarah	something1	1B0EBF6E2B2C2FAF59693BE697855A29
6	mno789	Class 3	Sally	something2	CCFD8BCBBCB4098D40383B993AE6C762
6	mno789	Class 3	Jane	something3	4DEBF92BED9C14E884D8AD59E00F693B
6	mno789	Class 3	Melissa	something4	0089FF209EEDBFED7089B5E1714B171948

The M code I have so far is below but I can't get the pivot part in the last step to work since there are multiple values to pivot on.

Any idea what I can do from here?

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"),
    #"Pivoted Column" = Table.Pivot(#"Expanded values", List.Distinct(#"Expanded values"[items.name]), "items.name", "items.values")
in
    #"Pivoted Column"

[MFelix · Accepted solution]

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.

[GrantBrunton]

Thanks that works!

I thought I might have to do something like that but I was hoping there might be a simpler way to handle it. 😊