Billin "Kuinka puhdistat nämä tiedot" -haaste - Excel-vinkit

Sisällysluettelo

Kun teen suoran Power Excel -seminaarin, tarjoan, että jos joku huoneessa on joskus outo Excel-ongelma, hän voi lähettää sen minulle avuksi. Siten sain tämän tiedonpuhdistusongelman. Jolla oli yhteenvetotaulukko, joka näyttää tältä:

Yhteenvetotaulukko

He halusivat alustaa tiedot uudelleen näyttäen tältä:

Halutut alustetut tiedot

Yksi mielenkiintoinen vihje näistä tiedoista: 18 G4: ssä näyttää olevan H4: K4: n välisumma. On houkuttelevaa poistaa sarakkeet G, L ja niin edelleen, mutta ensin sinun on purettava työntekijän nimi G3: sta, L3: sta ja niin edelleen.

Se oli kello 4 sunnuntaina 9. helmikuuta, kun käynnistäin videonauhurin ja nauhoitin joitain kömpelöitä vaiheita Power Queryyn ongelman ratkaisemiseksi. Koska oli sunnuntai, päivä, jolloin en yleensä tee videoita, pyysin ihmisiä lähettämään ideoitaan ongelman ratkaisemiseksi. 29 ratkaisua on lähetetty.

Jokainen ratkaisu tarjoaa hienoja uusia parannuksia prosesseihini. Suunnitteluni on aloittaa sarja artikkeleita, jotka esittävät menetelmäni parannuksia.

Katso video

Ennen kuin aloitan prosessin, kutsun sinut tutustumaan ratkaisuuni:

Ja M-koodi, jonka Power Query loi minulle:

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"

Ennen kuin aloitamme ratkaisuihin tutustumisen, tarkastellaan monia yleisiä kommentteja:

  • Jotkut teistä sanoivat menevänne taaksepäin selvittääkseen, miksi tiedot näkyvät tässä muodossa aluksi. Arvostan näitä kommentteja. Jokainen, joka sanoi tämän, on parempi ihminen kuin minä. Olen vuosien varrella oppinut, että kun kysyt "Miksi?" vastaus koskee yleensä tätä entistä työntekijää, joka aloitti tien 17 vuotta sitten, ja kaikki käyttävät sitä edelleen tällä tavalla, koska olemme kaikki tottuneet siihen nyt.
  • Lisäksi - monet teistä - sanoivat lopullisen ratkaisun olevan korkea pystysuora pöytä ja käyttäkää sitten pivot-taulukkoa lopullisten tulosten tuottamiseen. Jonathan Cooper tiivisti tämän parhaiten: "Olen samaa mieltä myös joidenkin muiden YouTube-kommenttien kanssa siitä, että oikeassa tietojoukossa ei olisi" Totals "eikä sitä tarvitsisi kääntää lopussa. Mutta jos käyttäjä todella haluaa tavallista vanha pöytä sitten annat heille mitä he haluavat. " Näen itse asiassa tämän molemmat puolet. Rakastan kääntöpöytää ja ainoa hauskempaa asia kuin Power Query on Power Query, jonka päällä on mukava kääntöpöytä. Mutta jos voimme tehdä kaiken Power Queryssä, niin yksi vähemmän asia rikkoa.

Tässä on hyperlinkkejä erilaisiin tekniikoihin

  • Tehokyselytekniikat

    • Tietoryhmien numerointi
    • Poimitaan kaksi vasenta merkkiä
    • Sarake yhteensä
    • Muut, jos lausekkeet
    • Useita identtisiä otsikoita virtakyselyssä
    • Mitä poistaa
    • Jaettu Q: lla
    • Rivikohtien lajittelu
    • Power Query -ratkaisut Excel MVP: ltä

  • Siirtyminen Power Query -rajapinnan ulkopuolelle

    • Taulukko
    • Bill Szyszin maailma

  • Formula Solutions

    • Yksi dynaaminen matriisikaava
    • Vanhan koulun auttajasarakkeet
    • Formula Solutions

  • Yhdistelmä kaikista yllä olevista ideoista ja lopullinen video

    • Yhdistelmä kaikkien parhaista ideoista

Mielenkiintoisia artikkeleita...