Merk
Dette er en av en serie artikler som beskriver løsninger som er sendt inn for Podcast 2316-utfordringen.
Et av problemene med løsningen min er at den endelige sekvensen til kategoriene ikke nødvendigvis samsvarte med den opprinnelige sekvensen til kolonnene. Jeg skjønte dette helt på slutten av videoen min, og siden den ikke var spesielt viktig, bekymret jeg meg ikke for det.
Imidlertid sendte Josh Johnson inn en løsning som håndterte den. Da Josh sa at han brukte en indekskolonne, antok jeg at det var som indeksen og modulen i Power Query: Number Groups of Records som 1 til 5 gjentatte ganger. Men Joshs bruk var helt annerledes.
Merk: Excel MVP John MacDougall brukte også denne metoden, men han sammenkoblet indekskolonnen til slutten av kategoribeskrivelsen. Se Johns video her: https://www.youtube.com/watch?v=Dqmb6SEJDXI og les mer om koden hans her: Excel MVPs Attack the Data Cleansing Problem in Power Query.
Tidlig i prosessen, da Josh fremdeles bare hadde seks poster, la han til en indeks som begynte med 1. Josh klikket i formellinjen og omdøpte Indeks-kolonnen til Kategori.
Kategorikolonnen var den siste siste kolonnen. Han brukte Move, to Beginning for å flytte den til å være først:
Etter dette skjer det mange andre trinn. De er trinn som er innovative, men som for det meste er dekket i de andre artiklene. Etter mange slike trinn begynte jeg å tro at kategorien 1 til 6 bare var en feil. Jeg trodde at muligens Josh skulle slette dem uten å bruke dem.
Josh Unpivots, deretter betinget kolonne, deretter fyll ned, og deretter pivoter, legger til summen. Han ser aldri ut til å bruke den kategorikolonnen. Etter mange trinn er han her:
Men i de siste trinnene sorterer Josh dataene etter ansattes navn og deretter kategori!
På dette tidspunktet kan han slette kategorikolonnen. Den siste forskjellen: PTO kommer før prosjekt A, akkurat som det hadde i de originale kolonnene. Det er en fin touch.
Jeg vil også påpeke at Josh sendte inn en video av ham som gikk gjennom disse trinnene. Kudos til Josh for å bruke hurtigtaster inne 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å tilbake til hovedsiden for Podcast 2316-utfordringen.
Les neste artikkel i denne serien: Excel MVPs Attack the Data Cleansing Problem in Power Query.
