Parametrikyselyn luominen (Power Query)

Parametrikyselyt ovat sinulle ehkä tuttuja SQL:ssä ja Microsoft Queryssä. Power Query -parametreissa on kuitenkin merkittäviä eroja:

• Parametreja voidaan käyttää kaikissa kyselyn vaiheissa. Sen lisäksi, että parametrit toimivat tietosuodattimena, niillä voidaan määrittää esimerkiksi tiedostopolku tai palvelimen nimi.
• Parametrit eivät pyydä tietoja. Sen sijaan voit muuttaa niiden arvoa nopeasti Power Query -toiminnolla. Voit myös tallentaa ja hakea arvoja soluista Excelissä.
• Parametrit tallennetaan yksinkertaiseen parametrikyselyyn, mutta ne ovat erillään tietokyselyistä, joissa niitä käytetään. Kun kysely on luotu, voit lisätä kyselyyn parametrin tarpeen mukaan.

Huomautus Jos haluat luoda parametrikyselyjä toisin tavalla, katso lisätietoja kohdasta Parametrikyselyn luominen Microsoft Queryssä.

Parametrin luominen

Parametrin avulla voit muuttaa kyselyn arvoa automaattisesti ja välttää kyselyn muokkaamisen joka kerta arvon muuttamiseksi. Muutat vain parametrin arvon. Kun olet luonut parametrin, se tallennetaan erityiseen parametrikyselyyn, jota voit muuttaa kätevästi suoraan Excelistä.

1. Valitse tiedot,>nouda tiedot>muut lähteet>, käynnistä Power Query -editori.

2. Valitse Power Query -editorissa Aloitus>,Parametrien hallinta, Uudet > parametrit.

3. Valitse Parametrien hallinta -valintaikkunassa Uusi.

4. Määritä seuraavat tarpeen mukaan:

   Nimi	Tämän pitäisi vastata parametrin funktiota, mutta pitää se mahdollisimman lyhyenä.
   Kuvaus	Tämä voi sisältää mitä tahansa tietoja, jotka auttavat käyttäjiä käyttämään parametria oikein.
   Pakollinen	Toimi seuraavasti: Mikä tahansa arvo Voit antaa parametrikyselyyn minkä tahansa tietotyypin arvon. Arvoluettelo Voit rajoittaa arvot tiettyyn luetteloon kirjoittamalla ne pieneen ruudukkoon. Alla on myös valittava Oletusarvo ja Nykyinen arvo . Kysely Valitse luettelokysely, joka muistuttaa luettelon rakenteellista saraketta, joka on erotettu toisistaan pilkuilla ja suljettu aaltosulkeisiin. Seurantakohteiden tila -kentässä voi esimerkiksi olla kolme arvoa: {"Uusi", "Käynnissä", "Suljettu"}. Sinun on luotava luettelokysely etukäteen avaamalla Laajennettu editori (valitse HomeLaajennettu>editori), poistamalla koodimalli, kirjoittamalla arvoluettelo kyselyluettelomuodossa ja valitsemalla sitten Valmis. Kun olet luonut parametrin, luettelokysely näkyy parametrin arvoissa.
   Tyyppi	Tämä määrittää parametrin tietotyypin.
   Ehdotetut arvot	Voit halutessasi lisätä arvoluettelon tai määrittää kyselyn, joka antaa ehdotuksia tietojen saamiseksi.
   Oletusarvo	Tämä näkyy vain, jos Ehdotetut arvot -asetuksena on Arvojen luettelo ja mikä luettelokohde on määritetty oletusarvoksi. Tässä tapauksessa sinun on valittava oletusasetus.
   Nykyinen arvo	Jos parametri on tyhjä, kysely ei ehkä palauta tuloksia. Jos Pakollinen-vaihtoehto on valittuna, nykyinen arvo ei voi olla tyhjä.

5. Luo parametri valitsemalla OK.

Tietolähteen muuttaminen parametrin avulla

Voit hallita tietolähteiden sijaintien muutoksia ja välttää päivitysvirheitä seuraavalla tavalla. Jos rakenne ja tietolähde ovat samanlaiset, voit esimerkiksi luoda parametrin, jonka avulla voit helposti muuttaa tietolähdettä ja estää tietojen päivitysvirheet. Joskus palvelin, tietokanta, kansio, tiedostonimi tai sijainti muuttuu. Tietokannan hallinnoija saattaa joskus vaihtaa palvelimen, kuukausittain pudotettava CSV-tiedosto siirtyy toiseen kansioon tai joudut ehkä helposti vaihtamaan kehitys-, testaus- ja tuotantoympäristöstä toiseen.

