Cómo pivotar datos anidados de nombre/valor cuando los valores son matrices de varios tamaños

[GrantBrunton]

Tengo algunos datos que estoy obteniendo de una API de GraphQL que tiene una serie de objetos que pueden tener grupos de elementos vinculados a los objetos.

Puede haber cualquier número de elementos vinculados a los objetos de 0 o más para que los atributos de los elementos se registren como pares de valores de nombre donde los valores son matrices de datos para los elementos y los nombres son los atributos que tienen los elementos.

He logrado introducir los datos y ponerlos en forma de tabla de esta manera:

Id	Nombre	Clase	items.name	items.value
4	ghi123	Clase 2	Nombre	Alfa
4	ghi123	Clase 2	Nombre	Beta
4	ghi123	Clase 2	Descripción	cosas1
4	ghi123	Clase 2	Descripción	cosas2
4	ghi123	Clase 2	Serial	01FD6D30FCA3CA51A81BBC640E35032D
4	ghi123	Clase 2	Serial	1B0EBF6E2B2C2FAF59693BE697855A29
5	jkl456	Clase 2	Nombre	James
5	jkl456	Clase 2	Nombre	Simon
5	jkl456	Clase 2	Descripción	cosa1
5	jkl456	Clase 2	Descripción	cosa2
5	jkl456	Clase 2	Serial	0089FF209EEDBFED7089B5E1714B171948
5	jkl456	Clase 2	Serial	1B0EBF6E2B2C2FAF59693BE697855A29
6	mno789	Clase 3	Nombre	Sarah
6	mno789	Clase 3	Nombre	Sally
6	mno789	Clase 3	Nombre	Jane
6	mno789	Clase 3	Nombre	Melissa
6	mno789	Clase 3	Descripción	algo1
6	mno789	Clase 3	Descripción	algo2
6	mno789	Clase 3	Descripción	algo3
6	mno789	Clase 3	Descripción	algo4
6	mno789	Clase 3	Serial	1B0EBF6E2B2C2FAF59693BE697855A29
6	mno789	Clase 3	Serial	CCFD8BCBBCB4098D40383B993AE6C762
6	mno789	Clase 3	Serial	4DEBF92BED9C14E884D8AD59E00F693B
6	mno789	Clase 3	Serial	0089FF209EEDBFED7089B5E1714B171948

Me gustaría pivotar los elementos y terminar con una salida como la siguiente:

idnameclassItem NameItem DescriptionItem Serial

Id	Nombre	Clase	Nombre del elemento	Descripción del artículo	Serie de artículos
4	ghi123	Clase 2	Alfa	cosas1	01FD6D30FCA3CA51A81BBC640E35032D
4	ghi123	Clase 2	Beta	cosas2	1B0EBF6E2B2C2FAF59693BE697855A29
5	jkl456	Clase 2	James	cosa1	0089FF209EEDBFED7089B5E1714B171948
5	jkl456	Clase 2	Simon	cosa2	1B0EBF6E2B2C2FAF59693BE697855A29
6	mno789	Clase 3	Sarah	blob98	1B0EBF6E2B2C2FAF59693BE697855A29
6	mno789	Clase 3	Sally	blob123	CCFD8BCBBCB4098D40383B993AE6C762
6	mno789	Clase 3	Jane	blob564	4DEBF92BED9C14E884D8AD59E00F693B
6	mno789	Clase 3	Melissa	blob987	0089FF209EEDBFED7089B5E1714B171948

El código M que tengo hasta ahora está a continuación, pero no puedo conseguir que la parte de pivote funcione ya que hay varios valores para pivotar.

¿Alguna idea de lo que puedo hacer desde aquí?

