Power Queryn käytön parhaat käytännöt

Näiden Power Query parhaiden käytäntöjen avulla voit parantaa kyselyn tehokkuutta, hyödyntää kyselyn delegointia lähteeseen, valita oikeat tietotyypit, järjestää muunnoksia ja käyttää logiikkaa uudelleen parametreilla ja mukautetuilla funktioilla. Ne koskevat sekä Power Query Desktop- että Power Query Online -kokemuksia.

Valitse oikea liitin

Power Query tarjoaa useita tietoliittimiä. Nämä liittimet vaihtelevat TXT-, CSV- ja Excel-tiedostoista tietokantoihin, kuten Microsoft SQL Server, tietokantoihin ja suosittuihin SaaS(software as a service) -tuotteisiin, kuten Microsoft Dynamics 365 ja Salesforceen. Jos tarkoitukseen luotua liitintä ei ole käytettävissä Nouda tiedot -ikkunassa, käytä yleistä liitintä, kuten ODBC tai OLE DB.

Valitse tietolähteellesi tarkoitukseen luotu liitin, kun sellainen on käytettävissä. esimerkiksi SQL Server tarjoaa paremman Nouda tiedot -käyttökokemuksen kuin yleinen ODBC-liitin, kun muodostat yhteyden SQL Server tietokantaan. SQL Server-liitin tukee myös suorituskykyominaisuuksia, kuten kyselyn delegointia lähteeseen. Lisätietoja on artikkelissa Yleiskatsaus kyselyn arvioinnista ja kyselyn delegoinnista lähteeseen Power Query.

Jokainen tietoyhdistin noudattaa vakiokokemusta, joka on selitetty kohdassa Tietojen hankkiminen. Tässä standardoidussa käyttökokemuksessa on vaihe nimeltä Tietojen esikatselu. Tässä vaiheessa sinulle annetaan käyttäjäystävällinen ikkuna, jossa voit valita tietolähteestä saatavat tiedot, jos liitin sallii sen, ja näiden tietojen yksinkertaisen tietojen esikatselun. Voit myös valita useita tietojoukkoja tietolähteestäSi Siirtymistoiminto-ikkunan kautta.

Suodata tiedot aikaisessa vaiheessa suorituskyvyn parantamiseksi

Suodata tiedot mahdollisimman aikaisin, jotta voit vähentää prosessien Power Query rivien määrää myöhemissä muunnoksissa. Jos liitin tukee kyselyn delegointia lähteeseen, Power Query voi työntää suodattimia takaisin tietolähteeseen. Ohjeet ovat kohdassa Yleiskatsaus kyselyn arvioinnista ja kyselyn delegoinnista lähteeseen Power Query. Epäolenna olevien tietojen suodattaminen pois rajoittaa myös tietojen esikatselussa näytettyjä tietoja.

Käytä automaattisen suodattimen valikkoa, joka näyttää sarakkeesta löytyneiden arvojen erillisen luettelon, ja valitse arvot, jotka haluat säilyttää tai suodattaa pois. Löydät sarakkeen arvot hakupalkin avulla.

Voit myös hyödyntää tyyppikohtaisia suodattimia, kuten Edellinen-suodatin päivämäärä-, päivämäärä/aika- tai jopa päivämäärä-aikavyöhykesarakkeessa.

Näiden tyyppikohtaisten suodattimien avulla voit luoda dynaamisen suodattimen, joka hakee aina edellisen x sekunnin, minuuttien, tuntien, päivien, viikkojen, kuukausien, vuosineljänneksten tai vuosien määrän tiedot.

Kalliiden toimintojen käyttö viimeisenä suorituskyvyn parantamiseksi

Jos haluat parantaa esikatselun suorituskykyä Power Query editorissa, suorita sitten kalliita toimintoja. Jotkin toiminnot edellyttävät koko tietolähteen lukemista tulosten palauttamiseksi, joten niiden esikatselu on hidasta. Jos esimerkiksi suoritat lajittelun, on mahdollista, että ensimmäiset lajiteltuja rivejä ovat lähdetietojen lopussa. Tulosten palauttamiseksi lajittelutoiminnon on ensin luettava kaikki rivit.

Muiden toimintojen (kuten suodattimien) ei tarvitse lukea kaikkia tietoja ennen tulosten palauttamista. Sen sijaan ne käsittelevät tietoja niin kutsutulla suoratoistolla. Tiedot "tietovirtana" ja tulokset palautetaan samalla. Power Query -editorissa tällaisten toimintojen tarvitsee vain lukea tarpeeksi lähdetietoja esikatselun täyttämiseksi.

Kun mahdollista, suorita ensin tällaiset suoratoistotoiminnot ja tee vielä kalliimmat toiminnot. Toimintojen suorittaminen tässä järjestyksessä auttaa minimoimaan esikatselun hahmontamiseen kuluvaa aikaa aina, kun lisäät uuden vaiheen kyselyyn.