Vaihe 1: Parametrikyselyn luominen

Seuraavassa esimerkissä sinulla on useita CSV-tiedostoja, jotka tuodaan kansion tuontitoiminnolla (Valitse Tiedot>Hae tiedot>FilesFrom>-kansiosta) kansiosta C:\DataFilesCSV1. Joskus tiedostojen pudottamispaikkana käytetään kuitenkin myös eri kansiota, C:\DataFilesCSV2. Voit käyttää kyselyssä parametria toisen kansion korvikkeena.

1. Valitse Aloitus,>Parametrien>hallinta, uusi parametri.

2. Kirjoita seuraavat tiedot Parametrien hallinta -valintaikkunaan:

   Nimi	CSVFileDrop
   Kuvaus	Vaihtoehtoinen tiedostojen pudotussijainti
   Pakollinen	Kyllä
   Tyyppi	Teksti
   Ehdotetut arvot	Mikä tahansa arvo
   Nykyinen arvo	C:\DataFilesCSV1

3. Valitse OK.

Vaihe 2: Parametrin lisääminen tietokyselyyn

1. Voit määrittää kansion nimen parametriksi valitsemalla KyselyasetustenKyselyvaiheet-kohdassaLähde ja valitsemalla sitten Muokkaa asetuksia.
2. Varmista, että Tiedostopolku-asetukseksi on määritetty Parametri, ja valitse sitten juuri luomasi parametri avattavasta luettelosta.
3. Valitse OK.

Vaihe 3: Päivitä parametrin arvo

Kansion sijainti on juuri muuttunut, joten voit nyt päivittää parametrikyselyn.

1. Valitse Tietoyhteydet>& Kyselyt>Kyselyt-välilehti , napsauta parametrikyselyä hiiren kakkospainikkeella ja valitse sitten Muokkaa.
2. Kirjoita uusi sijainti Nykyinen arvo -ruutuun, esimerkiksi C:\DataFilesCSV2.
3. Valitse Aloitus,>Sulje & Lataa.
4. Voit vahvistaa tulokset lisäämällä uusia tietoja tietolähteeseen ja päivittämällä sitten tietokyselyn päivitetyllä parametrilla (Valitse Tiedot>päivitä kaikki).

Tietojen suodattaminen parametrien avulla

Joskus haluat ehkä muuttaa kyselyn suodatinta helposti, jotta saat erilaisia tuloksia muokkaamatta kyselyä tai tekemättä hieman erilaisia kopioita samasta kyselystä. Tässä esimerkissä muutetaan päivämäärää tietosuodattimen muuttamiseksi.

1. Voit avata kyselyn etsimällä aiemmin Power Query -editorista ladatun kyselyn, valitsemalla tiedoissa olevan solun ja valitsemalla sitten Kyselyn>muokkaaminen. Lisätietoja on artikkelissa Kyselyn luominen, lataaminen tai muokkaaminen Excelissä.

2. Suodata tiedot valitsemalla minkä tahansa sarakkeen otsikossa oleva suodatinnuoli ja valitse sitten suodatuskomento, kuten Päivämäärä ja aika suodattimet>jälkeen. Suodata rivit -valintaikkuna tulee näkyviin.

   

3. Valitse Arvo-ruudun vasemmalla puolella oleva painike ja tee sitten jompikumpi seuraavista:

   • Jos haluat käyttää aiemmin luotua parametria, valitse Parametri ja valitse sitten haluamasi parametri oikealle tulevasta luettelosta.
   • Jos haluat käyttää uutta parametria, valitse Uusi parametri ja luo sitten parametri.

4. Kirjoita uusi päivämäärä Nykyinen arvo -ruutuun ja valitse sitten Aloitus>Sulje & Lataa.

5. Voit vahvistaa tulokset lisäämällä uusia tietoja tietolähteeseen ja päivittämällä sitten tietokyselyn päivitetyllä parametrilla (Valitse Tiedot>päivitä kaikki). Voit esimerkiksi vaihtaa suodattimen arvon toiseen päivämäärään, jotta näet uudet tulokset.

6. Kirjoita uusi päivämäärä Nykyinen arvo -ruutuun.

7. Valitse Aloitus,>Sulje & Lataa.

