jegyzet
Ez egy olyan cikksorozat, amely a Podcast 2316 kihíváshoz elküldött megoldásokat részletezi.
A megoldásom egyik problémája az, hogy a kategóriák végső sorrendje nem feltétlenül egyezett meg az oszlopok eredeti sorrendjével. A videóm legvégén rájöttem erre, és mivel ez nem volt különösebben fontos, nem aggódtam miatta.
Josh Johnson azonban megoldást küldött, amely kezelte. Amikor Josh azt mondta, hogy Index oszlopot használ, feltételeztem, hogy ez olyan, mint az Index és a Modulo a Power Query: Rekordcsoportok száma 1-től 5-ig. De Josh használata teljesen más volt.
Megjegyzés: Az Excel MVP John MacDougall is ezt a módszert használta, de összefűzte az index oszlopot a kategória leírás végéig. Lásd John videóját itt: https://www.youtube.com/watch?v=Dqmb6SEJDXI, és itt olvashat bővebben a kódjáról: Excel MVP-k támadják meg az Power Query adattisztítási problémáját.
A folyamat elején, amikor Joshnak még csak hat rekordja volt, hozzáadta az 1-től kezdődő indexet. Josh kattintott a képletsávon, és az Index oszlopot Kategóriának nevezte át.
A Kategória oszlop volt az új utolsó oszlop. Ő használta a Move-ot a kezdetig, hogy az első legyen:
Ezek után sok más lépés történik. Olyan lépésekről van szó, amelyek innovatívak, de leginkább a többi cikkben foglalkoztak eddig. Sok ilyen lépés után kezdtem azt gondolni, hogy az 1–6. Kategóriák csak hiba voltak. Azt hittem, hogy Josh valószínűleg felhasználás nélkül törölni fogja őket.
Josh Unpivots, majd feltételes oszlop, majd töltse ki, majd elfordulva összeadja az összeget. Úgy tűnik, soha nem használja azt a Kategória oszlopot. Sok lépés után itt van:
De aztán az utolsó lépésben Josh az adatokat Munkavállaló neve, majd Kategória szerint rendezi!
Ekkor törölheti a Kategória oszlopot. A végső különbség: A PTO az A projekt elé kerül, akárcsak az eredeti oszlopokban. Ez egy szép érintés.
Arra is felhívom a figyelmet, hogy Josh beküldött egy videót arról, ahogy végigcsinálja ezeket a lépéseket. Dicséret Josh-nak a Power Query belsejében található billentyűparancsok használatáért!
Itt van Josh kódja:
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"Térjen vissza a Podcast 2316 kihívás főoldalára.
Olvassa el a sorozat következő cikkét: Az Excel MVP-k megtámadják az adattisztítási problémát a Power Query alkalmazásban.
