Power Queryn kielivirran parhaat käytännöt

Tässä opetusohjelmassa opit Power Query Language Flowsta ja joistakin sen parhaista käytännöistä. Opit myös muuntamaan tietojasi helposti parhaan mahdollisen tuloksen saavuttamiseksi. Tietojen muunnokset optimoivat raporttisi ja saavat sen näyttämään houkuttelevalta.

Sisällysluettelo

• Aseta tietotyypit ilman Power Query -kielikulkua
  • Aseta tietotyypit käyttämällä Muunna-välilehteä
  • Aseta tietotyypit sarakeotsikon avulla
  • Aseta tietotyypit sarakekuvakkeiden avulla
• Aseta tietotyypit Power Queryn kielivirtauksella
  • Poista tarpeettomat sarakkeet
  • Määritä sopivat tietotyypit sarakkeisiin ja rajoita rivejä
  • Yhdistä Power Queryn kielivirtauksen ylimääräiset vaiheet
• Järjestä kyselyitä tehokkaamman kyselyn kielikulun saamiseksi
• Yhteenveto Power Queryn kielikulusta
• Johtopäätös

Aseta tietotyypit ilman Power Query -kielikulkua

Vähimmäismuunnokset, jotka sinun tulee aina tehdä, ovat sarakkeiden tietotyyppien uudelleennimeäminen ja asettaminen.

käytetään luokittelemaan arvoja M . Sarakkeen nimen vieressä oleva kuvake näyttää kentälle määritetyn tietotyypin. Tässä esimerkkitaulukossa sarakkeiden nimien vieressä on ABC123-kuvake. Tämä kuvake edustaa Kaikki tietotyyppiä, joka osoittaa, että tietotyyppiä ei ole määritetty kyseiselle sarakkeelle. 

Kirjoista automaattinen tyypin tunnistus on käytössä. Tämä luo kyselyihisi automaattisesti muuttunut tyyppi -vaiheet. Vaikka se olisi käytössä, varmista se ainaarvaa jokaisen sarakkeen tietotyypit oikein kaikissa taulukoissasi.

Voit ottaa asetuksen käyttöön tai poistaa sen käytöstä seuraavasti.

Yleinen - vaihtoehdossa voit valita yhden kolmesta käytettävissäsi olevasta Type Detection -vaihtoehdosta.

Nykyinen tiedosto -vaihtoehdon alta voit vaihtaa Tyypin tunnistuksen asetuksia yleisasetuksen mukaan.

Aseta tietotyypit käyttämällä Muunna-välilehteä

Käyttöliittymä tarjoaa useita tapoja asettaa tietotyyppejä sarakkeillesi. Muunna-välilehden Mikä tahansa sarake -osiossa on Tunnista tietotyyppi -painike.

Jos valitset kaikki sarakkeet ja napsautat kyseistä painiketta, se asettaa automaattisesti kaikkien sarakkeiden tietotyypit. Koska se kuitenkin tarkistaa vain 200 ylintä riviä, sinun on silti tarkistettava, onko Power Querylla oikea tietotyyppi.

Jos valitset sarakkeen taulukosta, näet sen tietotyypin Mikä tahansa sarake -osiossa.

Jos napsautat pudotusvalikkopainiketta ja valitset Teksti, valitun sarakkeen kuvake muuttuu ABC123 :sta vain ABC:ksi .

Näet sitten Muutettu tyyppi -vaiheen Applied Steps -ruudussa.

Etusivu-välilehdellä on myös mahdollisuus määrittää tietotyyppi.

Aseta tietotyypit sarakeotsikon avulla

Toinen tapa on napsauttaa otsikkoa hiiren kakkospainikkeella ja valita Muuta tyyppiä . Sieltä voit valita sopivan tietotyypin sarakkeillesi.

Aseta tietotyypit sarakekuvakkeiden avulla

Yleisin tapa määrittää tietotyypit on napsauttaa sarakkeen nimen vieressä olevaa kuvaketta. Se näyttää kaikki käytettävissä olevat tietotyypit.

Aseta esimerkkitaulukossa Tilauspäivämäärä- sarakkeen tietotyypiksi Päivämäärä .

Valitsemasi sarakkeen tietotyypistä riippuen Power Query tarjoaa joukon muunnoksia ja vaihtoehtoja, jotka koskevat kyseistä tietotyyppiä Muunna- , Lisää sarake -välilehdellä ja Älykäs suodatin -osiossa .

Joten jos valitset päivämääräsarakkeen ja siirryt Muunna-välilehteen, näet vaihtoehdot Päivämäärä-painikkeen alla.

