Soluție compusă la Podcast 2316 Challenge - Sfaturi Excel

Notă

Acesta este unul dintr-o serie de articole care detaliază soluțiile trimise pentru provocarea Podcast 2316.

După ce am studiat toate ideile trimise de spectatori, am ales tehnicile mele preferate din fiecare videoclip. Soluția mea finală utilizează acești pași:

• Obțineți date, din gama numită
• Ștergeți cei doi pași suplimentari adăugați la Promovarea antetelor și Modificarea tipului. Acest lucru împiedică să rupe sufixul din sferturi. Mulțumim lui Jason M, Ondřej Malinský și Peter Bartholomew pentru această idee.
• Transpune
• Promovați anteturi
• Eliminați, Top Rows, Top 5 Rows. Frumos truc de la MF Wong.
• Înlocuiți Q1 cu _Q1. Repetați pentru alte trei sferturi. Mulțumesc Jonathan Cooper.
• Împărțit de Delimiter la _. Acest pas uimitor păstrează numele într-o coloană și mută sferturile în coloana următoare. Propus de Fowmy, perfecționat de Jonathan Cooper.
• (Nu este un pas!) Accesați bara de formule și redenumiți coloanele pentru a fi Angajat și Trimestrial. Mulțumesc Josh Johnson
• În coloana Angajați, nu înlocuiți nimic cu nul
• Completați
• În coloana Trimestru, schimbați valoarea nulă la Total. Această idee de la Michael Karpfen
• Dezimpivotați alte coloane. Redenumiți atributul la categorie în bara de formule
• Pivot Quarters
• Mutați Coloana Totală la sfârșit

Iată codul meu 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"

Toți cei menționați în aceste articole sau videoclipuri câștigă un patch Excel Guru. Am trimis deja mai multe prin poștă. Dacă nu primiți unul, lăsați un comentariu la videoclipul de mai jos.