Soluzione composita per Podcast 2316 Challenge - Suggerimenti per Excel

Nota

Questo è uno di una serie di articoli che descrivono in dettaglio le soluzioni inviate per la sfida Podcast 2316.

Dopo aver studiato tutte le idee inviate dagli spettatori, ho scelto le mie tecniche preferite da ogni video. La mia soluzione finale utilizza questi passaggi:

• Ottieni dati, da intervallo denominato
• Elimina i due passaggi aggiuntivi aggiunti a Promuovi intestazioni e Modifica tipo. Ciò evita di dover rompere il suffisso dai quarti. Grazie a Jason M, Ondřej Malinský e Peter Bartholomew per questa idea.
• Trasporre
• Promuovi intestazioni
• Rimuovi, prime righe, prime 5 righe. Bel trucco di MF Wong.
• Sostituisci Q1 con _Q1. Ripeti per altri tre quarti. Grazie Jonathan Cooper.
• Dividi per delimitatore in corrispondenza di _. Questo incredibile passaggio mantiene i nomi in una colonna e sposta i quarti nella colonna successiva. Proposto da Fowmy, perfezionato da Jonathan Cooper.
• (Non un passaggio!) Raggiungi la barra della formula e rinomina le colonne in Impiegato e Trimestre. Grazie Josh Johnson
• Nella colonna Employee, non sostituire nulla con null
• Fill Down
• Nella colonna Quarter, cambia null in Total. Questa idea di Michael Karpfen
• Sblocca altre colonne. Rinomina attributo a categoria nella barra della formula
• Pivot Quarters
• Sposta la colonna totale alla fine

Ecco il mio codice finale:

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"

Tutti gli utenti menzionati in questi articoli o video vincono una patch Excel Guru. Ne ho già spediti diversi. Se non ne ricevi uno, lascia un commento sul video qui sotto.