Parametro užklausos kūrimas ("Power Query")

Galite naudoti parametrą, kad automatiškai pakeistumėte reikšmę užklausoje ir kiekvieną kartą negalėtumėte redaguoti užklausos, kad pakeistumėte reikšmę. Tiesiog pakeiskite parametro reikšmę. Sukūrus parametrą, jis įrašomas į specialiojo parametro užklausą, kurią galite patogiai pakeisti tiesiogiai iš "Excel".

1. Pasirinkite Duomenų > Gauti duomenis > Kiti šaltiniai > Paleisti "Power Query" rengyklė.

2. "Power Query" rengyklė pasirinkite Pagrindinis > Tvarkyti parametrus > Naujus parametrus.

3. Dialogo lange Valdyti parametrą pasirinkite Naujas.

4. Jei reikia, nustatykite:

   Pavadinimas	Tai turėtų atspindėti parametro funkciją, bet palikti ją kuo trumpesnė.
   Aprašas	Tai gali apimti bet kokią informaciją, kuri padės žmonėms tinkamai naudoti parametrą.
   Būtina	Atlikite vieną iš šių veiksmų: Bet kuri reikšmė Galite įvesti bet kurio duomenų tipo reikšmę parametro užklausoje. Reikšmių sąrašas    Galite apriboti reikšmes iki konkretaus sąrašo įvesdami jas mažame tinklelyje. Taip pat turite pasirinkti numatytąją reikšmę ir dabartinę reikšmę . Užklausos Pasirinkite sąrašo užklausą, kuri panaši į sąrašo struktūrinį stulpelį, atskirtą kableliais ir apskliaustą riestiniais skliaustais. Pavyzdžiui, problemų būsenos lauke gali būti trys reikšmės: {"Naujas", "Tęstinis", "Uždaryta"}. Iš anksto turite sukurti sąrašo užklausą atidarydami patobulinta rengyklė (pasirinkite Pagrindinis > patobulinta rengyklė), pašalindami kodo šabloną, įvesdami reikšmių sąrašą užklausų sąrašo formatu, tada pasirinkdami Atlikta. Baigus kurti parametrą, sąrašo užklausa rodoma parametrų reikšmėse.
   Tipas	Tai nurodo parametro duomenų tipą.
   Siūlomos reikšmės	Jei norite, įtraukite reikšmių sąrašą arba nurodykite užklausą, kad pateiktumėte įvesties pasiūlymų.
   Numatytoji reikšmė	Tai rodoma tik tada, jei rekomenduojamų reikšmių reikšmė nustatyta kaip Reikšmių sąrašas ir nurodo, kuris sąrašo elementas yra numatytasis. Tokiu atveju turite pasirinkti numatytąją reikšmę.
   Dabartinė reikšmė	Atsižvelgiant į tai, kur naudojate parametrą, jei jis tuščias, užklausa gali nepateikti jokių rezultatų. Jei pažymėta Būtina , dabartinė reikšmė negali būti tuščia.

5. Norėdami sukurti parametrą, pasirinkite Gerai.

Štai kaip valdyti duomenų šaltinio vietų pakeitimus ir išvengti atnaujinimo klaidų. Pavyzdžiui, jei panaši schema ir duomenų šaltinis yra panašūs, sukurkite parametrą, kad lengvai pakeistumėte duomenų šaltinį ir išvengtumėte duomenų atnaujinimo klaidų. Kartais pasikeičia serveris, duomenų bazė, aplankas, failo vardas arba vieta. Galbūt duomenų bazės valdytojas kartais pakeičia serverį, mėnesio CSV failų kritimas patenka į kitą aplanką arba jums reikia lengvai perjungti kūrimo / testavimo / gamybos aplinką.

1 veiksmas: parametro užklausos kūrimas

Toliau pateiktame pavyzdyje yra keli CSV failai, kuriuos importuojate naudodami importavimo aplanko operaciją (Pasirinkite Duomenys > Gauti duomenis > Iš failų > iš aplanko) iš aplanko C:\DataFilesCSV1. Tačiau kartais kaip vieta failams numesti kartais naudojamas kitas aplankas, C:\DataFilesCSV2. Parametrą užklausoje galite naudoti kaip kito aplanko pakaitinę reikšmę.

