Desafío de Bill "¿Cómo limpiarías estos datos?" - Consejos de Excel

Tabla de contenido

Cuando doy un seminario de Power Excel en vivo, ofrezco que si alguien en la sala alguna vez tiene un problema extraño con Excel, me lo pueden enviar para pedir ayuda. Así es como llegué a recibir este problema de limpieza de datos. Alguien tenía una hoja de trabajo de resumen que se ve así:

Hoja de trabajo de resumen

Querían reformatear los datos para que se vieran así:

Datos reformateados deseados

Una pista interesante sobre estos datos: el 18 en G4 parece ser un subtotal de H4: K4. Es tentador eliminar las columnas G, L, etc., pero primero debe extraer el nombre del empleado de G3, L3, etc.

Eran las 4 de la mañana del domingo 9 de febrero cuando encendí la grabadora de video y grabé algunos pasos torpes en Power Query para resolver el problema. Dado que era domingo, un día en el que normalmente no hago videos, pedí a las personas que enviaran sus ideas sobre cómo resolver el problema. Se han enviado 29 soluciones.

Cada solución ofrece una nueva y genial mejora sobre mi proceso. Mi plan es comenzar una serie de artículos que muestren las diversas mejoras de mi método.

Ver video

Antes de comenzar ese proceso, los invito a ver mi solución:

Y el código M que Power Query generó para mí:

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))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "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))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",(("Attribute.1", "TextValue"))), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length((TextValue))), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if (Length)> 2 then (TextValue) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Name")), #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",("Category Description", "Name", "TextValue", "Value", "Length")), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ((TextValue) = "Q1" or (TextValue) = "Q2" or (TextValue) = "Q3" or (TextValue) = "Q4")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each (Name) "Dept. Total"), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Length")), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"(TextValue)), "TextValue", "Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Name", Order.Ascending))), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each (Q1)+(Q2)+(Q3)+(Q4)) in #"Added Custom1"

Antes de comenzar con las soluciones, abordemos muchos comentarios comunes:

  • Algunos de ustedes dijeron que retrocederían para averiguar por qué los datos se muestran en este formato para empezar. Agradezco estos comentarios. Todos los que dijeron esto son mejores personas que yo. He aprendido a lo largo de los años que cuando preguntas "¿Por qué?" la respuesta generalmente involucra a este ex empleado que comenzó por este camino hace 17 años y todos lo siguen usando de esta manera ya que todos estamos acostumbrados.
  • Además, muchos de ustedes dijeron que la solución final debería ser una mesa vertical alta y luego usar una tabla dinámica para producir los resultados finales. Jonathan Cooper resumió esto de la mejor manera: "También estoy de acuerdo con algunos de los otros comentarios de YouTube de que un conjunto de datos adecuado no tendría" Totales "y no tendría que ser pivotado al final. Pero si el usuario realmente quiere un mesa vieja entonces les das lo que quieren ". De hecho, puedo ver ambos lados de esto. Me encantan las tablas dinámicas y lo único más divertido que Power Query es Power Query con una bonita tabla dinámica en la parte superior. Pero si podemos hacer todo en Power Query, entonces una cosa menos que romper.

Aquí hay hipervínculos a varias técnicas

  • Técnicas de Power Query

    • Numeración de grupos de registros
    • Extrayendo dos caracteres de la izquierda
    • Columna total
    • Otras cláusulas if
    • Múltiples encabezados idénticos en Power Query
    • Qué eliminar
    • Dividir por Q
    • Clasificación de elementos de línea
    • Soluciones de Power Query de Excel MVP

  • Más allá de la interfaz de Power Query

    • Mesa dividida
    • El mundo de Bill Szysz

  • Soluciones de fórmula

    • Una fórmula de matriz dinámica
    • Columnas de ayuda de la vieja escuela
    • Soluciones de fórmula

  • Compuesto de todas las ideas de arriba y video final

    • Compuesto de las mejores ideas de todas

Articulos interesantes...