Tietojen alijoukon käyttäminen kyselyn kehittämisen aikana

Jos uusien vaiheiden lisääminen Power Query-editoriin on hidasta, käytä Säilytä ensimmäiset rivit -komentoa, kun kehität kyselyä. Kun olet lisännyt kaikki tarvittavat vaiheet, poista Säilytä ensimmäiset rivit -vaihe, jotta valmis kysely käsittelee koko tietojoukon.

Käytä oikeita tietotyyppejä

Määritä kullekin sarakkeelle oikea tietotyyppi, jotta Power Query voivat käyttää tyyppikohtaisia muunnoksia ja suodattimia. Kun esimerkiksi valitset päivämääräsarakkeen, voit käyttää Lisää sarake -valikon Päivämäärä ja aika -sarakeryhmän asetuksia. Jos sarakkeessa ei ole tietotyyppijoukkoa, nämä asetukset näkyvät harmaina.

Samankaltainen tilanne ilmenee tyyppikohtaisissa suodattimissa, koska ne koskevat tiettyjä tietotyyppejä. Jos sarakkeeseen ei ole määritetty oikeaa tietotyyppiä, nämä tyyppikohtaiset suodattimet eivät ole käytettävissä.

On tärkeää, että käsittelet aina sarakkeiden oikeita tietotyyppejä. Kun käsittelet jäsennettyjä tietolähteitä, kuten tietokantoja, tietotyyppitiedot tuodaan tietokannasta löytytystä taulukkorakenteesta. Rakenteettomien tietolähteiden, kuten TXT- ja CSV-tiedostojen, tapauksessa on kuitenkin tärkeää määrittää oikeat tietotyypit kyseisestä tietolähteestä tuleville sarakkeille. Power Query tarjoaa oletusarvoisesti automaattisen tietotyypin tunnistuksen jäsentämättömille tietolähteille. Voit lukea lisää tästä ominaisuudesta ja siitä, miten siitä voi olla apua tietotyypeissä.

Profiloi ja tutki tietojasi

Ennen kuin valmistelet tietosi ja lisäät muunnosvaiheita, ota käyttöön Power Query tietojen profilointityökalut, jotta voit löytää tietoja tiedoistasi.

Power Query tarjoaa kolme tietojen profilointityökalua:

Voit myös käsitellä näitä ominaisuuksia, mikä auttaa sinua valmistelemaan tietojasi.

Työn dokumentointi

Power Query ratkaisun dokumentointi antamalla vaiheita, kyselyitä sekä ryhmittelemällä kuvaavia nimiä ja kuvauksia. Näiden tietojen avulla kunkin muunnoksen tarkoitus on helpompi ymmärtää ja ylläpitää.

Vaikka Power Query luo vaihenimi puolestasi automaattisesti käytössä olevat vaiheet -ruudussa, voit myös nimetä vaiheet uudelleen tai lisätä niihin kuvauksen.

Suurten kyselyiden jakaminen moduuleihin

Jaa suuri Power Query kysely pienempiin viitattuihin kyselyihin, jotta sen muunnosvaiheita on helpompi ymmärtää ja ylläpitää. Vaikka yksittäinen kysely voi sisältää kaikki tarvitsemasi muunnokset ja laskutoimitukset, monia vaiheita sisältävää kyselyä on helpompi hallita, kun yksi kysely viittaa seuraavaan.

Esimerkiksi seuraavassa kyselyssä on yhdeksän vaihetta, ja siinä on Yhdistä hintoihin -taulukon vaihe.

Voit jakaa tämän kyselyn kahdeksi Yhdistä hinnat -taulukon vaiheessa. Näin on helpompi ymmärtää vaiheet, jotka otettiin käyttöön myyntikyselyssä ennen yhdistämistä. Voit tehdä tämän napsauttamalla hiiren kakkospainikkeella Yhdistä hinnoilla -taulukon vaihetta ja valitsemalla Poimi edellinen - vaihtoehto.

Näyttöön tulee valintaikkuna, jossa pyydetään antamaan uudelle kyselylle nimi. Tämä vaihe jakaa kyselyn käytännössä kahdeksi kyselyksi. Yhdellä kyselyllä on kaikki vaiheet ennen yhdistämistä. Toisessa kyselyssä on ensimmäinen vaihe, joka viittaa uuteen kyselyyn ja muihin vaiheisiin, jotka olet saanut alkuperäisessä kyselyssäsi Yhdistä hinnoilla -taulukon vaiheesta alaspäin.

Voit myös käyttää kyselyviittausta tarpeidesi mukaan. Kyselyt kannattaa kuitenkin pitää sellaisella tasolla, joka ei vaikuta ensi silmäyksellä niin monilta vaiheilta.

