Parametrikyselyn luominen (Power Query)

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

1. Valitse Tiedot > Nouda tiedot> Muut lähteet > Käynnistä Power Query -editori.

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

3. Valitse Parametrin hallinta -valintaikkunassa Uusi.

4. Määritä seuraavat asetukset tarpeen mukaan:

   Nimi	Tämän pitäisi kuvastaa parametrin funktiota, mutta pitää se mahdollisimman lyhyenä.
   Kuvaus	Tämä voi sisältää tietoja, joiden avulla käyttäjät voivat käyttää parametria oikein.
   Pakollinen	Tee jompikumpi seuraavista: Mikä tahansa arvo Voit kirjoittaa minkä tahansa tietotyypin arvon parametrikyselyyn. Arvoluettelo    Voit rajoittaa arvot tiettyyn luetteloon kirjoittamalla ne pieneen ruudukkoon. Sinun on myös valittava oletusarvo ja nykyinen arvo alla. Kyselyn Valitse luettelokysely, joka muistuttaa luettelorakenteista saraketta pilkuilla erotettuna ja aaltosulkeissa. Esimerkiksi Seurantakohteet-tilakentässä voi olla kolme arvoa: {"Uusi", "Jatkuva", "Suljettu"}. Luettelokysely on luotava etukäteen avaamalla Laajennettu editori (valitse Aloitus > Laajennettu editori), poistamalla koodimalli, kirjoittamalla arvoluettelo kyselyluettelomuodossa ja valitsemalla sitten Valmis. Kun olet luonut parametrin, luettelokysely näkyy parametriarvoissa.
   Tyyppi	Tämä määrittää parametrin tietotyypin.
   Ehdotetut arvot	Voit halutessasi lisätä arvoluettelon tai määrittää kyselyn, joka antaa ehdotuksia syötettä varten.
   Oletusarvo	Tämä näkyy vain, jos Ehdotetut arvot -asetuksena on Arvoluettelo ja se määrittää, mikä luettelokohde on oletusarvo. Tässä tapauksessa sinun on valittava oletusasetus.
   Nykyinen arvo	Jos tämä on tyhjä, kysely ei ehkä palauta tuloksia sen mukaan, missä parametria käytetään. Jos Pakollinen on valittuna, Nykyinen arvo ei voi olla tyhjä.

5. Luo parametri valitsemalla OK.

Näin voit hallita tietolähteiden sijaintien muutoksia ja estää päivitysvirheet. Jos esimerkiksi oletetaan, että samankaltainen rakenne ja tietolähde ovat samankaltaisia, luo parametri, jonka avulla voit helposti muuttaa tietolähdettä ja estää tietojen päivitysvirheet. Joskus palvelin, tietokanta, kansio, tiedoston nimi tai sijainti muuttuu. Tietokantapäällikkö voi ehkä joskus vaihtaa palvelinta, CSV-tiedostojen kuukausittainen pudotus siirtyy toiseen kansioon tai sinun on helppo siirtyä kehitys-, testi- ja tuotantoympäristön välillä.

Vaihe 1: Parametrikyselyn luominen

Seuraavassa esimerkissä sinulla on useita CSV-tiedostoja, jotka tuot tuontikansiotoiminnolla (Valitse Tiedot > Nouda tiedot > Tiedostoista > Kansiosta) kansiosta C:\DataFilesCSV1. Joskus tiedostojen pudottamiseen käytetään joskus toista kansiota, C:\DataFilesCSV2. Voit käyttää kyselyn parametria eri kansion korvaavana arvona.

1. Valitse Aloitus > Parametrien hallinta > Uusi parametri.

2. Kirjoita seuraavat tiedot Parametrin hallinta -valintaikkunaan:

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

3. Valitse OK.

Vaihe 2: Parametrin lisääminen tietokyselyyn

1. Jos haluat määrittää kansion nimen parametriksi, valitse Kyselyn asetukset -kohdan Kyselyn vaiheet -kohdassa Lähde ja valitse 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 Tiedot > Yhteydet & Kyselyt > Kyselyt-välilehti , napsauta parametrikyselyä hiiren kakkospainikkeella ja valitse sitten Muokkaa.

2. Kirjoita uusi sijainti Nykyinen arvo - ruutuun, kuten C:\DataFilesCSV2.

3. Valitse Aloitus > Sulje & Lataa.

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

