Poznámka
Toto je jeden z řady článků s podrobnými řešeními zaslanými pro výzvu Podcast 2316.
Jedním z problémů mého řešení je, že konečná posloupnost kategorií nemusí nutně odpovídat původní posloupnosti sloupců. Uvědomil jsem si to na samém konci svého videa a protože to nebylo nijak zvlášť důležité, nedělal jsem si s tím starosti.
Josh Johnson však poslal řešení, které to zvládlo. Když Josh řekl, že používá sloupec Index, předpokládal jsem, že to bylo jako Index a Modulo v Power Query: Počet skupin záznamů jako 1 až 5 opakovaně. Ale Joshovo použití bylo úplně jiné.
Poznámka: Tuto metodu použil také Excel MVP John MacDougall, ale zřetězil sloupec indexu na konec popisu kategorie. Podívejte se na Johnovo video zde: https://www.youtube.com/watch?v=Dqmb6SEJDXI a přečtěte si více o jeho kódu zde: Excel MVP Attack the Data Cleansing Problem in Power Query.
Na začátku procesu, kdy měl Josh stále jen šest záznamů, přidal index začínající na 1. Josh klikl do řádku vzorců a přejmenoval sloupec Index na kategorii.
Sloupec Kategorie byl nový poslední sloupec. Použil Move, na začátku, aby se přesunul, aby byl první:
Poté dojde k mnoha dalším krokům. Jsou to kroky, které jsou inovativní, ale dosud byly většinou zahrnuty v ostatních článcích. Po mnoha takových krocích jsem si začal myslet, že čísla kategorie 1 až 6 jsou jen omyl. Myslel jsem, že je možná Josh smaže, aniž by je použil.
Josh Unpivots, pak podmíněný sloupec, pak vyplňte, pak otočte, přidá součet. Zdá se, že ten sloupec Kategorie nikdy nepoužívá. Po mnoha krocích je tady:
Ale v posledních krocích Josh seřadí data podle jména zaměstnance a podle kategorie!
V tomto okamžiku může odstranit sloupec Kategorie. Poslední rozdíl: PTO přichází před projektem A, stejně jako v původních sloupcích. Je to příjemný dotek.
Poukážu také na to, že Josh poslal video, jak prochází těmito kroky. Kudos Joshovi za používání klávesových zkratek uvnitř Power Query!
Tady je Joshův kód:
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"Vraťte se na hlavní stránku výzvy Podcast 2316.
Přečtěte si další článek v této sérii: Excel MVP zaútočí na problém čištění dat v Power Query.
