Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago
Solved

Pivotar varias columnas

Hola

Tengo una tabla como esta:

Data.PNG

aunque tiene más de 8 columnas.

¿Podría decirme cómo puedo pivotar toda la tabla, de tal manera que por cada día obtenga una fila, no varias filas?

  • Syndicate_Admin's avatar
    Syndicate_Admin
    4 years ago

    @eshragh_g , Compruebe este código de consulta de energía. Columna de valor Unpivot. Columna de fusión - Comida + atribución

    Pivote de nuevo

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY31DcyMDJS0lHyKc1LzgDShgYgQilWB0XWqSg1MTstsbgEJGkIIvSM0NWkZOblpRYBGRZAbAyVNcJrvhER5hvhMj8WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Meal = _t, Cal = _t, Fat = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Meal", type text}, {"Cal", Int64.Type}, {"Fat", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Meal"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Meal", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
    #"Pivoted Column"

    Agregar a una consulta en blanco y marcar

2 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    @eshragh_g , Compruebe este código de consulta de energía. Columna de valor Unpivot. Columna de fusión - Comida + atribución

    Pivote de nuevo

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY31DcyMDJS0lHyKc1LzgDShgYgQilWB0XWqSg1MTstsbgEJGkIIvSM0NWkZOblpRYBGRZAbAyVNcJrvhER5hvhMj8WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Meal = _t, Cal = _t, Fat = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Meal", type text}, {"Cal", Int64.Type}, {"Fat", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Meal"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Meal", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
    #"Pivoted Column"

    Agregar a una consulta en blanco y marcar