Bilješka
Ovo je jedan od niza članaka koji detaljno opisuju rješenja poslana za izazov Podcast 2316.
U svom izvornom problemu oblikovanja podataka naišao sam na problem vrlo rano u procesu. Dolazni podaci imat će mnogo stupaca s naslovom Q1.
U svom rješenju stvorio sam imenovani raspon "UglyData" i uvezao ga u Power Query. To je dovelo do nesretnog rezultata Power Queryja koji je moje stupce preimenovao u Q1_1.
Kasnije, nakon opozivanja, morao sam iz tih zaglavlja izvući samo lijeva dva znaka.
Za ovaj su problem postojala tri zasebna rješenja:
- Wyn Hopkins i Demote Headers
- MF Wong i poništite potvrdni okvir My Table Has Headers (također predložio Peter Bartholomew)
- Jason M i jednostavno izbrišite Promovirana zaglavlja (također su predložili Ondřej Malinský i Excel MVP John MacDougall)
Prva inovacija bila je Wyn Hopkins iz tvrtke Access Analytic. Umjesto imenovanog raspona, Wyn je podatke pretvorio u tablicu pomoću Ctrl + T. U ovom je trenutku nanesena šteta naslovima, jer je Excel pretvorio naslove u:
Jednom kada je Wyn podatke preuzeo u Power Query, otvorio je padajući izbornik Koristi prvi redak kao zaglavlja i izabrao Koristi zaglavlja kao prvi red. Nikad nisam shvatio da je ovo tamo. Stvara korak pod nazivom Table.DemoteHeaders.
Ali, čak i s Wynovim poboljšanjem, on će ipak kasnije morati izdvojiti prva 2 znaka iz tih zaglavlja.
Druga inovacija je tehnika MF Wonga. Kad je kreirao tablicu, poništio je potvrdu Moje tablice ima zaglavlja!
To osigurava da Excel višestruka zaglavlja Q1 ostavi na miru i da kasnije nije potrebno ekstrahirati dodatni sufiks.
Razumijem da u kampu "Volim stolove" ima ljudi. Video MF Wonga pokazao je kako može dodati nove zaposlenike s desne strane podataka, a tablica se automatski širi. Puno je dobrih razloga za upotrebu tablica.
No, budući da volim međuzbrojeve, prilagođene poglede i filtriram po odabiru, obično ne koristim tablice. Dakle, cijenim rješenje Jason M.-a. Podatke je čuvao kao imenovani raspon UglyData. Čim je uvozio podatke u Power Query, izbrisao je ova dva koraka:
Sad, s podacima jednostavno u 1. retku, nema gnjavaže s mnogo stupaca koji se nazivaju Q1.
Evo koda Wyna Hopkina koji prikazuje DemotedHeaders:
let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Demoted Headers1" = Table.DemoteHeaders(Source), #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"), #"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each if Text.Start((Column1),1) = "Q" then null else (Column1)), #"Filled Down" = Table.FillDown(#"Added Custom",("Custom")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Custom) "Dept. Total")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith((Column1), "Employee")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", (PromoteAllScalars=true)), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", (("Category Description", each Text.Start(_, 2), type text))), #"Reordered Columns" = Table.ReorderColumns(#"Extracted First Characters",("Category Description_1", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category Description_1", "Category Description"), "Attribute", "Value"), #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",("Category Description_1", "Attribute", "Category Description", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"(#"Category Description")), "Category Description", "Value", List.Sum), #"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",("Attribute", "Category Description_1", "Q1", "Q2", "Q3", "Q4")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",(("Attribute", "Cat Deasc"), ("Category Description_1", "Emp Name"))), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",(("Emp Name", type text), ("Q1", Int64.Type), ("Q2", Int64.Type), ("Q3", Int64.Type), ("Q4", Int64.Type))), #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), Int64.Type) in #"Inserted Sum"Povratak na glavnu stranicu za izazov Podcast 2316.
Pročitajte sljedeći članak iz ove serije: Power Query: Izbrišite ovo, izbrišite one ili ništa ne izbrišite ?.
