Mittataulukko tekstitiedostossa: Power Query -ratkaisu

Tässä blogiviestissä käyn läpi ongelman 2 meneillään olevasta Viikon ongelmasta osoitteessa , jossa keskustelimme sotkuisten tietojen lajittelemisesta puhtaaksi mittataulukoksi. Tämä on toinen iteraatio uudesta aloitteesta, jota isännöimme LuckyTemplatesissa. Olen erityisen intohimoinen tähän sarjaan, koska se tarjoaa kaikille mahdollisuuden harjoitella säännöllisesti. Voit katsoa tämän opetusohjelman koko videon tämän blogin alaosasta.

Jokaisen kuukauden ensimmäisenä keskiviikkona on DAX-haaste ja kolmantena keskiviikkona power query -haaste .

Tämä on loistava tilaisuus tutkia, löytää ja oppia uusia asioita näistä kielistä, joita sinun on hyödynnettävä LuckyTemplatesissa.

Löydät viikon ongelman kategorian LuckyTemplates-foorumilta.

Pääpaino on prosessissa eik�� niinkään tuloksessa.

Jos onnistut hajottamaan ongelman pienempiin osiin, pystyt ratkaisemaan kaikki kohtaamasi ongelmat.

Melissa de Korte

Brian puhui aiemmin tekniikasta, jota kutsutaan kumi ankan virheenkorjaukseksi . Jos unohdat sen, muista katsoa hänen videonsa. Se voi auttaa sinua, kun jäät jumissa näihin haasteisiin.

Ensin tarkastellaan käsillä olevaa tehtävää. Meillä on sotkuinen tekstitiedosto, joka meidän on muutettava oikeaksi mittataulukoksi.

Nyt kun katson tällaisia ​​tietoja, suurin osa kyse on tekstin puhdistamisesta, ei-toivottujen merkkien poistamisesta, leikkaamisesta, sanojen isoista kirjaimista ja niin edelleen.

Mutta meidän on myös noudettava kaikki rivit tästä pinotiedosta ja muutettava se yhdeksi riviksi kullekin maalle. Kutsun sitä pinotiedoksi, koska kaikkien kenttien nimet toistetaan yhdessä sarakkeessa kunkin maan osalta.

Minulla on kokemusta Excelistä, ja yksi sen tehokkaimmista ominaisuuksista mielestäni on.

Sisällysluettelo

• Pivot-taulukot Excelissä
• Power Queryn käyttäminen ulottuvuustaulukon luomiseen
• Mukautettu tekstinpuhdistustoiminto
• Datan kääntäminen
• Johtopäätös

Pivot-taulukot Excelissä

Pivot-taulukoiden avulla voin tarkastella tietoja segmenteiltä. Riippuen siitä, mitä sijoitat riviosaan, pivot-taulukko tiivistää kunkin segmentin esiintymisen yhdeksi riviksi.

Voit sitten jakaa sen edelleen vetämällä kenttiä sarakeosaan.

Olen luonut esimerkin siitä, miltä tämä näyttää. Täällä meillä on melkein samat tiedot kuin tekstitiedostossa.

Ja seuraavalle sivulle olen luonut tyhjän pivot-taulukon.

Nyt en näyttänyt sinulle, että minulla on täällä kolme saraketta niiden kahden sijaan, jotka olen jo näyttänyt sinulle.

Vedän segmenttini riveille.

Koska sarakkeessa 1 oli kenttien nimet, aion vetää ne sarakeosaan.

Sarakkeessa 2 oli kaikki kentän arvot, joten vedän sen arvo-osioon.

Näemme tässä, että pivot-taulukot eivät pysty käsittelemään tekstimerkkijonoja. Se laskee ne, mutta se osoittaa, että meillä on yksi arvo jokaiselle kenttään.

Katsotaanpa nyt luomaani segmenttiä. Joten aion palata tietoihin ja paljastaa sarakkeeni. Voit nähdä, että se on vain indeksinumero, joka tunnistaa jokaisen erillisen datalohkon, joka on edelleen pinottu päällekkäin.

Joten minulle avainmuunnos on tietojen kääntöpiste, jotta se saadaan takaisin tuohon taulukkomuotoon.

Olen foorumilla nähnyt muita tapoja käsitellä tätä. On muitakin tapoja päästä taulukkomuotoon pivot-toiminnon lisäksi, ja ne toimivat yhtä hyvin. Jos olet kiinnostunut siitä, mene foorumille ja aloita tutkiminen.

Power Queryn käyttäminen ulottuvuustaulukon luomiseen

Käydään tehokyselyn läpi ja tarkastellaan ratkaisuani.

Henkilökohtaisesti uskon, että käyttöliittymä tekee hienoa työtä kirjoittaessaan suurimman osan M-koodista meille. Joten minulla on tapana suunnitella kyselyni käyttämällä käyttöliittymää niin paljon kuin pystyn.