1. Pasirinkite Pagrindinis > Tvarkyti parametrus > naują parametrą.

2. Dialogo lange Valdyti parametrą įveskite šią informaciją:

   Pavadinimas	CSV failo lašai
   Aprašas	Alternatyvi failų numetimo vieta
   Būtina	Taip
   Tipas	Teksto pranešimas
   Siūlomos reikšmės	Bet kuri reikšmė
   Dabartinė reikšmė	C:\DataFilesCSV1

3. Pasirinkite Gerai.

2 veiksmas: parametro įtraukimas į duomenų užklausą

1. Norėdami nustatyti aplanko pavadinimą kaip parametrą, dalyje Užklausos parametrai, dalyje Užklausos veiksmai pasirinkite Šaltinis, tada pasirinkite Redaguoti parametrus.

2. Įsitikinkite, kad failo kelio parinktis nustatyta kaip Parametras, tada išplečiamajame sąraše pasirinkite ką tik sukurtą parametrą.

3. Pasirinkite Gerai.

3 veiksmas: atnaujinkite parametro reikšmę

Aplanko vieta ką tik pasikeitė, todėl dabar galite tiesiog atnaujinti parametro užklausą.

1. Pasirinkite skirtuką Duomenų > ryšiai & užklausos > užklausos , dešiniuoju pelės mygtuku spustelėkite parametro užklausą, tada pasirinkite Redaguoti.

2. Lauke Dabartinė reikšmė įveskite naują vietą, pvz., C:\DataFilesCSV2.

3. Pasirinkite Pagrindinis > Uždaryti & Įkelti.

4. Norėdami patvirtinti rezultatus, įtraukite naujų duomenų į duomenų šaltinį, tada atnaujinkite duomenų užklausą naudodami atnaujintą parametrą (Pasirinkite Duomenys > Atnaujinti viską).

Kartais norite lengvai pakeisti užklausos filtrą, kad gautumėte skirtingus rezultatus nei redaguodami užklausą, nei padarydami šiek tiek skirtingas tos pačios užklausos kopijas. Šiame pavyzdyje pakeičiame datą, kad būtų patogiai keičiamas duomenų filtras.

1. Norėdami atidaryti užklausą, raskite anksčiau iš "Power Query" rengyklė įkeltą užklausą, pažymėkite duomenų langelį, tada pasirinkite Užklausos > Redaguoti. Daugiau informacijos žr. Užklausos kūrimas, įkėlimas arba redagavimas programoje "Excel".

2. Bet kurio stulpelio antraštėje pasirinkite filtro rodyklę, kad filtruotumėte duomenis, tada pasirinkite filtro komandą, pvz., Datos / laiko filtrai > Po. Rodomas dialogo langas Filtruoti eilutes .
   
   

3. Pasirinkite mygtuką lauko Reikšmė kairėje ir atlikite vieną iš šių veiksmų:

   • Norėdami naudoti esamą parametrą, pasirinkite Parametras, tada dešinėje rodomame sąraše pasirinkite norimą parametrą.

   • Norėdami naudoti naują parametrą, pasirinkite Naujas parametras, tada sukurkite parametrą.

4. Lauke Dabartinė reikšmė įveskite naują datą, tada pasirinkite Pagrindinis > Uždaryti & Įkelti.

5. Norėdami patvirtinti rezultatus, įtraukite naujų duomenų į duomenų šaltinį, tada atnaujinkite duomenų užklausą naudodami atnaujintą parametrą (Pasirinkite Duomenys > Atnaujinti viską). Pavyzdžiui, pakeiskite filtro reikšmę į kitą datą, kad pamatytumėte naujus rezultatus.

6. Lauke Dabartinė reikšmė įveskite naują datą.

7. Pasirinkite Pagrindinis > Uždaryti & Įkelti.

8. Norėdami patvirtinti rezultatus, įtraukite naujų duomenų į duomenų šaltinį, tada atnaujinkite duomenų užklausą naudodami atnaujintą parametrą (Pasirinkite Duomenys > Atnaujinti viską).