Joskus haluat helpon tavan muuttaa kyselyn suodatinta, jotta saat erilaisia tuloksia muokkaamatta kyselyä tai tekemättä hieman erilaisia kopioita samasta kyselystä. Tässä esimerkissä muutamme päivämäärää niin, että tietosuodatinta muutetaan kätevästi.

1. Jos haluat avata kyselyn, etsi Power Query -editori aiemmin ladattu kysely, valitse tiedoista solu ja valitse sitten Kysely > Muokkaa. Lisätietoja on artikkelissa Kyselyn luominen, lataaminen tai muokkaaminen Excelissä.

2. Suodata tiedot valitsemalla minkä tahansa sarakeotsikon suodatinnuoli ja valitse sitten suodatinkomento, kuten Päivämäärä-/aikasuodattimet > Jälkeen. Näkyviin tulee Suodata rivit -valintaikkuna.
   
   

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

   • Jos haluat käyttää aiemmin luotua parametria, valitse Parametri ja valitse sitten haluamasi parametri oikealla olevasta 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äen sitten tietokyselyn päivitetyllä parametrilla (Valitse Tiedot > Päivitä kaikki). Voit esimerkiksi muuttaa suodatinarvon eri päivämääräksi, 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äen sitten tietokyselyn päivitetyllä parametrilla (Valitse Tiedot > Päivitä kaikki).

Tässä esimerkissä kyselyparametrin arvo luetaan työkirjan solusta. Sinun ei tarvitse muuttaa parametrikyselyä, päivität vain solun arvon. Haluat esimerkiksi suodattaa sarakkeen ensimmäisen kirjaimen mukaan, mutta muuttaa arvon helposti kirjaimeksi A–Ö.

1. Luo laskentataulukko työkirjassa, johon suodatettava kysely on ladattu, Excel-taulukko, jossa on kaksi solua: ylätunniste ja arvo.
    

   Omasuodatus
   G

2. Valitse solu Excel-taulukosta ja valitse sitten Tiedot > Nouda tiedot > Taulukosta tai alueelta. Näkyviin tulee Power Query -editori.

3. Muuta kyselyn nimi oikealla olevan Kyselyasetukset-ruudun Nimi-ruudussa kuvaavammaksi, kuten FilterCellValue. 

4. Jos haluat siirtää arvon taulukossa, ei itse taulukossa, napsauta arvoa hiiren kakkospainikkeella Tietojen esikatselussa ja valitse sitten Alirakenteeseen.

   Huomaa, että kaava muuttui = #"Changed Type"{0}[MyFilter]

   Kun käytät Excel-taulukkoa suodattimena vaiheessa 10, Power Query viittaa Taulukko-arvoon suodatinehtona. Suora viittaus Excel-taulukkoon aiheuttaisi virheen.

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

6. Valitse Tuo tiedot -valintaikkunassa Luo vain yhteys ja valitse sitten OK.

7. Avaa kysely, jonka haluat suodattaa SuodataSolunarvo -taulukon arvolla, joka on aiemmin ladattu Power Query -editori, valitsemalla solu tiedoista ja valitsemalla sitten Kysely > Muokkaa. Lisätietoja on artikkelissa Kyselyn luominen, lataaminen tai muokkaaminen Excelissä.

8. Suodata tiedot valitsemalla minkä tahansa sarakeotsikon suodatinnuoli ja valitse sitten suodatinkomento, kuten Tekstisuodattimet > Alkaa. Näkyviin tulee Suodata rivit -valintaikkuna. 

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

10. Tuo koko kaava näkyviin valitsemalla kaavarivin oikeassa reunassa oleva nuoli. Seuraavassa on esimerkki kaavan suodatinehdosta:
    
    = 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 käyttää nyt luomasi Excel-taulukon arvoa kyselyn tulosten suodattamiseen. Jos haluat käyttää uutta arvoa, muokkaa alkuperäisen Excel-taulukon solun sisältöä vaiheessa 1, muuta G-kirjaimeksi V ja päivitä sitten kysely.

Voit määrittää, sallitaanko parametrikyselyt vai ei.

1. Valitse Power Query -editori Tiedostojen > asetukset ja Asetukset > kyselyasetukset > Power Query -editori.

2. Valitse vasemmanpuoleisessa ruudussa YLEINEN-kohdassaPower Query -editori.

3. Valitse oikeanpuoleisessa ruudussa Parametrit-kohdassaSalli parametrien lisääminen aina tietolähde- ja muunnosvalintaikkunoiden avulla tai poista sen valinta.