Kun kysely tekee sen, mitä tarvitsin, siirryn edistyneeseen editoriin ja tutkin M-koodia nähdäkseni, voinko muokata sitä. Katsotaan miltä se näyttää.

Tämä on perusryhmäni, joka on rakennettu käyttöliittymän avulla.

Näet, että oikean puolen sovelletuissa vaiheissa on paljon vaiheita.

Se ei sinänsä ole ongelma, mutta vain katsomalla näitä vaiheita voit nähdä, että on olemassa monia muunnoksia, jotka voidaan ryhmitellä yhteen.

Avataan edistynyt editori.

Näemme, että tässä kyselyssä on 31 vaihetta.

Lisäsin myös kommentteja tähän samaan 31 vaihetta sisältävään kyselyyn, mutta jaoin sen osiin.

Ensimmäinen asia, jonka tein, oli poistaa muutostyypin vaihe. Suosittelen luomaan mukautetun funktion kaikkien näiden tekstin muunnosvaiheiden suorittamiseksi.

Täällä on paljon kommentteja, mutta tässä blogikirjoituksessa haluan korostaa vain kahta asiaa. Ensimmäinen on mukautettu toiminto tekstin puhdistustarkoituksiin .

Toinen on pivot-vaihe tämäntyyppisten tietojen muuttamiseksi takaisin oikeaan taulukkomuotoon .

Mukautettu tekstinpuhdistustoiminto

Palataanpa kyselyni luomisen alkuvaiheisiin, joissa minulla oli kaikki nuo ryhmitellyt vaiheet näiden tekstien puhdistamiseksi: sarake 1 ja yhdistetty sarake.

Lisäsin myös ylimääräisen mukautetun sarakkeen. Sen ainoa tarkoitus on rakentaa mukautettu tekstinpuhdistustoimintoni . Vetoin siihen yhdistetyssä sarakkeessa.

Tällä tavalla minun ei tarvitse kirjoittaa funktiota yhdellä kertaa, vaan rakentaa sitä asteittain vaihe kerrallaan ja lisää uusi muunnos edellisen vaiheen tulosten tarkastelun jälkeen.

Katsotaanpa tekstinpuhdistustoiminnon M-koodia.

Kuten näet, minulla on useita vaiheita. Kun rakensin tätä tekstifunktiota, kuljin edestakaisin kyselyn välillä katsoakseni tuloksia nähdäkseni mitä rakentaa ja mitä korjata seuraavaksi.

Tällä tuloksella tein kaikki tarvittavat muutokset. Jotkut tässä käyttämistäni M-funktioista saatiin käyttöliittymästä, kun rakensin alkuperäistä kyselyäni, kuten teksti. Trimmaustoiminto. Muut käytetyt toiminnot eivät kuitenkaan olleet.

Jos et tunne niitä, voit etsiä ne kaikkiM-kaavaoppaassa verkossa. Tämä on linkki , johon sinun täytyy mennä.

Löydät tehokyselyille ja toiminnoille omistetun osion.

Jos vierität alaspäin, löydät tekstitoimintoa käsittelevän osion ja jokainen osio alkaa yleiskatsauksella. Siellä on luettelo kaikista tehokyselyn ja kaavan M-kielen tekstifunktioista.

Jos etsit tiettyä muutosta, voit etsiä sen täältä.

Datan kääntäminen

Toinen osa, jonka halusin korostaa tätä dimensiotaulukkoa luodessaan, on itse tietojen kääntyminen. Katsotaanpa tätäkin tarkemmin.

Aloitin lisäämällä indeksin. Päivitin indeksin segmentoidakseni tietolohkot oikein. Tein tämän palauttamalla indeksinumeron jokaiselle riville, jonka sarakkeessa 1 oli tekstimaa, ja sitten täytin arvon alas.

Meidän tarvitsee vain kääntää tiedot itse. Muunnos-välilehdeltä löydät Pivot-sarakkeen. Kun sarake 1 on valittuna, napsauta Pivot Column -kohtaa.

Se käyttää sarakkeen 1 arvoja uuden sarakkeen nimenä. Se haluaa myös tietää, missä näiden kenttien nimien arvot ovat. Ne ovat yhdistetyssä sarakkeessamme.

Jos Excel pystyy käsittelemään tekstiarvoja, Power Query pystyy myös lisäasetusasetuksensa vuoksi. Meidän tarvitsee vain valita Älä yhdistä, jotta se pystyy käsittelemään tekstiarvoja.

Kun napsautamme OK, voimme nähdä, että tietomme on kierretty.

Johtopäätös

Yllä oleva kuva on lopputuloskysely. Toivon, että olet nauttinut siitä, kuinka olemme muokanneet toimitetun tekstitiedoston sotkuiset tiedot ja muuttaneet ne analysointiin sopivaksi puhtaaksi.

Jos pidit tästä blogikirjoituksesta, tilaa LuckyTemplates-kanava, jotta et jää paitsi uudesta sisällöstä.

Toivon näkeväni teidät kaikki tulevissa Viikon Ongelma -haasteissa.

Melissa