Bemærk
Dette er en af en række artikler, der beskriver løsninger sendt til Podcast 2316-udfordringen.
Et af problemerne med min løsning er, at den endelige sekvens af kategorierne ikke nødvendigvis matchede den oprindelige rækkefølge af kolonnerne. Jeg indså dette i slutningen af min video, og da det ikke var særlig vigtigt, bekymrede jeg mig ikke om det.
Imidlertid sendte Josh Johnson en løsning, der håndterede den. Da Josh sagde, at han brugte en indeks-kolonne, antog jeg, at det var som indekset og modulet i Power Query: Antal grupper af poster som 1 til 5 gentagne gange. Men Joshs brug var helt anderledes.
Bemærk: Excel MVP John MacDougall brugte også denne metode, men han sammenkædede indekskolonnen til slutningen af kategoribeskrivelsen. Se Johns video her: https://www.youtube.com/watch?v=Dqmb6SEJDXI og læs mere om hans kode her: Excel MVP'er Attack the Data Cleansing Problem in Power Query.
Tidligt i processen, da Josh stadig kun havde seks poster, tilføjede han et indeks, der startede med 1. Josh klikkede i formellinjen og omdøbte kolonnen Indeks til Kategori.
Kategorikolonnen var den nye sidste kolonne. Han brugte Move, to Beginning for at flytte det til at være først:
Efter dette sker der mange andre trin. De er trin, der er innovative, men hidtil mest er blevet dækket af de andre artikler. Efter mange sådanne trin begyndte jeg at tro, at kategorinumrene 1 til 6 bare var en fejltagelse. Jeg troede, at Josh muligvis ville slette dem uden at bruge dem.
Josh Unpivots, derefter betinget kolonne, så udfyld, og drej derefter, tilføjer summen. Han ser aldrig ud til at bruge den kategorikolonne. Efter mange trin er han her:
Men så i de sidste trin sorterer Josh dataene efter medarbejdernavn og derefter kategori!
På dette tidspunkt kan han slette kategorikategorien. Den sidste forskel: PTO kommer før projekt A, ligesom det havde i de originale kolonner. Det er et dejligt strejf.
Jeg vil også påpege, at Josh sendte en video af ham igennem disse trin. Kudos til Josh for at bruge tastaturgenveje inde i Power Query!
Her er Joshs kode:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q1_1", type number), ("Q2_2", type number), ("Q3_3", Int64.Type), ("Q4_4", Int64.Type), ("Employee 2", Int64.Type), ("Q1_5", Int64.Type), ("Q2_6", Int64.Type), ("Q3_7", Int64.Type), ("Q4_8", Int64.Type), ("Employee 3", Int64.Type), ("Q1_9", Int64.Type), ("Q2_10", Int64.Type), ("Q3_11", Int64.Type), ("Q4_12", Int64.Type), ("Employee 4", type number), ("Q1_13", type number), ("Q2_14", type number), ("Q3_15", type number), ("Q4_16", Int64.Type))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"Gå tilbage til hovedsiden for Podcast 2316-udfordringen.
Læs den næste artikel i denne serie: Excel MVP'er angriber datarensningsproblemet i strømforespørgsel.
