Solución compuesta para el desafío Podcast 2316: consejos de Excel

Nota

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

Después de estudiar todas las ideas enviadas por los espectadores, elegí mis técnicas favoritas de cada video. Mi solución final usa estos pasos:

• Obtener datos de un rango con nombre
• Elimine los dos pasos adicionales agregados a Promocionar encabezados y Cambiar tipo. Esto evita tener que romper el sufijo de los cuartos. Gracias a Jason M, Ondřej Malinský y Peter Bartholomew por esta idea.
• Transponer
• Promocionar encabezados
• Eliminar, filas superiores, 5 filas superiores. Buen truco de MF Wong.
• Reemplace Q1 con _Q1. Repita para otros tres cuartos. Gracias Jonathan Cooper.
• Dividido por delimitador en _. Este asombroso paso mantiene los nombres en una columna y mueve los cuartos a la siguiente columna. Propuesto por Fowmy, perfeccionado por Jonathan Cooper.
• (¡Ni un paso!) Busque en la barra de fórmulas y cambie el nombre de las columnas a Empleado y Trimestre. Gracias Josh Johnson
• En la columna Empleado, reemplace nada con nulo
• Rellenar hacia abajo
• En la columna Trimestre, cambie nulo a Total. Esta idea de Michael Karpfen
• Retirar otras columnas. Cambiar el nombre de Attrib a Categoría en la barra de fórmulas
• Cuartos de pivote
• Mover la columna de total al final

Aquí está mi código final:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Transposed Table" = Table.Transpose(Source), #"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))), #"Removed Top Rows" = Table.Skip(#"Changed Type",5), #"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows","Q1","_Q1",Replacer.ReplaceText,("Category Description")), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Q2","_Q2",Replacer.ReplaceText,("Category Description")), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Q3","_Q3",Replacer.ReplaceText,("Category Description")), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Q4","_Q4",Replacer.ReplaceText,("Category Description")), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value3", "Category Description", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), ("Employee", "Qtr")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Employee", type text), ("Qtr", type text))), #"Replaced Value4" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,("Employee")), #"Filled Down" = Table.FillDown(#"Replaced Value4",("Employee")), #"Replaced Value5" = Table.ReplaceValue(#"Filled Down",null,"Total",Replacer.ReplaceValue,("Qtr")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value5", ("Employee", "Qtr"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(Qtr)), "Qtr", "Value", List.Sum), #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",("Employee", "Attribute", "Q1", "Q2", "Q3", "Q4", "Total")) in #"Reordered Columns"

Todas las personas mencionadas en estos artículos o videos ganan un parche de Excel Guru. Ya envié varios por correo. Si no recibe uno, deje un comentario en el video a continuación.