Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"