Dejar
    Fuente ? Json.Document(Binary.FromText("ewogICJkYXRhIjogewogICAgIm9iamVjdHMiOiB7CiAgICAgICJlZGdlcyI6IFsKICAgICAgICB7CiAgICAgICAgICAibm9kZSI6IHsKICAgICAgICAgICAgImlkIjogIjQiLAogICAgICAgICAgICAibmFtZSI6ICJnaGkxMjMiLAogICAgICAgICAgICAiZGV2aWNlQ2xhc3MiOiB7CiAgICAgICAgICAgICAgImlkIjogIjQ1NiIsCiAgICAgICAgICAgICAgImNsYXNzIjogIkNsYXNzIDIiLAogICAgICAgICAgICB9LAogICAgICAgICAgICAiY29uZmlnRGF0YSI6IHsKICAgICAgICAgICAgICAiZWRnZXMiOiBbCiAgICAgICAgICAgICAgICB7CiAgICAgICAgICAgICAgICAgICJub2RlIjogewogICAgICAgICAgICAgICAgICAgICJncm91cHMiOiBbCiAgICAgICAgICAgICAgICAgICAgICB7CiAgICAgICAgICAgICAgICAgICAgICAgICJsYWJlbCI6ICJJdGVtIDEiLAogICAgICAgICAgICAgICAgICAgICAgICAiaXRlbXMiOiBbCiAgICAgICAgICAgICAgICAgICAgICAgICAgewogICAgICAgICAgICAgICAgICAgICAgICAgICAgIm5hbWUiOiAiTmFtZSIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAidmFsdWVzIjogWwogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAiYWxwaGEiLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAiYmV0YSIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICBdCiAgICAgICAgICAgICAgICAgICAgICAgICAgfSwKICAgICAgICAgICAgICAgICAgICAgICAgICB7CiAgICAgICAgICAgICAgICAgICAgICAgICAgICAibmFtZSI6ICJEZXNjcmlwdGlvbiIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAidmFsdWVzIjogWwogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAic3R1ZmYxIiwKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgInN0dWZmMiIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICBdCiAgICAgICAgICAgICAgICAgICAgICAgICAgfSwKICAgICAgICAgICAgICAgICAgICAgICAgICB7CiAgICAgICAgICAgICAgICAgICAgICAgICAgICAibmFtZSI6ICJTZXJpYWwiLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgInZhbHVlcyI6IFsKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIjAxRkQ2RDMwRkNBM0NBNTFBODFCQkM2NDBFMzUwMzJEIiwKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIjFCMEVCRjZFMkIyQzJGQUY1OTY5M0JFNjk3ODU1QTI5IiwKICAgICAgICAgICAgICAgICAgICAgICAgICAgIF0KICAgICAgICAgICAgICAgICAgICAgICAgICB9CiAgICAgICAgICAgICAgICAgICAgICAgIF0KICAgICAgICAgICAgICAgICAgICAgIH0KICAgICAgICAgICAgICAgICAgICBdCiAgICAgICAgICAgICAgICAgIH0KICAgICAgICAgICAgICAgIH0KICAgICAgICAgICAgICBdCiAgICAgICAgICAgIH0KICAgICAgICAgIH0KICAgICAgICB9LAogICAgICAgIHsKICAgICAgICAgICJub2RlIjogewogICAgICAgICAgICAiaWQiOiAiNSIsCiAgICAgICAgICAgICJuYW1lIjogImprbDQ1NiIsCiAgICAgICAgICAgICJkZXZpY2VDbGFzcyI6IHsKICAgICAgICAgICAgICAiaWQiOiAiNDU2IiwKICAgICAgICAgICAgICAiY2xhc3MiOiAiQ2xhc3MgMiIsCiAgICAgICAgICAgIH0sCiAgICAgICAgICAgICJjb25maWdEYXRhIjogewogICAgICAgICAgICAgICJlZGdlcyI6IFsKICAgICAgICAgICAgICAgIHsKICAgICAgICAgICAgICAgICAgIm5vZGUiOiB7CiAgICAgICAgICAgICAgICAgICAgImdyb3VwcyI6IFsKICAgICAgICAgICAgICAgICAgICAgIHsKICAgICAgICAgICAgICAgICAgICAgICAgImxhYmVsIjogIkl0ZW0gMSIsCiAgICAgICAgICAgICAgICAgICAgICAgICJpdGVtcyI6IFsKICAgICAgICAgICAgICAgICAgICAgICAgICB7CiAgICAgICAgICAgICAgICAgICAgICAgICAgICAibmFtZSI6ICJOYW1lIiwKICAgICAgICAgICAgICAgICAgICAgICAgICAgICJ2YWx1ZXMiOiBbCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICJqYW1lcyIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICJzaW1vbiIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICBdCiAgICAgICAgICAgICAgICAgICAgICAgICAgfSwKICAgICAgICAgICAgICAgICAgICAgICAgICB7CiAgICAgICAgICAgICAgICAgICAgICAgICAgICAibmFtZSI6ICJEZXNjcmlwdGlvbiIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAidmFsdWVzIjogWwogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAidGhpbmcxIiwKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgInRoaW5nMiIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICBdCiAgICAgICAgICAgICAgICAgICAgICAgICAgfSwKICAgICAgICAgICAgICAgICAgICAgICAgICB7CiAgICAgICAgICAgICAgICAgICAgICAgICAgICAibmFtZSI6ICJTZXJpYWwiLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgInZhbHVlcyI6IFsKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIjAwODlGRjIwOUVFREJGRUQ3MDg5QjVFMTcxNEIxNzE5NDgiLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAiMUIwRUJGNkUyQjJDMkZBRjU5NjkzQkU2OTc4NTVBMjkiLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgXQogICAgICAgICAgICAgICAgICAgICAgICAgIH0KICAgICAgICAgICAgICAgICAgICAgICAgXQogICAgICAgICAgICAgICAgICAgICAgfQogICAgICAgICAgICAgICAgICAgIF0KICAgICAgICAgICAgICAgICAgfQogICAgICAgICAgICAgICAgfQogICAgICAgICAgICAgIF0KICAgICAgICAgICAgfQogICAgICAgICAgfQogICAgICAgIH0sCiAgICAgICAgewogICAgICAgICAgIm5vZGUiOiB7CiAgICAgICAgICAgICJpZCI6ICI2IiwKICAgICAgICAgICAgIm5hbWUiOiAibW5vNzg5IiwKICAgICAgICAgICAgImRldmljZUNsYXNzIjogewogICAgICAgICAgICAgICJpZCI6ICI3ODkiLAogICAgICAgICAgICAgICJjbGFzcyI6ICJDbGFzcyAzIiwKICAgICAgICAgICAgfSwKICAgICAgICAgICAgImNvbmZpZ0RhdGEiOiB7CiAgICAgICAgICAgICAgImVkZ2VzIjogWwogICAgICAgICAgICAgICAgewogICAgICAgICAgICAgICAgICAibm9kZSI6IHsKICAgICAgICAgICAgICAgICAgICAiZ3JvdXBzIjogWwogICAgICAgICAgICAgICAgICAgICAgewogICAgICAgICAgICAgICAgICAgICAgICAibGFiZWwiOiAiSXRlbSAxIiwKICAgICAgICAgICAgICAgICAgICAgICAgIml0ZW1zIjogWwogICAgICAgICAgICAgICAgICAgICAgICAgIHsKICAgICAgICAgICAgICAgICAgICAgICAgICAgICJuYW1lIjogIk5hbWUiLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgInZhbHVlcyI6IFsKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgInNhcmFoIiwKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIlNhbGx5IiwKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIkphbmUiLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAiTWVsaXNzYSIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICBdCiAgICAgICAgICAgICAgICAgICAgICAgICAgfSwKICAgICAgICAgICAgICAgICAgICAgICAgICB7CiAgICAgICAgICAgICAgICAgICAgICAgICAgICAibmFtZSI6ICJEZXNjcmlwdGlvbiIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAidmFsdWVzIjogWwogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAic29tZXRoaW5nMSIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICJzb21ldGhpbmcyIiwKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgInNvbWV0aGluZzMiLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAic29tZXRoaW5nNCIKICAgICAgICAgICAgICAgICAgICAgICAgICAgIF0KICAgICAgICAgICAgICAgICAgICAgICAgICB9LAogICAgICAgICAgICAgICAgICAgICAgICAgIHsKICAgICAgICAgICAgICAgICAgICAgICAgICAgICJuYW1lIjogIlNlcmlhbCIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAidmFsdWVzIjogWwogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAiMUIwRUJGNkUyQjJDMkZBRjU5NjkzQkU2OTc4NTVBMjkiLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAiQ0NGRDhCQ0JCQ0I0MDk4RDQwMzgzQjk5M0FFNkM3NjIiLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAiNERFQkY5MkJFRDlDMTRFODg0RDhBRDU5RTAwRjY5M0IiLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAiMDA4OUZGMjA5RUVEQkZFRDcwODlCNUUxNzE0QjE3MTk0OCIsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICBdCiAgICAgICAgICAgICAgICAgICAgICAgICAgfQogICAgICAgICAgICAgICAgICAgICAgICBdCiAgICAgICAgICAgICAgICAgICAgICB9CiAgICAgICAgICAgICAgICAgICAgXQogICAgICAgICAgICAgICAgICB9CiAgICAgICAgICAgICAgICB9CiAgICAgICAgICAgICAgXQogICAgICAgICAgICB9CiAgICAgICAgICB9CiAgICAgICAgfQogICAgICBdCiAgICB9CiAgfQp9")),
    #"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"