Unpivot complejo - nombres de columna como filas

[Oscar_Mtz_V]

Hola, necesito tu apoyo en lo siguiente.

Tengo la mesa a la izquierda y necesito transformarla en la de la derecha.

Soy capaz de hacerlo usando el siguiente código y pivotando / despivo y filtrado, pero me pregunto si hay una manera mejor, más eficiente?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROGRAM", type text}, {"Attribute Name 1", type text}, {"Attribute Name 2", type text}, {"Attribute Value 1", Int64.Type}, {"Attribute Value 2", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PROGRAM", "Attribute Value 1", "Attribute Value 2"}, "Attribute", "Value"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"PROGRAM", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.AfterDelimiter(_, " ", 1), type text}}),
    #"Extracted Text After Delimiter1" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Attribute.1", each Text.AfterDelimiter(_, " ", 1), type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Extracted Text After Delimiter1", "Custom", each if [Attribute] = [Attribute.1] then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Attribute.1", "Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "Value.1", List.Sum)
in
    #"Pivoted Column"

Uno de los desafíos es que los nombres de atributo van a cambiar y se agregarán nuevos.

Adicional, ¿sería más fácil hacerlo en la base de datos de origen? Me estoy conectando a un HIVE DB.

En el siguiente enlace encontrará el archivo de Excel del ejemplo (necesito hacerlo en Power BI).

https://1drv.ms/x/s!Aj1t-UWaJ-akgdJBHMs3zhpeYWEGQw?e=efS6jz

Gracias de antemano.

saludos

Oscar

[Ashish_Mathur]

Hola

Este código M parece funcionar

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROGRAM", type text}, {"Attribute Name 1", type text}, {"Attribute Name 2", type text}, {"Attribute Value 1", Int64.Type}, {"Attribute Value 2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "PROGRAM", "Attribute Name 1", "Attribute Name 2", "Attribute Value 1", "Attribute Value 2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index", "PROGRAM"}, "Attribute", "Value"),
    #"Added Index1" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index.1", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each try #"Added Index1" [Value] { [Index.1]  + 2 } otherwise null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Attribute] = "Attribute Name 1" or [Attribute] = "Attribute Name 2")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Index.1", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "Custom")
in
    #"Pivoted Column"

Espero que esto ayude.

[littlemojopuppy]

Hola Oscar - aquí hay un enlace a una solución. Hice algo similar la semana pasada.

Estoy de acuerdo con @AllisonKennedy que porque cada registro tiene valores nulos para 2/3 de los valores algo más tabular es probablemente una mejor solución. Pero si usted tiene que tenerlo como en la imagen debe ser fácil de pivotar para llegar allí.

[AllisonKennedy]

Hola Oscar

@Oscar_Mtz_V

Esto parece dos tablas colocadas una al lado de la otra en una, por lo que puede considerar intentar dividirlas en sus tablas separadas y hacer un anexo. No estoy convencido de que necesite el PIVOT en power Query - podría simplemente hacer esto en las visualizaciones en su lugar, pero lo he puesto en desde que lo pidió.

Necesitará tres consultas con los siguientes nombres para que esto funcione, solo tiene que actualizar el paso Origen con la ruta de acceso de archivo de Excel.

Atributo 1 de la Tabla1

Dejar
Origen: Excel.Workbook(File.Contents("C:-Ejemplo Unpivot.xlsx"), null, true),
Table1_Table de origen [Item-"Table1",Kind-"Table"]-[Datos],
"Eliminado Otras Columnas" - Table.SelectColumns(Table1_Table,-"PROGRAM", "Nombre de atributo 1", "Valor de atributo 1"-),
"Columnas renombradas" - Table.RenameColumns("Removed Other Columns","Nombre de atributo 1", "Nombre de atributo", "Valor de atributo 1", "Valor de atributo", "Valor de atributo" )
En
"Columnas renombradas"

Atributo 2 de la Tabla1