Jos kuitenkin valitset tekstisarakkeen, Päivämäärä-painikkeen vaihtoehdot eivät ole enää käytettävissä.

Älykästä suodatusta varten tekstisarakkeissa on tekstisuodattimet ja päivämääräsarakkeissa päivämääräsuodattimet.

Muuta seuraavan sarakkeen sarakkeen nimeksi Asiakasavain ja tietotyypiksi Kokoluku .

Muuta seuraavaksi Kanava-, Valuuttakoodi- ja Varastokoodi-sarakkeiden tietotyypiksi Teksti.

Muuta sen jälkeen toimitusalueen indeksin nimi Toimitusalueen avaimeksi ja sen tietotyypiksi Kokoluku . Tee sitten sama tuotekuvausindeksi- ja Tilausmäärä - sarakkeille.

Aseta lopuksi yksikköhinta-, rivin kokonaissumma- ja yksikköhinta-sarakkeiden tietotyypiksi Kiinteä desimaaliluku .

Aseta tietotyypit Power Queryn kielivirtauksella

Sarakkeiden muutosten vuoksi Applied Steps -ruutuun on luotu paljon vaiheita. Ongelmana on, että koska samantyyppiset muunnokset suoritetaan useita kertoja, näytemuuttuu tehottomaksi. Tämä on jotain, jota sinun tulee välttää.

Tehostaaksesi kyselyäsi, yritä luoda yksi vaihe ottamalla kyseinen muunnos kaikkiin sarakkeihisi ennen uuden vaiheen luomista.

Kopioi esimerkkitaulukon kysely ja nimeä se uudelleen Best Practice . Napsauta Applied Steps -ruudussa hiiren kakkospainikkeella ensimmäistä edellisessä kyselyssä tehtyä muunnosa ja valitse Poista loppuun asti . Vahvista sitten vaiheen poistaminen Poista vaihe -valintaikkunassa.

Lisäksi tässä on joitain parhaita käytäntöjä. Ensimmäinen asia on luoda parametrit, jotka sisältävät tietolähteen sijainnin. Tämä helpottaa ongelmien korjaamista, kun tiedostonimi on muutettu.

Voit luoda parametreja napsauttamalla Aloitus-välilehdellä Hallitse parametreja ja valitsemalla Uusi parametri .

Toinen tapa on napsauttaa hiiren kakkospainikkeella Kysely-ruutua ja valita Uusi parametri .

Sen jälkeen näkyviin tulee Parametrien hallinta -valintaikkuna. Nimeä parametri FileLocation ja aseta Tyyppi arvoksi Teksti. Aseta Ehdotetut arvot -kohdaksi arvoluettelo , jotta voit liittää merkkijonosi ja muuttaa tai lisätä useita sijainteja, joiden välillä voit vaihtaa.

Siirry seuraavaksi File Exploreriin ja valitse tiedosto. Kopioi polku ja liitä parametrit. Kun olet valmis, paina OK .

Palaa Best Practice -kyselyyn ja napsauta Lähde-vaihetta Applied Steps -ruudussa. Muuta sitten kovakoodatun tiedoston polku kaavapalkissa FileLocation .

Poista tarpeettomat sarakkeet

Poista kaikki tarpeettomat sarakkeet säästääksesi tilaa ja parantaaksesi suorituskykyä. Tuo vain tarvitsemasi tiedot, koska taulukoiden ja sarakkeiden lisääminen on paljon helpompaa kuin niiden poistaminen.

Helpoin tapa tehdä se on Koti-välilehden Valitse sarakkeet -vaihe. Jos napsautat tätä painiketta, näkyviin tulee valintaikkuna, jonka avulla voit valita säilytettävät sarakkeet. Sinun tulee suunnitella ja muotoilla taulukot tietyllä tarkoituksella, jotta ne sopivat parhaiten suoritettavaan analyysiin.

Poista sieltä niiden sarakkeiden valinnat, joita et tarvitse taulukossasi. Tässä esimerkkitaulukossa OrderNumber-sarakkeen kardinaalisuus on korkea. On parasta poistaa kyseisen sarakkeen valinta, koska se vaikuttaa tiedoston kokoon ja yleiseen suorituskykyyn.

Jos et tarvitse sijaintitietoja näytekyselyn analysointiin, on myös parasta poistaa toimitusalueen indeksin valinta . Lopuksi, koska taulukossa on jo yksikköhinta ja määrä, rivin kokonaismäärä -saraketta ei tarvita.

