Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GrantBrunton
Frequent Visitor

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

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:

IdNombreClaseitems.nameitems.value
4ghi123Clase 2NombreAlfa
4ghi123Clase 2NombreBeta
4ghi123Clase 2Descripcióncosas1
4ghi123Clase 2Descripcióncosas2
4ghi123Clase 2Serial01FD6D30FCA3CA51A81BBC640E35032D
4ghi123Clase 2Serial1B0EBF6E2B2C2FAF59693BE697855A29
5jkl456Clase 2NombreJames
5jkl456Clase 2NombreSimon
5jkl456Clase 2Descripcióncosa1
5jkl456Clase 2Descripcióncosa2
5jkl456Clase 2Serial0089FF209EEDBFED7089B5E1714B171948
5jkl456Clase 2Serial1B0EBF6E2B2C2FAF59693BE697855A29
6mno789Clase 3NombreSarah
6mno789Clase 3NombreSally
6mno789Clase 3NombreJane
6mno789Clase 3NombreMelissa
6mno789Clase 3Descripciónalgo1
6mno789Clase 3Descripciónalgo2
6mno789Clase 3Descripciónalgo3
6mno789Clase 3Descripciónalgo4
6mno789Clase 3Serial1B0EBF6E2B2C2FAF59693BE697855A29
6mno789Clase 3SerialCCFD8BCBBCB4098D40383B993AE6C762
6mno789Clase 3Serial4DEBF92BED9C14E884D8AD59E00F693B
6mno789Clase 3Serial0089FF209EEDBFED7089B5E1714B171948

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

idnameclassItem NameItem DescriptionItem Serial

IdNombreClaseNombre del elementoDescripción del artículoSerie de artículos
4ghi123Clase 2Alfacosas101FD6D30FCA3CA51A81BBC640E35032D
4ghi123Clase 2Betacosas21B0EBF6E2B2C2FAF59693BE697855A29
5jkl456Clase 2Jamescosa10089FF209EEDBFED7089B5E1714B171948
5jkl456Clase 2Simoncosa21B0EBF6E2B2C2FAF59693BE697855A29
6mno789Clase 3Sarahblob981B0EBF6E2B2C2FAF59693BE697855A29
6mno789Clase 3Sallyblob123CCFD8BCBBCB4098D40383B993AE6C762
6mno789Clase 3Janeblob5644DEBF92BED9C14E884D8AD59E00F693B
6mno789Clase 3Melissablob9870089FF209EEDBFED7089B5E1714B171948

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"

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors