Opmerking
Dit is een van een reeks artikelen over oplossingen die zijn ingezonden voor de Podcast 2316-uitdaging.
Een van de problemen met mijn oplossing is dat de uiteindelijke volgorde van de categorieën niet noodzakelijk overeenkwam met de oorspronkelijke volgorde van de kolommen. Ik realiseerde me dit helemaal aan het einde van mijn video, en aangezien het niet bijzonder belangrijk was, maakte ik me er geen zorgen over.
Josh Johnson stuurde echter een oplossing die het afhandelde. Toen Josh zei dat hij een Index-kolom gebruikte, nam ik aan dat het herhaaldelijk leek op de Index en Modulo in Power Query: Number Groups of Records als 1 tot en met 5. Maar het gebruik van Josh was compleet anders.
Opmerking: Excel MVP John MacDougall gebruikte deze methode ook, maar hij voegde de indexkolom samen aan het einde van de categoriebeschrijving. Bekijk de video van John hier: https://www.youtube.com/watch?v=Dqmb6SEJDXI en lees hier meer over zijn code: Excel MVP's vallen het probleem van het opschonen van gegevens aan in Power Query.
In het begin van het proces, toen Josh nog maar zes records had, voegde hij een index toe die begon bij 1. Josh klikte in de formulebalk en hernoemde de Index-kolom naar Categorie.
De Categorie-kolom was de nieuwe laatste kolom. Hij gebruikte Move, to Beginning om het als eerste te verplaatsen:
Hierna volgen nog veel andere stappen. Het zijn stappen die innovatief zijn, maar die tot nu toe grotendeels in de andere artikelen zijn behandeld. Na veel van dergelijke stappen begon ik te denken dat de categorienummers 1 tot en met 6 gewoon een vergissing waren. Ik dacht dat Josh ze mogelijk zou verwijderen zonder ze te gebruiken.
Josh Unpivots, dan voorwaardelijke kolom, dan invullen, dan draait, telt het totaal op. Hij lijkt die categoriekolom nooit te gebruiken. Na vele stappen is hij hier:
Maar dan in de laatste stappen, sorteert Josh de gegevens op Werknemersnaam en vervolgens op Categorie!
Op dit punt kan hij de kolom Categorie verwijderen. Het laatste verschil: PTO komt voor Project A, net als in de originele kolommen. Het is een leuke touch.
Ik zal er ook op wijzen dat Josh een video heeft ingezonden waarin hij deze stappen doorloopt. Een pluim voor Josh voor het gebruik van sneltoetsen in Power Query!
Hier is de code van Josh:
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"Keer terug naar de hoofdpagina voor de Podcast 2316-uitdaging.
Lees het volgende artikel in deze serie: Excel MVP's vallen het probleem van het opschonen van gegevens aan in Power Query.
