Los MVP de Excel atacan el problema de limpieza de datos en Power Query - Consejos de Excel

Tabla de contenido

Nota

Este es uno de una serie de artículos que detallan las soluciones enviadas para el desafío Podcast 2316.

El MVP de Excel Oz Du Soleil del canal Excel on Fire en YouTube mencionó al jinete brasileño Kaique Pachecho. Oz fue la primera persona en notar que fui por el camino lento para sumar los cuatro cuartos.

El video de Oz es:
https://www.youtube.com/watch?v=OluZlF44PNI

Su código es:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Removed Columns" = Table.RemoveColumns(Source,("Column2", "Column3", "Column4", "Column5", "Column6")), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Administrative", type number), ("Holiday", Int64.Type), ("PTO/LOA/Jury Duty", Int64.Type), ("Project A", type number), ("Project B", type number), ("Project C", type number))), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if (Category Description) = "Q1" then null else if (Category Description) = "Q2" then null else if (Category Description) = "Q3" then null else if (Category Description) = "Q4" then null else (Category Description)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Custom")), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",(("Custom", "Names"))), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each (Category Description) = "Q1" or (Category Description) = "Q2" or (Category Description) = "Q3" or (Category Description) = "Q4"), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",("Names", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Names", "Category Description"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(#"Category Description")), "Category Description", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Sum",(("Addition", "TOTAL"))) in #"Renamed Columns1"

Otra solución, esta de Excel MVP John MacDougall.

  • John fue el primero en decir que al eliminar los dos pasos adicionales que agregó Power Query, eliminas los sufijos impares en los títulos duplicados Q1 Q2 Q3 Q4.
  • John usó una columna de índice al principio que se usaría al final para clasificar. Pero … John concatenó su columna de índice después de la descripción de la categoría. Usó un carácter de tubería vertical | para poder desglosar los datos más tarde.
  • John escribió su columna condicional como una columna personalizada en lugar de utilizar la interfaz de columna condicional.
Columna condicional como columna personalizada

Mira el video de John aquí:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

El MVP de Excel Ken Puls, coautor del libro M is for (Data) Monkey enviado en tres soluciones. Su columna condicional es probablemente la más corta.

Pero la solución preferida de Ken ignora la pregunta original. En lugar de crear la tabla en Power Query, crea un conjunto de datos pivotantes en Power Query y luego termina con una tabla dinámica.

La vista previa final de Ken en Power Query se ve así:

Conjunto de datos pivotantes

Aquí está el código de Ken:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q1_1", type number), ("Q2_2", type number), ("Q3_3", Int64.Type), ("Q4_4", Int64.Type), ("Employee 2", Int64.Type), ("Q1_5", Int64.Type), ("Q2_6", Int64.Type), ("Q3_7", Int64.Type), ("Q4_8", Int64.Type), ("Employee 3", Int64.Type), ("Q1_9", Int64.Type), ("Q2_10", Int64.Type), ("Q3_11", Int64.Type), ("Q4_12", Int64.Type), ("Employee 4", type number), ("Q1_13", type number), ("Q2_14", type number), ("Q3_15", type number), ("Q4_16", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",("Dept. Total", "Q1", "Q2", "Q3", "Q4")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", ("Category Description"), "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if Text.Contains((Attribute), "_") then null else (Attribute)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ((Attribute.2) null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",(("Attribute.1", "Quarter"), ("Value", "Amount"))), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",(("Category Description", type text), ("Quarter", type text), ("Amount", type number), ("Employee", type text))) in #"Changed Type2"

Después de crear esta consulta solo como una conexión, luego usa una tabla dinámica para crear el informe final.

Informe final con tabla dinámica

Soluciones de otros MVP:

  • El código de Wyn Hopkins está aquí: Power Query: Manejo de múltiples encabezados idénticos.
  • El código de Mike Girvin está aquí: Power Query: Extraer 2 caracteres de la izquierda de una columna.
  • La solución de fórmula de Roger Govier está aquí: Soluciones de fórmula.

Regrese a la página principal del desafío Podcast 2316.

Lea el siguiente artículo de esta serie: Power Query: más allá de la interfaz de usuario: Table.Split y más.

Articulos interesantes...