8. Voit vahvistaa tulokset lisäämällä uusia tietoja tietolähteeseen ja päivittämällä sitten tietokyselyn päivitetyllä parametrilla (Valitse Tiedot>päivitä kaikki).

Tietojen suodattaminen solun arvon avulla

Tässä esimerkissä kyselyparametrin arvo luetaan työkirjan solusta. Sinun ei tarvitse muuttaa parametrikyselyä, vaan voit päivittää vain solun arvon. Oletetaan, että haluat suodattaa sarakkeen ensimmäisen kirjaimen perusteella, mutta muuttaa arvoa helposti miksi tahansa kirjaimeksi A:sta Ö:hen.

1. Luo sen työkirjan laskentataulukkoon, jossa suodatettava kysely on ladattu, Excel-taulukko, jossa on kaksi solua: otsikko ja arvo.

   MyFilter
   G

2. Valitse solu Excel-taulukosta ja valitse sitten Tietojen>noutaminen>taulukosta tai alueesta. Power Query -editori tulee näkyviin.

3. Muuta oikealla olevan Kyselyasetukset-ruudunNimi-ruudussa kyselyn nimeä kuvaavammaksi (esimerkiksi SuodataSolunArvo).

4. Välitä taulukon arvon taulukon sijaan napsauttamalla arvoa hiiren kakkospainikkeella tietojen esikatselussa ja valitsemalla sitten Siirry alaspäin.
   Huomaa, että kaava muuttui muotoon = #"Changed Type"{0}[MyFilter]
   Kun käytät Excel-taulukkoa suodattimena vaiheessa 10, Power Query viittaa taulukon arvoon suodatusehtona. Suora viittaus Excel-taulukkoon aiheuttaa virheen.

5. Valitse Aloitus>Sulje & Lataa,>Sulje & Lataa. Sinulla on nyt kyselyparametri nimeltä FilterCellValue, jota käytät vaiheessa 12.

6. Valitse Tietojen tuominen -valintaikkunassa Luo vain yhteys ja valitse sitten OK.

7. Avaa kysely, jonka haluat suodattaa, käyttämällä FilterCellValue-taulukon arvoa, joka on aiemmin ladattu Power Query -editorista, valitsemalla tiedoissa oleva solu ja valitsemalla sitten Kyselyn>muokkaus. Lisätietoja on artikkelissa Kyselyn luominen, lataaminen tai muokkaaminen Excelissä.

8. Suodata tiedot valitsemalla minkä tahansa sarakkeen otsikossa oleva suodatinnuoli ja valitse sitten suodatinkomento, kuten Tekstisuodattimet>alkaa. Suodata rivit -valintaikkuna tulee näkyviin.

9. Kirjoita Arvo-ruutuun mikä tahansa arvo, kuten "G", ja valitse sitten OK. Tässä tapauksessa arvo on tilapäinen paikkamerkki FilterCellValue-taulukon arvolle, jonka kirjoitat seuraavassa vaiheessa.

10. Tuo koko kaava näkyviin valitsemalla kaavarivin oikealla puolella oleva nuoli. Seuraavassa on esimerkki kaavassa olevasta suodatusehdosta:

    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

11. Valitse suodattimen arvo. Valitse kaavassa "G".

12. Kirjoita M Intellisensen avulla luomasi FilterCellValue-taulukon muutama ensimmäinen kirjain ja valitse se sitten näkyviin tulevasta luettelosta.

13. Valitse Aloitus,>Sulje,>Sulje & Lataa.

Tulos

Kysely suodattaa nyt kyselyn tulokset luomasi Excel-taulukon arvon perusteella. Jos haluat käyttää uutta arvoa, muokkaa alkuperäisen Excel-taulukon solun sisältöä vaiheessa 1, muuta "G" arvoksi "V" ja päivitä sitten kysely.

Parametrikyselyjen käytön hallinta

Voit valita, sallitaanko parametrikyselyt vai eivät.

1. Valitse Power Query -editori Tiedostoasetukset>jaAsetukset-kyselyvaihtoehdotPower >>Query -editori.
2. Valitse vasemman reunan ruudun YLEINEN-kohdassaPower Query -editori.
3. Valitse oikeanpuoleisen ruudun Parametrit-kohdassaSalli parametrit tai poista sen valinta tietolähteen ja muunnoksen valintaikkunoissa.

Katso myös

Microsoft Power Query for Excelin ohje

Kyselyparametrien käyttäminen (docs.com)