Notera
Detta är en av en serie artiklar som beskriver lösningar som skickats in för Podcast 2316-utmaningen.
Ett av problemen med min lösning är att den slutliga sekvensen för kategorierna inte nödvändigtvis matchade den ursprungliga sekvensen för kolumnerna. Jag insåg detta i slutet av min video, och eftersom det inte var särskilt viktigt, oroade jag mig inte för det.
Men Josh Johnson skickade in en lösning som hanterade den. När Josh sa att han använde en indexkolumn antog jag att det var som Index och Modulo i Power Query: Number Groups of Records som 1 till 5 upprepade gånger. Men Joshs användning var helt annorlunda.
Obs: Excel MVP John MacDougall använde också den här metoden, men han sammanfogade indexkolumnen till slutet av kategoribeskrivningen. Se Johns video här: https://www.youtube.com/watch?v=Dqmb6SEJDXI och läs mer om hans kod här: Excel MVPs Attack the Data Cleansing Problem in Power Query.
Tidigt i processen, när Josh fortfarande bara hade sex poster, lade han till ett index med början på 1. Josh klickade i formelfältet och döpte om kolumnen Index till Kategori.
Kategorikolumnen var den senaste sista kolumnen. Han använde Move, to Beginning för att flytta det till att vara först:
Efter detta händer många andra steg. De är steg som är innovativa men som hittills mest har behandlats i de andra artiklarna. Efter många sådana steg började jag tro att kategorin 1 till 6 bara var ett misstag. Jag trodde att Josh möjligen skulle radera dem utan att använda dem.
Josh Unpivots, sedan villkorlig kolumn, fyll sedan ner och sedan pivots, lägger till summan. Han verkar aldrig använda den kategorikolumnen. Efter många steg är han här:
Men i de sista stegen sorterar Josh uppgifterna efter anställds namn och sedan kategori!
Vid den här tiden kan han radera kolumnen Kategori. Den sista skillnaden: PTO kommer före projekt A, precis som det hade i de ursprungliga kolumnerna. Det är en fin touch.
Jag kommer också att påpeka att Josh skickade in en video av honom genom dessa steg. Kudos till Josh för att använda kortkommandon inuti Power Query!
Här är Joshs kod:
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å tillbaka till huvudsidan för Podcast 2316-utmaningen.
Läs nästa artikel i denna serie: Excel MVPs Attack the Data Cleansing Problem in Power Query.
