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

Tässä artikkelissa on joitakin vihjeitä ja vinkkejä, joiden avulla voit hyödyntää tietojen käyttökokemustasi Power Queryssa.

Valitse oikea liitin

Power Query tarjoaa suuren määrän tietoyhdistimiä. Nämä liittimet vaihtelevat TXT-, CSV- ja Excel-tiedostoista tietokantoihin, kuten Microsoft SQL Serveriin, ja suosittuihin SaaS-palveluihin, kuten Microsoft Dynamics 365 ja Salesforce. Jos et näe tietolähdettä Nouda tiedot -ikkunassa, voit aina muodostaa yhteyden tietolähteeseen ODBC- tai OLEDB-liittimen avulla.

Kun käytät tehtävään parasta liitintä, saat parhaan käyttökokemuksen ja suorituskyvyn. Esimerkiksi SQL Server -liittimen käyttäminen ODBC-liittimen sijaan SQL Server -tietokantaan yhdistettäessä tarjoaa paljon paremman Nouda tiedot -käyttökokemuksen lisäksi myös ominaisuuksia, jotka voivat parantaa käyttökokemustasi ja suorituskykyä, kuten kyselyn delegoinnin lähteeseen. Jos haluat lisätietoja kyselyn delegoinnista lähteeseen, siirry kohtaan Yleiskatsaus kyselyn arviointiin ja kyselyn delegointaan lähteeseen Power Queryssa.

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 seuraavassa kuvassa esitetyllä tavalla.

Muistiinpano

Jos haluat nähdä luettelon kaikista Power Queryssä käytettävissä olevista liittimista, siirry Näyttöyhteys ors-kohtaan Power Queryssa.

Suodata aikaisin

Tietojen suodattaminen kyselyn alkuvaiheessa tai mahdollisimman aikaisessa vaiheessa on aina suositeltavaa. Jotkin liittimet hyödyntävät suodattimiasi kyselyn delegoinnilla lähteeseen. Saat ohjeet kohdasta Yleiskatsaus kyselyn arviointiin ja kyselyn delegointaan lähteeseen Power Queryssa. Parhaana käytäntönä on myös suodattaa pois tiedot, jotka eivät ole olennaisia sinun tapauksessasi. Näin voit keskittyä paremmin käsillä olevaa tehtävääsi näyttämällä vain tiedot, jotka ovat olennaisia tietojen esikatseluosiossa.

Voit käyttää automaattisen suodattimen valikkoa, joka näyttää sarakkeesta löytyneiden arvojen erillisen luettelon, ja valita arvot, jotka haluat säilyttää tai suodattaa pois. Voit myös etsiä 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 noutaa aina tiedot, jotka ovat edellisen x sekunnin, minuutin, tunnin, päivän, viikon, kuukauden, vuosineljänneksen tai vuoden kohdalla seuraavassa kuvassa esitetyllä tavalla.

Muistiinpano

Jos haluat lisätietoja tietojen suodattamisesta sarakkeen arvojen perusteella, siirry kohtaan Suodata arvojen mukaan.

Tee kalliita toimintoja viimeksi

Tietyt toiminnot edellyttävät koko tietolähteen lukemista tulosten palauttamiseksi, joten niiden esikatselu on hidasta Power Query -editori. Jos esimerkiksi suoritat lajittelun, on mahdollista, että ensimmäiset lajiteltuja rivejä ovat lähdetietojen lopussa. Tulosten palauttamiseksi lajittelutoiminnon on siis 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 -editori 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. Tämä auttaa minimoimaan esikatselun hahmontamiseen kuluvan ajan, joka kerta, kun lisäät uuden vaiheen kyselyyn.

Tietojen alijoukon käsitteleminen tilapäisesti

Jos uusien vaiheiden lisääminen kyselyyn Power Query -editori on hidasta, harkitse ensin Säilytä ensimmäiset rivit -toiminnon tekemistä ja käsittelemiesi rivien määrän rajoittamista. Kun olet lisännyt kaikki tarvitsemasi vaiheet, poista Säilytä ensimmäiset rivit -vaihe.

Käytä oikeita tietotyyppejä