Kyselyjen järjestäminen ryhmiin

Pidä työsi organisoituna kyselyruudun ryhmien avulla.

Ryhmien ainoa tarkoitus on auttaa pitämään työsi järjestettynä toimimalla kyselyiden kansioina. Voit luoda ryhmiä ryhmissä tarpeen mukaan. Kyselyjen siirtäminen ryhmien välillä on yhtä helppoa kuin vetäminen ja pudottaminen.

Yritä antaa ryhmillesi merkityksellinen nimi, joka on järkevä sinulle ja tapauksessasi.

Tulevaisuuden kestävät kyselyt

Suunnittele kyselyt, joiden avulla voit käsitellä odotettuja muutoksia lähdetiedoissa, jotta tulevat päivitykset onnistuvat edelleen. Power Query tarjoaa muunnoksia, jotka tekevät kyselystä joustavan, kun tietolähteen rivit, sarakkeet tai arvot muuttuvat.

Määritä kyselyn laajuus, mukaan lukien sen toiminta ja se, mitä sen tulee selittää rakenteen, asettelun, sarakkeiden nimien, tietotyyppien ja minkä tahansa muun oleellisen osan osalta.

Seuraavat muunnokset voivat auttaa kyselyä pysymään sitkeänä muutoksille:

Parametrien käyttäminen

Power Query parametrien avulla voit tallentaa ja hallita arvoja, joita voit käyttää uudelleen muunnoksissa, tietolähdefunktioissa ja mukautetuissa funktioissa. Parametrit helpottavat kyselyiden päivittämistä, koska voit muuttaa arvoa yhdessä sijainnissa sitä käyttävän jokaisen kyselyn muokkaamisen sijaan. Kaksi yleistä skenaariota ovat:

• Vaihe-argumentti: Käytä parametria käyttöliittymästä ajettujen useiden muunnosten argumenttina.

  

• Custom Function -argumentti: Luo kyselystä uusi funktio ja viittaa parametreihin mukautetun funktion argumentteina.

  

Parametrien luomisen ja käyttämisen tärkeimmät edut ovat seuraavat:

• Keskitetty näkymä kaikista parametreista Parametrien hallinta - ikkunan kautta.

  

• Parametrin uudelleenkäytettävyys useissa vaiheissa tai kyselyissä.

• Tekee mukautettujen funktioiden luomisesta yksinkertaista ja helppoa.

Voit jopa käyttää parametreja joissakin tietoliittimien argumenteissa. Voit esimerkiksi luoda parametrin palvelimen nimelle, kun muodostat yhteyden SQL Server -tietokantaan. Sen jälkeen voit käyttää kyseistä parametria SQL Server -tietokanta-valintaikkunassa.

Jos muutat palvelimen sijaintia, sinun tarvitsee vain päivittää palvelimen nimen parametri ja kyselysi päivitetään.

Uudelleenkäytettäviä funktioita

Luo mukautettu Power Query funktio, kun haluat käyttää samoja muunnoksia eri kyselyissä tai arvoissa. Power Query mukautettu funktio yhdistää syötearvojen joukon yhteen tulostearvoon, ja se luodaan alkuperäisistä Power Query M-kaavakielifunktioista ja -operaattoreista.

Oletetaan esimerkiksi, että sinulla on useita kyselyitä tai arvoja, jotka edellyttävät samoja muunnoksia. Voit luoda mukautetun funktion, joka käynnistetään myöhemmin valintasi kyselyiden tai arvojen perusteella. Tämä mukautettu funktio säästää aikaa ja auttaa hallitsemaan muunnosten joukkoa keskitetyssä sijainnissa, jota voit muokata milloin tahansa.

Power Queryn mukautettuja funktioita voidaan luoda olemassa olevista kyselyistä ja parametreista. Kuvitellaan esimerkiksi kysely, jossa on useita koodeja tekstimerkkijonona, ja haluat luoda funktion, joka purkaa näiden arvojen koodauksen.

Aloitat ottamalla parametrin, jonka arvo toimii esimerkkinä.

Tässä parametrissa luot uuden kyselyn, jossa käytät tarvitsemiasi muunnoksia. Tässä tapauksessa haluat jakaa koodin PTY-CM1090-LAX useisiin osiin:

• Alkuperä = PTY
• Kohde = LAX
• Lentoyhtiö = CM
• FlightID = 1090

Voit sitten muuntaa kyselyn funktioksi napsauttamalla kyselyä hiiren kakkospainikkeella ja valitsemalla Luo funktio. Lopuksi voit käynnistää mukautetun funktion mihin tahansa kyselyösi tai arvoosi.

Muutaman muunnoksen jälkeen näet, että saavutit haluamasi tuloksen ja käytit tällaisen muunnoksen logiikkaa mukautetusta funktiosta.