Võimalik, et olete parameetripäringutega sql-is või Microsoft Querys kasutamisega üsna tuttav. Power Query parameetritel on siiski põhierinevused.
- Parameetreid saab kasutada mis tahes päringuetapis. Lisaks andmefiltrina töötamisele saab parameetreid kasutada näiteks failitee või serverinime määramiseks.
- Parameetrid ei küsi sisendi kohta. Selle asemel saate nende väärtust kiiresti muuta, kasutades Power Query. Excelis saate lahtreid isegi talletada ja alla laadida.
- Parameetrid salvestatakse lihtsas parameetripäringus, kuid need on eraldatud andmepäringutest, milles neid kasutatakse. Pärast loomist saate vajaduse korral lisada päringutele parameetri.
Märkus Kui soovite parameetripäringute loomiseks teist viisi, lugege teemat Parameetripäringu loomine Microsoft Querys.
Parameetri loomine
Parameetri abil saate automaatselt muuta päringu väärtust ja vältida päringu redigeerimist iga kord väärtuse muutmiseks. Peate lihtsalt muutma parameetri väärtust. Pärast parameetri loomist salvestatakse see spetsiaalsesse parameetripäringusse, mida saate mugavalt muuta otse Excelist.
Valige Power Query redaktor DataGet DataOther SourcesLaunch (Andmete >toomine andmeallikad>>).
Valige Power Query redaktor Avaleht>Halda parameetreid > Uued parameetrid.
Valige dialoogiboksis Parameetri haldamine nupp Uus.
Seadke vastavalt vajadusele järgmine.
Nimi See peaks kajastama parameetri funktsiooni, kuid jääma võimalikult lühikeseks. Kirjeldus See võib sisaldada üksikasju, mis aitavad inimestel parameetrit õigesti kasutada. Nõutav Tehke ühte järgmistest.
Suvaline väärtus Parameetripäringusse saate sisestada mis tahes andmetüübi väärtuse.
Väärtuste loend Väärtusi saate piiritleda kindla loendiga, sisestades need väiksesse ruudustikku. Peate valima ka allpool vaikeväärtuse ja praeguse väärtuse .
Päring Valige loendipäring, mis sarnaneb komadega eraldatud ja looksulgudega ümbritsetud loendi struktureeritud veeruga.
Näiteks võib probleemide olekuväljal olla kolm väärtust: {"Uus", "Pooleli", "Suletud"}. Loendipäringu loomiseks avage esmalt Täpsem redaktor (valige Avaleht>Täpsem redaktor), eemaldage koodimall, sisestage väärtuste loend päringuloendi vormingus ja seejärel valige Valmis.
Kui olete parameetri loomise lõpetanud, kuvatakse loendipäring teie parameetriväärtustes.Tippige arvuti otsinguväljale See määrab parameetri andmetüübi. Soovitatud väärtused Soovi korral lisage väärtuste loend või määrake päring sisendisoovituste esitamiseks. Vaikeväärtus See kuvatakse ainult siis, kui sätte Soovitatud väärtused väärtuseks on seatud Väärtuste loend ja see määrab, milline loendiüksus on vaikeväärtus. Sel juhul peate valima vaikeväärtuse. Praegune väärtus Olenevalt sellest, kus te parameetrit kasutate, ei pruugi päring juhul, kui see on tühi, tulemeid tagastada. Kui valitud on Nõutav , ei tohi praegune väärtus olla tühi. Parameetri loomiseks valige OK.
Andmeallika muutmine parameetri abil
Siit leiate juhised andmeallikate asukohtade muudatuste haldamiseks ja värskendamistõrgete vältimiseks. Näiteks kui eeldate sarnast skeemi ja andmeallikat, looge parameeter, et andmeallikat oleks lihtne muuta ja vältida andmete värskendamise tõrkeid. Mõnikord muutub server, andmebaas, kaust, failinimi või asukoht. Võib-olla vahetab andmebaasihaldur aeg-ajalt serveri välja, igakuine CSV-failide tilk läheb teise kausta või peate hõlpsasti arendus-/testimis-/tootmiskeskkonda vaheldumisi aktiveerima.
1. juhis: parameetripäringu loomine
Järgmises näites on teil mitu CSV-faili, mille impordite impordikausta toimingu abil (Valige andmed>too andmed>kaustast Files>Kaustast C:\DataFilesCSV1). Mõnikord aga kasutatakse failide kukutamiseks asukohana mõnda muud kausta C:\DataFilesCSV2. Päringu parameetrit saate kasutada teise kausta asendusväärtusena.
Valige Avaleht>Halda parameetreid>Uus parameeter.
Sisestage dialoogiboksi Parameetri haldamine järgmine teave:
Nimi CSVFileDrop Kirjeldus Alternatiivne failide kukutamiskoht Nõutav Jah Tippige arvuti otsinguväljale Text (Tekst) Soovitatud väärtused Suvaline väärtus Praegune väärtus C:\DataFilesCSV1 Klõpsake nuppu OK.
2. juhis: parameetri lisamine andmepäringusse
- Kausta nime määramiseks parameetriks valige päringusätete jaotises Päringutoimingud suvand Allikas ja seejärel valige Redigeeri sätteid.
- Veenduge, et suvandi Faili tee väärtuseks oleks seatud Parameeter, ja seejärel valige ripploendist äsja loodud parameeter.
- Klõpsake nuppu OK.
3. juhis: parameetri väärtuse värskendamine
Kausta asukoht on just muutunud, nii et nüüd saate lihtsalt parameetripäringu värskendada.
- Valige Andmeühendused>& Päringupäringud>, paremklõpsake parameetripäringuiklit ja seejärel valige Redigeeri.
- Sisestage väljale Praegune väärtus uus asukoht, näiteks C:\DataFilesCSV2.
- Valige Avaleht>Sule & Laadi.
- Tulemite kinnitamiseks lisage andmeallikasse uued andmed ja seejärel värskendage andmepäring värskendatud parameetriga (valige Andmete>värskendamine kõik).
Andmete filtreerimine parameetri abil
Mõnikord soovite hõlpsalt muuta päringu filtrit, et saada erinevaid tulemeid päringut redigeerimata või teha samast päringust veidi erinevaid koopiaid. Selles näites muudame kuupäeva, et andmefiltrit oleks mugav muuta.
Päringu avamiseks otsige üles Power Query redaktor varem laaditud päring, valige andmetes lahter ja seejärel valige Päringu>redigeerimine. Lisateavet leiate teemast Päringu loomine, laadimine või redigeerimine Excelis.
Valige andmete filtreerimiseks mis tahes veerupäises filtrinool ja seejärel valige filtrikäsk (nt Kuupäeva-/kellaajafiltrid>pärast). Kuvatakse dialoogiboks Ridade filtreerimine .
Valige väljast Väärtus vasakul asuv nupp ja seejärel tehke ühte järgmistest.
- Olemasoleva parameetri kasutamiseks valige Parameeter ja seejärel valige paremal kuvatavast loendist soovitud parameeter.
- Uue parameetri kasutamiseks valige Uus parameeter ja seejärel looge parameeter.
Sisestage uus kuupäev väljale Praegune väärtus ja seejärel valige Nupp Sule &>Laadi.
Tulemite kinnitamiseks lisage andmeallikasse uued andmed ja seejärel värskendage andmepäring värskendatud parameetriga (valige Andmete>värskendamine kõik). Näiteks saate uute tulemite kuvamiseks muuta filtri väärtuseks mõne muu kuupäeva.
Sisestage uus kuupäev väljale Praegune väärtus .
Valige Avaleht>Sule & Laadi.
Tulemite kinnitamiseks lisage andmeallikasse uued andmed ja seejärel värskendage andmepäring värskendatud parameetriga (valige Andmete>värskendamine kõik).
Lahtriväärtuse kasutamine andmete filtreerimiseks
Selles näites loetakse päringuparameetri väärtus ette töövihiku lahtrist. Te ei pea parameetripäringuid muutma, vaid värskendate lihtsalt lahtri väärtust. Näiteks soovite filtreerida veergu esimese tähe järgi, kuid muuta selle väärtuse hõlpsalt mis tahes täheks A-st Y-ni.
Looge töövihikus, kuhu päring, mida soovite filtreerida, töölehel kahe lahtriga Exceli tabel: päis ja väärtus.
Minu filtreerija G Valige Exceli tabelis lahter ja seejärel valige Andmed>Too andmed>tabelist/vahemikust. Kuvatakse Power Query redaktor.
Muutke päringu nime parempoolse paani Päringusätted väljal Nimi, et päringu nimi oleks tähendusrikkam (nt FilterCellValue).
Tabelis oleva väärtuse, mitte tabeli enda edastamiseks paremklõpsake andmeeelvaates väärtust ja seejärel valige Süvitsiminek.
Pange tähele, et valem muutus= #"Changed Type"{0}[MyFilter]
Kui kasutate Exceli tabelit 10. juhises filtrina, Power Query viitab tabeli väärtusele filtritingimusena. Otseviide Exceli tabelile põhjustaks tõrke.Valige Avaleht>Sule & Laadi>sule & Laadi kohta. Nüüd on teil päringuparameeter nimega FilterCellValue, mida kasutate juhises 12.
Valige dialoogiboksis Andmete importimine suvand Ainult ühenduse loomine ja seejärel valige OK.
Avage päring, mida soovite filtreerida tabeli FilterCellValue (üks varem Power Query redaktor laaditud) väärtusega, valides andmetes lahtri ja seejärel käsu Päringu>redigeerimine. Lisateavet leiate teemast Päringu loomine, laadimine või redigeerimine Excelis.
Valige andmete filtreerimiseks mis tahes veerupäises filtrinool ja seejärel valige filtrikäsk, näiteks Tekstifiltrid>Algab. Kuvatakse dialoogiboks Ridade filtreerimine .
Sisestage väljale Väärtus mis tahes väärtus (nt "G") ja seejärel valige OK. Praegusel juhul on väärtus tabeli FilterCellValue järgmises etapis sisestatud väärtuse ajutine kohatäide.
Kogu valemi kuvamiseks valige valemiriba paremas servas olev nool. Siin on näide valemi filtritingimusest.
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Valige filtri väärtus. Valige valemis "G".
Sisestage funktsiooni M Intellisense abil loodud filterCellValue tabeli paar esimest tähte ja valige see kuvatavast loendist.
Valige &Avakuva>sule sule>.
Tulem
Teie päring kasutab nüüd loodud Exceli tabeli väärtust päringutulemite filtreerimiseks. Uue väärtuse kasutamiseks redigeerige 1. juhises algses Exceli tabelis lahtri sisu, muutke "G" väärtuseks "V" ja seejärel värskendage päring.
Parameetripäringute kasutamise juhtimine
Saate määrata, kas parameetripäringud on lubatud või mitte.
- Valige Power Query redaktor Failisuvandid>ja Sätted>Päringusuvandid>Power Query redaktor.
- Valige vasakpoolsel paanil jaotises GLOBAALNE väärtus Power Query redaktor.
- Märkige või tühjendage parempoolsel paanil jaotises Parameetrid ruut Luba andmeallikas ja teisendusdialoogides alati parameetreid.