Composite Solution to Podcast 2316 Challenge - Excel Tips

Merk

Dette er en av en serie artikler som beskriver løsninger som er sendt inn for Podcast 2316-utfordringen.

Etter å ha studert alle ideene som ble sendt fra seerne, har jeg valgt favorittteknikkene mine fra hver video. Min endelige løsning bruker disse trinnene:

• Få data, fra navngitt rekkevidde
• Slett de to ekstra trinnene som er lagt til i Promote Headers og Change Type. Dette forhindrer at du må bryte suffikset fra kvartalet. Takk til Jason M, Ondřej Malinský og Peter Bartholomew for denne ideen.
• Transponere
• Fremme overskrifter
• Fjern, Topprader, Topp 5 rader. Fint triks fra MF Wong.
• Bytt ut Q1 med _Q1. Gjenta i andre tre kvartaler. Takk Jonathan Cooper.
• Del av Delimiter på _. Dette fantastiske trinnet holder navnene i en kolonne og flytter kvartalene til neste kolonne. Foreslått av Fowmy, perfeksjonert av Jonathan Cooper.
• (Ikke et trinn!) Nå inn i formellinjen og gi nytt navn til kolonnene for å være ansatt og kvartal. Takk Josh Johnson
• Erstatt ingenting med null i kolonnen Ansatt
• Fyll ned
• I nullkolonnen endrer du null til Total. Denne ideen fra Michael Karpfen
• Løsne andre kolonner. Gi nytt navn til Attrib to Category i formellinjen
• Pivot Quarters
• Flytt total kolonne til slutten

Her er min siste 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 som er nevnt i disse artiklene eller videoen, vinner en Excel Guru-patch. Jeg har allerede sendt flere ut. Hvis du ikke mottar en, kan du legge igjen en kommentar til videoen nedenfor.