Kun olet poistanut sarakkeiden valinnan, paina OK . Jos haluat muuttaa sarakkeen valintaa, tyhjennä vain vaiheen nimen vieressä oleva rataskuvake Applied Steps -ruudussa.

Määritä sopivat tietotyypit sarakkeisiin ja rajoita rivejä

Määritä seuraavaksi tietotyypit kaikille sarakkeille. Valitse kaikki sarakkeet ja napsauta Muunna-välilehdellä Tunnista tietotyyppi .

Tunnista tietotyyppi tunnistaa automaattisesti sarakkeiden tietotyypit 200 ylimmän rivin skannauksen perusteella. Joten tarkista ja varmista, että Power Query asettaa oikeat tietotyypit.

Paras käytäntö on rajoittaa rivien määrää. Jos tilikausi alkaa 1. heinäkuuta, voit jättää kesäkuun tiedot pois tai määrittää parametrin.

Siinä on kuitenkin ongelma, jos haluat muuttaa parametrin arvoa . Kun olet julkaissut raportin, sinun on oltava joko teksti- tai desimaalityyppinen.

Voit ratkaista tämän luomalla suodattimen tiedoille napsauttamalla avattavaa painiketta Tilauspäivämäärä-sarakkeessa. Napsauta seuraavaksi Päivämääräsuodattimet ja valitse Jälkeen .

Muuta valintaikkunassa ensimmäinen parametri arvoon on jälkeen tai yhtä suuri ja syötä sitten päivämäärä. Tässä esimerkissä syötetty päivämäärä oli 1. heinäkuuta 2014.

Kun olet painanut OK , suodatin otetaan käyttöön taulukossasi. Luo seuraavaksi parametri ja anna sille nimi DatesFrom . Aseta tyypiksi Teksti ja syötä päivämäärä Current Value -parametriin.

Palaa nyt Best Practice -kyselyyn ja korvaa sisäinen #date päivämäärällä.Alkaen (PäivämäärätFrom) .

Jos napsautat tarkistusmerkkiä ilman Date.From- funktiota, saat virheilmoituksen. Tämä johtuu siitä, että DatesFrom on tekstityyppi, kun taas kentällä on päivämäärätyyppi. Date.From - funktio muuntaa tekstin päivämääräksi.

Nimeä uudelleen kaikki sarakkeet, joita ei piiloteta tietomallissa. Nimen on oltava ytimekäs, itsekuvaava ja käyttäjäystävällinen. Muista, että suunnittelet tietomallia ihmisille, jotka aikovat käyttää raporttiasi.

Yhdistä Power Queryn kielivirtauksen ylimääräiset vaiheet

Seuraavaksi on yhdistettävä tarpeettomat vaiheet (kuten saraketietotyyppien nimeäminen uudelleen, poistaminen ja muuttaminen). Lisäksi vaiheet, kuten sarakkeiden uudelleenjärjestäminen, ovat asioita, joihin sinun tulee kiinnittää huomiota, kun etsit irtisanomisia.

Tietomalliin lataamasi taulukot eivät koskaan näy raportissasi. Tämä tekee sarakkeiden järjestyksestä merkityksettömän.

Toinen paras käytäntö on nimetä vaiheet uudelleen Applied Steps -ruudussa. Vaiheiden nimet ovat itsekuvaavia ja niitä käytetään muuttujina M- koodissa.

Välilyöntejä tai erikoismerkkejä sisältävät nimet kirjoitetaan lainausmerkinnällä. Se tarkoittaa, että nimet on suljettu lainausmerkkien joukkoon ja niiden edessä on hash- tai puntamerkki, mikä tekee M- koodista vaikeasti luettavan. Voit jättää välilyönnit pois tai sijoittaa niiden väliin alaviivan.

Dokumentaation lisätietojen lisääminen lisäämällä kommentteja Advanced Editor -ikkunaan on myös Power Queryn paras käytäntö. Voit tehdä sen myös Vaiheominaisuuden kuvauksessa . Ne näytetään työkaluvihjeinä, kun viet hiiren osoittimen huutomerkillä varustetun vaiheen päälle Applied Steps -ruudussa.

Tietäen, miksi teit tietyn valinnan alkukehityksessä, on erittäin hyödyllistä, kun sinun on palattava tiedostoon jonkin ajan kuluttua. Voit lisätä dokumentaatiotietoja napsauttamalla hiiren kakkospainikkeella vaihetta Applied Steps -ruudussa ja valitsemalla Ominaisuudet .

Näkyviin tulee Vaiheen ominaisuudet -valintaikkuna, johon voit kirjoittaa suodatuksen tai muuntamisen syyn.