Jotkin Power Queryn ominaisuudet liittyvät valittuun sarakkeen tietotyyppiin kontekstissa. Kun esimerkiksi valitset päivämääräsarakkeen, Käytettävissä olevat vaihtoehdot Päivämäärä ja aika -sarakeryhmässä Lisää sarake -valikossa ovat käytettävissä. Jos sarakkeessa ei ole tietotyyppijoukkoa, nämä asetukset näkyvät harmaana.

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ä.

Muistiinpano

Saat lisätietoja tietotyyppien tärkeydestä ja niiden käytön ohjeartikkelista Tietotyypit.

Tietojen tutkiminen

Ennen kuin aloitat tietojen valmistelun ja lisäät uusia muunnosvaiheita, suosittelemme, että otat käyttöön Power Query tietojen profilointityökalut , joiden avulla saat helposti tietoja tiedoistasi.

Tietojen profilointityökalut auttavat sinua ymmärtämään tietojasi paremmin. Työkalut tarjoavat pieniä visualisointeja, jotka näyttävät tietoja sarakekohtaisesti, kuten:

• Sarakkeen laatu – Tarjoaa pienen palkkikaavion ja kolme ilmaisinta, jotka ilmaisevat, kuinka monta arvoa sarakkeessa kuuluu kelvollisten, virheellisten tai tyhjien arvojen luokkiin.
• Sarakkeen jakelu – Tarjoaa joukon visualisointeja niiden sarakkeiden nimien alle, jotka esittelevät arvojen tiheyden ja jakautumisen kussakin sarakkeessa.
• Sarakeprofiili – tarjoaa kattavamman näkymän sarakkeeseen ja siihen liittyviin tilastotietoihin.

Voit myös käyttää näitä ominaisuuksia, jotka auttavat sinua valmistelemaan tietojasi.

Muistiinpano

Saat lisätietoja tietojen profilointityökaluista tietojen profilointityökalujen artikkelista.

Työn dokumentointi

Suosittelemme, että dokumentoit kyselysi nimeämällä kyselyt uudelleen tai lisäämällä kuvauksen vaiheisiin, kyselyihin tai ryhmiin haluamallasi tavalla.

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

Muistiinpano

Jos haluat lisätietoja kaikista käytössä olevien vaiheiden ruudussa olevista käytettävissä olevista ominaisuuksista ja osista, siirry Käytössä olevat vaiheet -luetteloon.

Ota modulaarinen lähestymistapa

On täysin mahdollista luoda yksittäinen kysely, joka sisältää kaikki tarvitsemasi muunnokset ja laskutoimitukset. Jos kysely kuitenkin sisältää suuren määrän vaiheita, voi olla hyvä idea jakaa kysely useisiin kyselyihin, joissa yksi kysely viittaa seuraavaan. Tämän lähestymistavan tavoitteena on yksinkertaistaa ja erottaa muunnosvaiheet pienemmiksi palasiksi, jotta ne ovat helpommin ymmärrettäviä.

Oletetaan esimerkiksi, että sinulla on kysely, jossa on seuraavassa kuvassa yhdeksän vaihetta.

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ä jakaa kyselyn kahdeksi kyselyksi. Yhdellä kyselyllä on kaikki kyselyt ennen yhdistämistä. Toisessa kyselyssä on ensimmäinen vaihe, joka viittaa uuteen kyselyyn ja muihin vaiheisiin, jotka olet suorittanut alkuperäisessä kyselyssäSi Yhdistä hinnoilla -taulukon vaiheesta alaspäin.

Voit myös hyödyntää kyselyn viittaamista tarpeen mukaan. Kyselyt kannattaa kuitenkin pitää sellaisella tasolla, joka ei vaikuta ensi silmäyksellä niin monilta vaiheilta.

Muistiinpano

Lisätietoja kyselyihin viittaamisesta on artikkelissa Tietoja kyselyiden ruudusta.

Luo ryhmiä

Voit pitää työsi organisoituna hyödyntämällä ryhmien käyttöä Kyselyt-ruudussa.

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.

Muistiinpano

Saat lisätietoja kaikista Kyselyt-ruudun käytettävissä olevista ominaisuuksista ja osista artikkelista Kyselyt-ruudun ymmärtäminen.

Tulevaisuuden kestävät kyselyt

Varmista, että luot kyselyn, jossa ei ole ongelmia tulevan päivityksen aikana, on etusijalla. Power Queryssä on useita ominaisuuksia, joiden avulla kyselysi kestää muutokset ja jotka voidaan päivittää, vaikka jotkin tietolähteen osat muuttuvat.

