Amikor élő Power Excel szemináriumot tartok, felajánlom, hogy ha valakinek valaha is furcsa Excel problémája van, elküldheti nekem segítségért. Így kaptam ezt az adattisztítási problémát. Valakinek volt egy összefoglaló munkalapja, amely így néz ki:
Az adatokat át akarták formázni, hogy így nézzen ki:
Egy érdekes nyom erről az adatról: A G4-ben lévő 18 a H4: K4 részösszegének tűnik. Csábító eltávolítani a G, L és így tovább oszlopokat, de először ki kell vonni a munkavállaló nevét a G3, L3 stb.
Február 9-én, vasárnap 4 óra volt, amikor bekapcsoltam a videomagnót, és néhány nehézkes lépést felvettem a Power Query-ben a probléma megoldására. Tekintettel arra, hogy vasárnap volt, egy olyan nap, amikor nem szoktam videókat csinálni, arra kértem az embereket, hogy küldjék el ötleteiket a probléma megoldásáról. 29 megoldást küldtek be.
Mindegyik megoldás újszerű fejlesztést kínál a folyamatomhoz képest. Az a tervem, hogy elindítok egy olyan cikksort, amely bemutatja a módszerem különböző fejlesztéseit.
Videót nézni
Mielőtt elkezdeném ezt a folyamatot, meghívlak, hogy nézze meg a megoldásomat:
És a Power Query általam generált M-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))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",(("Attribute.1", "TextValue"))), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length((TextValue))), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if (Length)> 2 then (TextValue) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Name")), #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",("Category Description", "Name", "TextValue", "Value", "Length")), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ((TextValue) = "Q1" or (TextValue) = "Q2" or (TextValue) = "Q3" or (TextValue) = "Q4")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each (Name) "Dept. Total"), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Length")), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"(TextValue)), "TextValue", "Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Name", Order.Ascending))), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each (Q1)+(Q2)+(Q3)+(Q4)) in #"Added Custom1"Mielőtt belekezdenénk a megoldásokba, foglalkozzunk sok közös megjegyzéssel:
- Néhányan azt mondtátok, hogy visszalépne, hogy kiderítse, miért jelennek meg az adatok ebben a formátumban. Nagyra értékelem ezeket a megjegyzéseket. Mindenki, aki ezt mondta, jobb ember, mint én. Az évek során megtanultam, hogy amikor azt kérdezi, hogy "miért?" a válasz általában ezt a volt alkalmazottat érinti, aki 17 évvel ezelőtt indult ezen az úton, és mindenki így használja, mivel most mindannyian megszoktuk.
- Továbbá - sokan közületek - azt mondták, hogy a végső megoldásnak egy magas függőleges asztalnak kell lennie, majd egy forgatótáblát használjon a végső eredmények előállításához. Jonathan Cooper ezt foglalta össze a legjobban: "Egyetértek a YouTube néhány más megjegyzésével is, miszerint egy megfelelő adathalmazon nem lenne" Totals ", és a végén nem kellene őket elforgatni. De ha a felhasználó valóban sima képet szeretne régi asztal, akkor megadod nekik, amit akarnak. " Ennek tulajdonképpen mindkét oldalát látom. Szeretem a forgóasztalt, és az egyetlen dolog, ami szórakoztatóbb, mint a Power Query, az a Power Query, amelynek tetején egy szép forgóasztal található. De ha az egészet meg tudjuk csinálni a Power Query-ben, akkor még egyet meg kell szakítanunk.
Itt találhatók hiperhivatkozások a különféle technikákra
-
Teljesítmény lekérdezési technikák
- A rekordok csoportjának számozása
- Bal két karakter kibontása
- Teljes oszlop
- Egyébként, ha záradékok
- Több azonos fejléc a Power Query-ben
- Mit kell törölni
- Felosztva Q-val
- Sorok rendezése
- Power Query megoldások az Excel MVP-ktől
-
Haladás a Power Query interfészen túl
- Táblázat. Hasított
- Szysz Bill világa
-
Formula Solutions
- Egy dinamikus tömbképlet
- Old School Helper oszlopok
- Formula Solutions
-
A fenti ötletek és a végső videó összesített ötlete
- A legjobb ötletek összessége