Dejar
Origen: Excel.Workbook(File.Contents("C:-Usuarios-Allison.DESKTOP-TFHI78M-Descargas-Ejemplo Unpivot.xlsx"), null, true),
Table1_Table de origen [Item-"Table1",Kind-"Table"]-[Datos],
"Eliminado Otras Columnas" - Table.SelectColumns(Table1_Table,-"PROGRAM", "Nombre de atributo 2", "Valor de atributo 2"-),
"Columnas renombradas" - Table.RenameColumns("Removed Other Columns","Nombre de atributo 2", "Nombre de atributo", "Valor de atributo 2", "Valor de atributo", "Valor de atributo" )
En
"Columnas renombradas"

Tabla 1 Todo

Dejar
Origen: Table.Combine('Atributo 1'Tabla1', ''Atributo1'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
"Tipo de cambio" - Table.TransformColumnTypes(Source,'PROGRAM", escriba text', ''Nombre de atributo'', escriba text', ''Valor de atributo', 'número de tipo'),
"Columna con pivote" - Table.Pivot(-"Changed Type", List.Distinct(-"Changed Type"[-"Nombre de atributo"]), "Nombre de atributo", "Valor de atributo", List.Sum)
En
"Columna pivotada"

[Oscar_Mtz_V]

@AllisonKennedy , gracias, mi mayor problema es que el caso real tiene más de 20 columnas para el nombre del atributo y el mismo número para el valor del atributo.

[AllisonKennedy]

¿Las 20 columnas se nombran de forma coherente? Creo que anexar las tablas es su mejor y más eficiente enfoque aquí y podría hacerse usando parámetros y una función personalizada si los nombres de columna son consistentes. Siempre que use elegir columnas, las 20 consultas y el anexo deben ser más eficaces que unpivot y pivote que está haciendo actualmente.

Otra opción es mirar el origen de datos, ¿es una exportación desde algún lugar? ¿Se puede exportar como 20 tablas diferentes? A continuación, puede utilizar la función integrada con obtener datos de la carpeta en power bi. Pero esto no es necesario, la función personalizada se puede construir muy bien.

Háganos saber la estructura de nomenclatura y si hay otras columnas en los datos.

[AllisonKennedy]

Este tema se ha movido al foro de Power Query

[Anonymous]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RZA7DoRADMXuMjUFCcyvJDkG4v7X2DhaKcU8WXLhwPu2px3tEWbEADLad/yFMjMGkFniYlYMIKvEzewYQHaJnqEzFtIzjQUaUaPOUylB1KjnBVqCqFHn6VWCqFHn6V2iZ4h6jvZUHujkPT8e0lGGvtMWSGcZDnDiAukqwwVOXSDdZXrG8gTwij/w/QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROGRAM = _t, #"Attribute Name 1" = _t, #"Attribute Name 2" = _t, #"Attribute Value 1" = _t, #"Attribute Value 2" = _t]),

    recToTab =(input)=> 
    let
    r=Record.FromList(input[#"Attribute Value 1"]&input[#"Attribute Value 2"],input[#"Attribute Name 1"]&input[#"Attribute Name 2"]),
    tab=Table.FromRecords({r})
    in tab,

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROGRAM", type text}, {"Attribute Name 1", type text}, {"Attribute Name 2", type text}, {"Attribute Value 1", Int64.Type}, {"Attribute Value 2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PROGRAM"}, {{"all", each recToTab(_)}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10"}, {"A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10"})
in
    #"Expanded all"

[Oscar_Mtz_V]

@AllisonKennedy, gracias por su respuesta, sí, hay más columnas que no deben ser despreoñadas, digamos que hay más atributos de los Programas, pero estos se comparten entre los programas.

Yo iría a los propietarios de la base de datos como el último resultado, más bien no.

[AllisonKennedy]

@Oscar_Mtz_V

Sí, se puede hacer sin ir a los propietarios de bases de datos. Puede continuar con el método actual de uso de unpivot o probar el enfoque de selección de columnas y anexar. ¿Sigo pensando que también debe guardar el último paso de "pivote" para hacer en la vista de informe dentro de las visualizaciones, a menos que lo necesite en Power Query para otra cosa?

¿Puede compartir una tabla de ejemplo o un archivo de Excel con los nombres de columna reales (todos ellos)? Si hay patrones en los nombres, podemos crear una función personalizada para esto.

PS, también voy a mover esta publicación al foro de Power Query para ver si obtiene más visibilidad de los especialistas de Power Query. 🙂

[Fowmy]

@Oscar_Mtz_V


¿Qué diferenciaría las columnas entre Name y Value?
En su ejemplo, es el nombre de atributo 1... y Valor de atributo 1..... Una forma de abordar este problema es apilar todas las columnas de nombre de atributo, una especie de anexión y por lo tanto lo mismo con los valores, entonces terminará con tres columnas PROGRAM, NAME y VALUE, entonces el resto es fácil.

________________________

Si mi respuesta fue útil, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla

Haga clic en el icono Thumbs-Up si le gusta esta respuesta 🙂

Youtube Linkedin

[Oscar_Mtz_V]

@Fowmy gracias por la respuesta, este es un enfoque interesante, mi preocupación es que en el caso real tengo más de 20 columnas para Nombre de atributo y 20 para el valor Atributo.

[Fowmy]

@Oscar_Mtz_V

Si el número de columnas se va a fijar como 20 columnas para cada atributo y valor, creo que es posible apilarlas.
tres columnas, por favor confirme si es así.

________________________

Si mi respuesta fue útil, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla

Haga clic en el icono Thumbs-Up si le gusta esta respuesta 🙂

Youtube Linkedin

[Oscar_Mtz_V]

Hola, Sí, el número de columnas para Nombres de atributo y Valores de atributo es Fijo.

[Jimmy801]

Hello @Oscar_Mtz_V 

here an approach that adds a new column with a table that uses the count of attribute-columns as trigger and creates for every pair of attirbutes a new row with name and value.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RZA7DoRADMXuMjUFCcyvJDkG4v7X2DhaKcU8WXLhwPu2px3tEWbEADLad/yFMjMGkFniYlYMIKvEzewYQHaJnqEzFtIzjQUaUaPOUylB1KjnBVqCqFHn6VWCqFHn6V2iZ4h6jvZUHujkPT8e0lGGvtMWSGcZDnDiAukqwwVOXSDdZXrG8gTwij/w/QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROGRAM = _t, #"Attribute Name 1" = _t, #"Attribute Name 2" = _t, #"Attribute Value 1" = _t, #"Attribute Value 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROGRAM", type text}, {"Attribute Name 1", type text}, {"Attribute Name 2", type text}, {"Attribute Value 1", Int64.Type}, {"Attribute Value 2", Int64.Type}}),
    HowManyAttributs = List.Count(List.Select(Table.ColumnNames(#"Changed Type"), each Text.Contains(Text.Upper(_), "ATTRIBUTE")))/2,
    CreateNewTableInNewColumn = Table.AddColumn
    (
        #"Changed Type",
        "New Attributes",
        (add)=> Table.FromRecords(List.Range(List.Generate
        (
            ()=> [Counter = 0],
            each [Counter] <= HowManyAttributs,
            (rec)=> 
            [
                Counter = rec[Counter]+1,
                Attribute Name= Record.Field(add, "Attribute Name " & Text.From(rec[Counter]+1)),
                Attribute Value= Record.Field(add, "Attribute Value " & Text.From(rec[Counter]+1))
            ],
            each Record.SelectFields(_,{"Attribute Name", "Attribute Value"})
        ),1))
    ), 
    RemoveAttributes = Table.SelectColumns(CreateNewTableInNewColumn, {"PROGRAM", "New Attributes"}),
    #"Expanded New Attributes" = Table.ExpandTableColumn(RemoveAttributes, "New Attributes", {"Attribute Name", "Attribute Value"}, {"Attribute Name", "Attribute Value"})
in
    #"Expanded New Attributes"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[amitchandak]

@Oscar_Mtz_V, consulte si esto puede ayudar a

https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/

[Oscar_Mtz_V]

@amitchandak gracias, pero realmente no aborda el problema de simplificar la consulta actual, donde varias columnas contienen varios nombres de columna y lo mismo para sus valores. ¡Salud!

[amitchandak]

@Oscar_Mtz_V , esto parece unpivot o transponer, pero el valor va a diferentes filas.

Transponer : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

Ver si transponer puede ayudar

@ImkeF, ¿puede ayudar en este