Parhaana käytäntönä on määrittää kyselyn vaikutusalue sen mukaan, mitä sen tulee tehdä ja mitä sen tulee ottaa huomioon rakenteessa, asettelussa, sarakkeiden nimissä, tietotyypeissä ja missä tahansa muussa laajuudessa oleellisen osan osalta.

Seuraavassa on esimerkkejä muunnoksista, joiden avulla voit tehdä kyselystäsi joustavan muutoksille:

• Jos kyselyssäsi on dynaaminen määrä rivejä, joissa on tietoja, mutta kiinteä määrä rivejä, jotka toimivat poistettavana alatunnisteena, voit käyttää Poista alimmat rivit -ominaisuutta.

  Muistiinpano

  Jos haluat lisätietoja tietojen suodattamisesta rivin sijainnin mukaan, siirry kohtaan Taulukon suodattaminen rivin sijainnin mukaan.

• Jos kyselyssäsi on dynaaminen määrä sarakkeita, mutta sinun tarvitsee valita vain tietyt sarakkeet tietojoukosta, voit käyttää Valitse sarakkeet -toimintoa .

  Muistiinpano

  Lisätietoja sarakkeiden valitsemisesta tai poistamisesta on kohdassa Sarakkeiden valitseminen tai poistaminen.

• Jos kyselyssäsi on dynaaminen määrä sarakkeita ja sinun on peruutettava vain sarakkejesi alijoukon pivotointi, voit käyttää Poista vain valittujen sarakkeiden pivotointi -toimintoa.

  Muistiinpano

  Saat lisätietoja sarakkeiden pivotoinnin poistamisen vaihtoehdoista artikkelista Poista sarakkeiden pivotointi.

• Jos kyselyssäsi on vaihe, joka muuttaa sarakkeen tietotyyppiä, mutta jotkin solut tuottavat virheitä, koska arvot eivät vastaa haluttua tietotyyppiä, voit poistaa rivit, jotka tuottivat virhearvoja.

  Muistiinpano

  Lisätietoja virheiden käsittelystä ja käsittelystä on kohdassa Virheiden käsittely.

Parametrien käyttäminen

Dynaamisten ja joustavien kyselyiden luominen on paras käytäntö. Power Queryn parametrien avulla voit tehdä kyselyistäsi dynaamisempia ja joustavampia. Parametrin avulla voit helposti tallentaa ja hallita arvoa, jota voidaan käyttää uudelleen monella eri tavalla. Sitä käytetään kuitenkin yleisemmin kahdessa skenaariossa:

• Vaihe-argumentti – Voit käyttää parametria käyttöliittymästä ajettujen useiden muunnosten argumenttina.

  

• Mukautettu funktio -argumentti – Voit luoda uuden funktion kyselystä ja viitata 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.

Muistiinpano

Saat lisätietoja parametrien luomisesta ja käyttämisestä parametrien käyttäminen -artikkelista.

Uudelleenkäytettäviä funktioita

Jos olet tilanteessa, jossa sinun on käytettävä samoja muunnoksia eri kyselyihin tai arvoihin, mukautetun Power Query -funktion luominen voidaan käyttää uudelleen niin monta kertaa kuin tarvitaan. Power Queryn mukautettu funktio on yhdistämismääritys syötearvojen ryhmästä yksittäiseen tulostearvoon, ja se on luotu alkuperäisistä M-funktioista ja -operaattoreista.

Oletetaan esimerkiksi, että sinulla on useita kyselyitä tai arvoja, jotka edellyttävät samoja muunnoksia. Voit luoda mukautetun funktion, joka voidaan myöhemmin käynnistää valintasi kyselyistä tai arvoista. Tämä mukautettu funktio säästää aikaa ja auttaa sinua hallitsemaan muunnosjoukkoasi 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, jolla on esimerkkinä toimiva arvo.

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 seuraavassa kuvassa esitetyllä tavalla.

Muutaman muunnoksen jälkeen näet, että olet saavuttanut haluamasi tuloksen ja hyödyntänyt tällaisen muunnoksen logiikkaa mukautetusta funktiosta.

Muistiinpano

Saat lisätietoja mukautettujen funktioiden luomisesta ja käyttämisestä Power Queryssa mukautetut funktiot -artikkelista.