Šiame pavyzdyje užklausos parametro reikšmė nuskaitoma iš darbaknygės langelio. Nereikia keisti parametro užklausos, tiesiog atnaujinkite langelio reikšmę. Pavyzdžiui, norite filtruoti stulpelį pagal pirmąją raidę, bet lengvai pakeisti reikšmę į bet kurią raidę iš A į Z.

1. Darbaknygės darbalapyje, kuriame įkeliama norima filtruoti užklausa, sukurkite "Excel" lentelę su dviem langeliais: antrašte ir reikšme.
    

   Mano filtras
   G

2. Pasirinkite langelį "Excel" lentelėje, tada pasirinkite Duomenys > Gauti duomenis > iš lentelės / diapazono. Rodomas "Power Query" rengyklė.

3. Dešinėje esančios srities Užklausos parametrai lauke Pavadinimas pakeiskite užklausos pavadinimą į prasmingesnį, pvz., FilterCellValue. 

4. Norėdami perduoti reikšmę lentelėje, o ne pačią lentelę, dešiniuoju pelės mygtuku spustelėkite reikšmę duomenų peržiūroje, tada pasirinkite Detalizuoti.

   Atkreipkite dėmesį, kad formulė pakeista į = #"Changed Type"{0}[MyFilter]

   Kai 10 veiksme kaip filtrą naudojate "Excel" lentelę, "Power Query" nurodo lentelės reikšmę kaip filtro sąlygą. Dėl tiesioginės nuorodos į "Excel" lentelę gali įvykti klaida.

5. Pasirinkite Pagrindinis > Uždaryti & Įkelti > Uždaryti & Įkelti į. Dabar turite užklausos parametrą pavadinimu "FilterCellValue", kurį naudojate atlikdami 12 veiksmą.

6. Dialogo lange Duomenų importavimas pasirinkite Kurti tik ryšį, tada pasirinkite Gerai.

7. Atidarykite užklausą, kurią norite filtruoti naudodami lentelės FilterCellValue reikšmę, anksčiau įkeltą iš "Power Query" rengyklė, pažymėdami duomenų langelį, tada pasirinkdami Užklausos > Redaguoti. Daugiau informacijos žr. Užklausos kūrimas, įkėlimas arba redagavimas programoje "Excel".

8. Bet kurio stulpelio antraštėje pasirinkite filtro rodyklę, kad filtruotumėte duomenis, tada pasirinkite filtro komandą, pvz., Teksto filtrai > Prasideda. Rodomas dialogo langas Filtruoti eilutes . 

9. Lauke Reikšmė įveskite bet kokią reikšmę, pvz., "G", tada pasirinkite Gerai. Šiuo atveju reikšmė yra laikinas lentelės FilterCellValue reikšmės vietos rezervavimo ženklas, kurį įvedate atlikdami kitą veiksmą.

10. Pasirinkite dešinėje formulės juostos pusėje esančią rodyklę, kad būtų rodoma visa formulė. Štai filtro sąlygos formulėje pavyzdys:
    
    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

11. Pasirinkite filtro reikšmę. Formulėje pasirinkite "G".

12. Naudodami "M IntelliSense", įveskite kelias pirmąsias sukurtos lentelės FilterCellValue raides ir pasirinkite ją rodomame sąraše.

13. Pasirinkite Pagrindinis > Uždaryti > Uždaryti & Įkelti.

Rezultatas

Jūsų užklausa dabar naudoja reikšmę "Excel" lentelėje, kurią sukūrėte užklausos rezultatams filtruoti. Norėdami naudoti naują reikšmę, redaguokite pradinės "Excel" lentelės langelio turinį atlikdami 1 veiksmą, pakeiskite "G" į "V", tada atnaujinkite užklausą.

Galite nustatyti, ar leistinos parametrų užklausos, ar ne.

1. "Power Query" rengyklė pasirinkite Failo > parinktys ir parametrai > Užklausos parinktys > "Power Query" rengyklė.

2. Kairėje esančioje srityje, dalyje VISUOTINIS, pasirinkite "Power Query" rengyklė.

3. Dešinėje esančioje srityje, dalyje Parametrai pasirinkite arba išvalykite Visada leisti parametrizavimą duomenų šaltinio ir transformavimo dialogo languose.