Nota
Este es uno de una serie de artículos que detallan las soluciones enviadas para el desafío Podcast 2316.
Uno de los problemas con mi solución es que la secuencia final de las categorías no coincidía necesariamente con la secuencia original de las columnas. Me di cuenta de esto al final de mi video, y como no era particularmente importante, no me preocupé por eso.
Sin embargo, Josh Johnson envió una solución que lo manejó. Cuando Josh dijo que usó una columna de índice, asumí que era como el índice y módulo en Power Query: Numere los grupos de registros del 1 al 5 repetidamente. Pero el uso de Josh fue completamente diferente.
Nota: El MVP de Excel, John MacDougall, también utilizó este método, pero concatenó la columna de índice al final de la descripción de la categoría. Vea el video de John aquí: https://www.youtube.com/watch?v=Dqmb6SEJDXI y lea más sobre su código aquí: Los MVP de Excel atacan el problema de limpieza de datos en Power Query.
Al principio del proceso, cuando Josh todavía solo tenía seis registros, agregó un índice que comenzaba en 1. Josh hizo clic en la barra de fórmulas y renombró la columna de Índice como Categoría.
La columna Categoría era la nueva última columna. Usó Move, to Beginning para moverlo a ser el primero:
Después de esto, suceden muchos otros pasos. Son pasos que son innovadores pero que hasta ahora se han tratado principalmente en otros artículos. Después de muchos de estos pasos, comencé a pensar que las categorías 1 a 6 eran solo un error. Pensé que posiblemente Josh los eliminaría sin usarlos.
Josh Desactiva, luego columna condicional, luego rellena, luego pivota, suma el total. Parece que nunca usa esa columna de Categoría. Después de muchos pasos, está aquí:
Pero luego, en los pasos finales, Josh ordena los datos por nombre de empleado y luego por categoría.
En este punto, puede eliminar la columna Categoría. La diferencia final: PTO viene antes del Proyecto A, tal como lo tenía en las columnas originales. Es un buen toque.
También señalaré que Josh envió un video de él siguiendo estos pasos. ¡Felicitaciones a Josh por usar atajos de teclado dentro de Power Query!
Aquí está el código de Josh:
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))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"Regrese a la página principal del desafío Podcast 2316.
Lea el siguiente artículo de esta serie: Los MVP de Excel atacan el problema de limpieza de datos en Power Query.