Järjestä kyselyitä tehokkaamman kyselyn kielikulun saamiseksi

Yksi Power Queryn yleisimmistä parhaista käytännöistä on kyselyjen järjestäminen . Luo kansioita parametreille, funktioille, vaiheittaisille kyselyille ja kyselyille, jotka ladataan tietomalliin. Valitse tässä esimerkissä FileLocation- ja DatesFrom-kyselyt ja napsauta niitä hiiren kakkospainikkeella. Valitse sitten Siirrä ryhmään ja napsauta Uusi ryhmä .

Lisää seuraavaksi nimi valituille kyselyille ja paina OK .

Kun kyselyt on ryhmitelty, Kysely-ruutu näyttää tältä.

Muista poistaa lataus käytöstä kaikissa vaiheistuskyselyissäsi poistamalla valinta Ota kuorma käyttöön.

Toinen asia, josta keskustellaan tässä opetusohjelmassa, on kielikulku. Jokainen Applied Steps -ruudun vaiheista muuttaa arvon, jonka näet, kun napsautat niitä.

Alkutiedot tulivat ja alkoivat Navigointi-vaiheessa, ja sarakkeet valittiin. Seuraavaksi tietotyyppejä muutettiin ja ajanjakso asetettiin. Myös sarakkeet nimettiin uudelleen.

Kaikki vaiheet palauttavat taulukkotyypin arvon. Jos avaat Advanced Editor -ikkunan, näet let- lausekkeen ja in - lauseen. Niiden välissä on luettelo vaiheista tai muuttujien nimistä ja niille osoitetuista lausekkeista.

Kysely palauttaa sen, mikä seuraa in lauseketta, joka viittaa muuttujaluettelosi viimeiseen vaiheeseen. M- moottori seuraa sitten riippuvuusketjua takaisin in -lauseesta poistaakseen kaiken tarpeettoman ja työntääkseen muunnoksia takaisin lähteeseen, jos mahdollista.

Yhteenveto Power Queryn kielikulusta

Jos katsot kaavaa, näet kyselyn vaiheiden kulun. Näet myös kussakin vaiheessa käyttämät toiminnot.

Ensimmäinen vaihe, joka tehtiin kyselyssä, oli sarakkeen valinta. Kun vaihe suoritettiin käyttöliittymän avulla, kutsuttiin Table.SelectColumns -toiminto. Ensimmäisenä parametrinaan se otti taulukon, joka viittasi edellisen vaiheen muuttujan nimeen. Sitten se listasi kaikki valitut sarakkeiden nimet.

Toisessa vaiheessa saraketyypit muutettiin kutsumalla Table.TransformColumnTypes -funktiota. Sen ensimmäistä parametria kutsutaan edellisen vaiheen ulostuloksi. Sitten se listasi joukon muunnosluetteloita.

Kolmas vaihe asettaa ajanjakson suodattimen käyttämällä Table.SelectRows -toimintoa. Se otti taulukkotyyppisen kyselyn ensimmäisenä argumenttina. Tässä esimerkissä se viittasi Muutostyyppi-vaiheen tulosteeseen.

Viimeisessä vaiheessa sarakkeet nimettiin uudelleen käyttämällä Table.RenameColumns -toimintoa. Edellisen vaiheen tulosta käytettiin sen ensimmäisenä argumenttina. Sitten se listasi joukon uudelleennimeämisluetteloita.

Kaikki käyttöliittymän kautta käytettävät toiminnot alkavat sanalla Table . He kaikki ottivat taulukon arvon ensimmäiseksi parametriksi ja muuttivat sitten arvon.

Vaikka koodi näyttää peräkkäiseltä, koska jokainen vaihe viittaa edelliseen vaiheeseen, sekvenssiä ei vaadita. Jos siirrät askeleen, kysely suoritetaan silti, koska M- moottori seuraa aina riippuvuusketjua.

Käyttöliittymä on kätevä, mutta se olettaa aina, että haluat muuttaa edellisen muunnoksen tuloksia. Useimmissa tapauksissa se on luultavasti totta, ja jos näin ei ole, sinun on päivitettävä koodissa viitattu arvo manuaalisesti.

Johtopäätös

Tietojen muuntaminen on välttämätöntä, jotta tietosi pysyvät ryhmiteltyinä ja järjestyksessä. Se nopeuttaa tietojen kehittämistä, koska voit helposti jäljittää Power Queryn kielikulun ongelmat ja muokata raportin muutoksia.

Melissa