Kada uživo izvodim seminar o programu Power Excel, nudim da ako netko u sobi ikad ima neobičan Excel problem, može mi ga poslati za pomoć. Tako sam dobio ovaj problem čišćenja podataka. Netko je imao sažeti radni list koji izgleda ovako:
Željeli su preformatirati podatke kako bi izgledali ovako:
Jedan zanimljiv trag o tim podacima: Čini se da je 18 u G4 podzbroj H4: K4. Primamljivo je ukloniti stupce G, L i tako dalje, ali prvo morate izvući ime zaposlenika iz G3, L3 i tako dalje.
U nedjelju 9. veljače bilo je 4 ujutro kad sam uključio video rekorder i snimio neke nezgrapne korake u Power Queryu kako bih riješio problem. S obzirom na to da je bila nedjelja, dan u kojem inače ne radim videozapise, zamolio sam ljude da pošalju svoje ideje kako riješiti problem. Poslano je 29 rješenja.
Svako rješenje nudi neko novo, novo poboljšanje u odnosu na moj postupak. Moj je plan započeti seriju članaka koji pokazuju razna poboljšanja moje metode.
Gledaj video
Prije nego započnem taj postupak, pozivam vas da vidite moje rješenje:
I M-kod koji mi je generirao Power Query:
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"
Prije nego što počnemo ulaziti u rješenja, obratimo se mnogim uobičajenim komentarima:
- Neki od vas rekli su da će se vratiti unatrag kako bi shvatili zašto se podaci prikazuju u ovom formatu za početak. Cijenim ove komentare. Svi koji su rekli da je ovo bolja osoba od mene. Tijekom godina naučio sam da kad pitate "Zašto?" odgovor obično uključuje ovog bivšeg zaposlenika koji je krenuo tim putem prije 17 godina i svi ga nastavljaju koristiti na ovaj način jer smo svi na to navikli.
- Također - mnogi od vas - rekli su da bi konačno rješenje trebalo biti visoka okomita tablica, a zatim upotrijebite zaokretnu tablicu za dobivanje konačnih rezultata. Jonathan Cooper ovo je najbolje sažeo: "Također se slažem s nekim drugim komentarima na YouTubeu da odgovarajući skup podataka ne bi imao" Ukupno "i da ga ne bi trebalo usmjeriti na kraju. Ali ako korisnik stvarno želi običan stari stol, a vi im dajte ono što žele. " Zapravo vidim obje strane ovoga. Volim zaokretnu tablicu i jedina stvar koja je zabavnija od Power Queryja je Power Query s lijepom zaokretnom tablicom na vrhu. Ali ako u Power Queryu uspijemo napraviti cijelu stvar, onda ćemo razbiti još jednu stvar.
Ovdje su hiperveze do različitih tehnika
-
Power Query tehnike
- Grupe numeriranja zapisa
- Izdvajanje lijevih dva znaka
- Ukupni stupac
- Inače ako klauzule
- Više identičnih zaglavlja u Power Queryju
- Što izbrisati
- Podijeljeno Q
- Sortiranje stavki retka
- Power Query rješenja iz Excel MVP-ova
-
Prelazak izvan Power Query sučelja
- Stol.Podijeljen
- Svijet Billa Szysza
-
Rješenja formule
- Jedna dinamička formula niza
- Stupci pomoćnika stare škole
- Rješenja formule
-
Kompozicija svih ideja odozgo i završni video
- Kompozicija najboljih ideja iz svih