Composite Solution to Podcast 2316 Challenge - Excel Tips

Bemærk

Dette er en af ​​en række artikler, der beskriver løsninger sendt til Podcast 2316-udfordringen.

Efter at have studeret alle de ideer, der blev sendt fra seerne, har jeg valgt mine foretrukne teknikker fra hver video. Min endelige løsning bruger disse trin:

• Få data fra det navngivne område
• Slet de to ekstra trin, der er tilføjet til Promover Headers og Change Type. Dette forhindrer at skulle bryde suffikset fra kvartalerne. Tak til Jason M, Ondřej Malinský og Peter Bartholomew for denne idé.
• Transponere
• Fremme overskrifter
• Fjern, Toprækker, Top 5 rækker. Dejligt trick fra MF Wong.
• Udskift Q1 med _Q1. Gentag i andre tre kvartaler. Tak Jonathan Cooper.
• Opdel af delimiter ved _. Dette fantastiske trin holder navnene i en kolonne og flytter kvartalerne til den næste kolonne. Foreslået af Fowmy, perfektioneret af Jonathan Cooper.
• (Ikke et trin!) Nå ind i formellinjen, og omdøb kolonnerne til medarbejder og kvartal. Tak Josh Johnson
• Erstat intet med null i kolonnen Medarbejder
• Fyld ned
• Skift null til Total i kolonnen Kvartal. Denne idé fra Michael Karpfen
• Frigør andre kolonner. Omdøb Attrib til kategori i formellinjen
• Pivot Quarters
• Flyt den samlede kolonne til slutningen

Her er min sidste kode:

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"

Alle nævnt i disse artikler eller video vinder en Excel Guru-patch. Jeg har allerede sendt flere ud. Hvis du ikke modtager en, skal du efterlade en kommentar til videoen nedenfor.