Uwaga
Jest to jeden z serii artykułów opisujących szczegółowo rozwiązania przesłane w ramach wyzwania Podcast 2316.
Jednym z problemów z moim rozwiązaniem jest to, że końcowa sekwencja kategorii niekoniecznie odpowiadała oryginalnej kolejności kolumn. Zrozumiałem to na samym końcu mojego filmu, a ponieważ nie było to szczególnie ważne, nie martwiłem się tym.
Jednak Josh Johnson przysłał rozwiązanie, które sobie z tym poradziło. Kiedy Josh powiedział, że użył kolumny indeksu, założyłem, że jest ona podobna do indeksu i modulo w dodatku Power Query: liczba grup rekordów wielokrotnie od 1 do 5. Ale zastosowanie Josha było zupełnie inne.
Uwaga: MVP programu Excel, John MacDougall, również użył tej metody, ale połączył kolumnę indeksu na końcu opisu kategorii. Zobacz wideo Johna tutaj: https://www.youtube.com/watch?v=Dqmb6SEJDXI i przeczytaj więcej o jego kodzie tutaj: MVP programu Excel atakują problem oczyszczania danych w dodatku Power Query.
Na początku procesu, kiedy Josh miał jeszcze tylko sześć rekordów, dodał indeks zaczynający się od 1. Josh kliknął pasek formuły i zmienił nazwę kolumny Indeks na Kategoria.
Kolumna Kategoria była nową, ostatnią kolumną. Użył Move, to Beginning, aby przesunąć go na pierwszy:
Po tym następuje wiele innych kroków. Są to kroki, które są innowacyjne, ale jak dotąd w większości zostały omówione w innych artykułach. Po wielu takich krokach zacząłem myśleć, że kategorie od 1 do 6 to tylko pomyłka. Myślałem, że być może Josh je usunie bez ich używania.
Josh Unpivots, następnie kolumna warunkowa, a następnie wypełnij, a następnie obraca, dodaje sumę. Wydaje się, że nigdy nie używa tej kolumny kategorii. Po wielu krokach jest tutaj:
Ale na końcu Josh sortuje dane według nazwiska pracownika, a następnie kategorii!
W tym momencie może usunąć kolumnę Kategoria. Ostatnia różnica: PTO występuje przed projektem A, tak jak w oryginalnych kolumnach. To miły akcent.
Zaznaczę również, że Josh przesłał mu film, na którym przechodzi przez te kroki. Wyrazy uznania dla Josha za używanie skrótów klawiaturowych w dodatku Power Query!
Oto kod Josha:
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"Wróć do strony głównej wyzwania Podcast 2316.
Przeczytaj następny artykuł z tej serii: MVP programu Excel atakują problem oczyszczania danych w dodatku Power Query.
