Huomautus
Tämä on yksi artikkelisarjasta, joka sisältää yksityiskohtaiset ratkaisut, jotka lähetettiin Podcast 2316 -haasteeseen.
Alkuperäisessä tiedonmuokkausongelmassani törmäsin ongelmaan hyvin varhaisessa vaiheessa. Saapuvassa datassa oli paljon sarakkeita, joiden otsikko oli Q1.
Ratkaisumassani loin nimellisen alueen "UglyData" ja toin sen Power Queryyn. Tämä johti siihen, että Power Query uudestaan nimitti sarakkeeni Q1_1: ksi.
Myöhemmin, kääntämisen jälkeen, minun täytyi poimia vain kaksi vasenta merkkiä noista otsikoista.
Tähän ongelmaan oli kolme erillistä ratkaisua:
- Wyn Hopkins ja alennetut otsikot
- MF Wong ja poista valinta My Table Has Headers -valinnasta (ehdotti myös Peter Bartholomew)
- Jason M ja poista yksinkertaisesti ylennetyt otsikot (myös Ondřej Malinský ja Excel MVP John MacDougall)
Ensimmäinen innovaatio oli Wyn Hopkinsilta Access Analyticiltä. Nimetyn alueen sijasta Wyn muunsi tiedot taulukkoon näppäimillä Ctrl + T. Tässä vaiheessa otsikoiden vahingoittuminen tapahtui, kun Excel muunsi otsikot seuraaviksi:
Kun Wyn vei tiedot Power Queryyn, hän avasi avattavan Käytä ensimmäistä riviä otsikoina -vaihtoehdon ja valitsi Käytä otsikoita ensimmäisenä rivinä. En koskaan tajunnut, että tämä oli siellä. Se luo vaiheen nimeltä Table.DemoteHeaders.
Mutta vaikka Wyn olisi parantunut, hänen täytyisi kuitenkin myöhemmin poimia kaksi ensimmäistä merkkiä noista otsikoista.
Toinen innovaatio on MF Wongin tekniikka. Kun hän loi taulukon, hän ei tarkista, että taulukossa on otsikot!
Tämä varmistaa, että Excel jättää useita Q1-otsikoita yksin eikä ylimääräistä jälkiliitettä tarvitse purkaa myöhemmin.
Ymmärrän, että “rakastan pöytiä” -leirillä on ihmisiä. MF Wongin video osoitti, kuinka hän voisi lisätä uusia työntekijöitä tietojen oikealle puolelle, ja taulukko laajenee automaattisesti. Taulukoiden käyttöön on paljon hyviä syitä.
Mutta koska rakastan välisummia, mukautettuja näkymiä ja suodatusta valinnan mukaan, en yleensä käytä taulukoita. Joten arvostan Jason M: n ratkaisua. Hän piti tiedot UglyDatan nimettynä alueena. Heti kun hän toi tiedot Power Queryyn, hän poisti nämä kaksi vaihetta:
Nyt kun tiedot ovat yksinkertaisesti rivillä 1, ei ole vaivaa, että monilla sarakkeilla, joita kutsutaan Q1: ksi.
Tässä on Wyn Hopkinin koodi, joka näyttää 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"Palaa Podcast 2316 -haasteen pääsivulle.
Lue tämän sarjan seuraava artikkeli: Power Query: Poista tämä, Poista ne tai poista mitään ?.
