Galbūt jau žinote parametrų užklausas ir jas naudojate SQL arba "„Microsoft“ Query". Tačiau "„Power Query“" parametrai turi esminių skirtumų:
- Parametrus galima naudoti bet kuriame užklausos veiksme. Be duomenų filtro veikimo, parametrai gali būti naudojami nurodant tokius dalykus kaip failo kelias arba serverio vardas.
- Parametrai nereikalauja įvesties. Vietoj to, galite greitai pakeisti jų reikšmę naudodami "„Power Query“". Programoje "Excel" netgi galite saugoti ir nuskaityti langelių reikšmes.
- Parametrai įrašomi paprastoje parametrų užklausoje, tačiau yra atskirti nuo duomenų užklausų, kuriose jie naudojami. Sukūrę galite įtraukti parametrą į užklausas, jei reikia.
Atkreipkite dėmesį Jei norite kito būdo parametrų užklausoms kurti, žr . Parametro užklausos kūrimas "„Microsoft“ Query".
Parametro kūrimas
Galite naudoti parametrą, norėdami automatiškai pakeisti užklausos reikšmę, ir kiekvieną kartą neredaguoti užklausos, kad reikšmė būtų pakeista. Tiesiog pakeiskite parametro reikšmę. Kai sukuriate parametrą, jis įrašomas į specialią parametro užklausą, kurią galite patogiai pakeisti tiesiai iš "Excel".
Duomenų pasirinkimas >Gauti duomenis>Kiti šaltiniai>Paleiskite "„Power Query“ rengyklė".
"„Power Query“ rengyklė" pasirinkite Pagrindinis>Parametrų > tvarkymas Nauji parametrai.
Dialogo lange Parametrų tvarkymas pasirinkite Naujas.
Jei reikia, nustatykite šiuos parametrus:
Vardas Tai turėtų atspindėti parametro funkciją, tačiau ji turi būti kuo trumpesnė. Aprašymas Jame gali būti bet kokia informacija, kuri padės žmonėms teisingai naudoti parametrą. Privaloma Atlikite vieną iš šių veiksmų:
Bet kokia reikšmė Į parametro užklausą galite įvesti bet kokią bet kokio duomenų tipo reikšmę.
Reikšmių sąrašas Galite apriboti reikšmes iki konkretaus sąrašo, įvesdami jas į mažą tinklelį. Taip pat turite pasirinkti numatytąją ir dabartinę reikšmę .
Užklausa Pasirinkite sąrašo užklausą, kuri panaši į sąrašo struktūros stulpelį, atskirtą kableliais ir įtrauktą į riestinius skliaustus.
Pavyzdžiui, problemų būsenos laukas gali turėti tris reikšmes: {"Nauja", "Vykdoma", "Uždaryta"}. Turite iš anksto 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 ir 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ą, kuri pateiktų įvesties pasiūlymus. Numatytoji reikšmė Tai rodoma tik jei Siūlomos reikšmės nustatytos kaip Reikšmių sąrašas, ir nurodoma, kuris sąrašo elementas yra numatytasis. Tokiu atveju turite pasirinkti numatytąjį. Dabartinė reikšmė Atsižvelgiant į tai, kur naudojate parametrą, jei jis yra tuščias, užklausa gali pateikti nepateikti rezultatų. Jei pasirinkta Būtina , Dabartinė reikšmė negali būti tuščia. Norėdami sukurti parametrą, pasirinkite Gerai.
Parametro naudojimas duomenų šaltiniui keisti
Štai būdas valdyti duomenų šaltinių vietų keitimus ir padėti išvengti atnaujinimo klaidų. Pavyzdžiui, tarkime, naudojama panaši schema ir duomenų šaltinis, sukurkite parametrą, kad būtų lengviau pakeisti duomenų šaltinį ir išvengti duomenų atnaujinimo klaidų. Kartais pasikeičia serverio, duomenų bazės, aplanko, failo vardas arba vieta. Galbūt duomenų bazės tvarkytuvas retkarčiais pakeičia serverį, kas mėnesį CSV failai perkeliami į kitą aplanką arba jums reikia lengvai perjungti kūrimo / bandymo / gamybos aplinką.
1 veiksmas: parametro užklausos kūrimas
Šiame pavyzdyje turite kelis CSV failus, kuriuos importuojate naudodami importavimo aplanko operaciją (Select Data>Get Data>From FilesFrom>Folder) iš aplanko C:\DataFilesCSV1. Tačiau kartais failams pašalinti naudojamas kitas aplankas, C:\DataFilesCSV2. Užklausos parametrą galite naudoti kaip kito aplanko pakaitalą.
Pasirinkite Pagrindinis>Tvarkyti parametrus>Naujas parametras.
Dialogo lange Parametrų valdymas įveskite šią informaciją:
Vardas CSVFileDrop Aprašymas Alternatyvi failų perkėlimo vieta Privaloma Taip Tipas Tekstas Siūlomos reikšmės Bet kokia reikšmė Dabartinė reikšmė C:\DataFilesCSV1 Pažymėkite Gerai.
2 veiksmas: parametro įtraukimas į duomenų užklausą
- Norėdami nustatyti aplanko pavadinimą kaip parametrą, užklausos parametrų dalyje Užklausos veiksmai pasirinkite Šaltinis, tada pasirinkite Redaguoti parametrus.
- Įsitikinkite, kad parinktis Failo kelias nustatyta kaip Parametras, tada pasirinkite parametrą, kurį ką tik sukūrėte iš išplečiamojo sąrašo.
- Pažymėkite Gerai.
3 veiksmas: atnaujinkite parametro reikšmę
Aplanko vieta ką tik pasikeitė, todėl dabar galite tiesiog atnaujinti parametro užklausą.
- Pasirinkite skirtuką Duomenų>ryšiai & užklausos>užklausos , dešiniuoju pelės mygtuku spustelėkite parametro užklausą, tada pasirinkite Redaguoti.
- Įveskite naują vietą lauke Dabartinė reikšmė , pvz., C:\DataFilesCSV2.
- Pasirinkite Pagrindinis>, Uždaryti & Įkelti.
- Norėdami patvirtinti rezultatus, įtraukite naujų duomenų į duomenų šaltinį, tada atnaujinkite duomenų užklausą su atnaujintu parametru (Pasirinkite Atnaujinti>duomenis viską).
Parametro naudojimas duomenims filtruoti
Kartais norisi paprasto būdo pakeisti užklausos filtrą ir gauti skirtingus rezultatus neredaguojant užklausos ir nekuriant truputį skirtingų tos pačios užklausos kopijų. Šiame pavyzdyje keičiame datą, kad patogiai pakeistume duomenų filtrą.
Norėdami atidaryti užklausą, raskite anksčiau iš „Power Query“ rengyklė įkeltą užklausą, pažymėkite duomenų langelį ir pasirinkite Užklausos>redagavimas. Daugiau informacijos rasite Užklausos kūrimas, įkėlimas arba redagavimas programoje "Excel".
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 Eilučių filtravimas .
Pažymėkite mygtuką, esantį į kairę nuo lauko Reikšmė , tada atlikite vieną iš šių veiksmų:
- Norėdami naudoti esamą parametrą, pasirinkite Parametras, tada pasirinkite norimą parametrą iš dešinėje rodomo sąrašo.
- Norėdami naudoti naują parametrą, pasirinkite Naujas parametras, tada sukurkite parametrą.
Įveskite naują datą į laukelį Dabartinė reikšmė , tada pasirinkite Namų>uždarymas & Įkelti.
Norėdami patvirtinti rezultatus, įtraukite naujų duomenų į duomenų šaltinį, tada atnaujinkite duomenų užklausą su atnaujintu parametru (Pasirinkite Atnaujinti>duomenis viską). Pavyzdžiui, pakeiskite filtro reikšmę į kitą datą, kad pamatytumėte naujus rezultatus.
Lauke Dabartinė reikšmė įveskite naują datą.
Pasirinkite Pagrindinis>, Uždaryti & Įkelti.
Norėdami patvirtinti rezultatus, įtraukite naujų duomenų į duomenų šaltinį, tada atnaujinkite duomenų užklausą su atnaujintu parametru (Pasirinkite Atnaujinti>duomenis viską).
Langelio reikšmės naudojimas duomenims filtruoti
Šiame pavyzdyje užklausos parametro reikšmė skaitoma iš darbaknygės langelio. Nereikia keisti parametro užklausos, tiesiog reikia atnaujinti langelio reikšmę. Pavyzdžiui, norite filtruoti stulpelį pagal pirmąją raidę, bet lengvai pakeisti reikšmę į bet kurią raidę nuo A iki Z.
Darbaknygės, kurioje įkelta filtruoti užklausa, darbalapyje sukurkite "Excel" lentelę su dviem langeliais: antrašte ir reikšme.
Mano filtras G Pasirinkite langelį "Excel" lentelėje, tada pasirinkite Duomenys>Gauti duomenis>iš lentelės / diapazono. Rodoma „Power Query“ rengyklė.
Užklausos parametrų srities dešinėje esančiame lauke Pavadinimas pakeiskite užklausos pavadinimą į prasmingesnį, pvz., FilterCellValue.
Norėdami perduoti reikšmę lentelėje, o ne pačioje lentelėje, 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 naudojate "Excel" lentelę kaip filtrą, "„Power Query“" nurodo lentelės reikšmę kaip filtro sąlygą. Tiesioginė nuoroda į "Excel" lentelę sukeltų klaidą.Pasirinkite Pagrindinis>, Uždaryti & Įkelti>,Uždaryti & Įkelti. Dabar turite užklausos parametrą pavadinimu "FilterCellValue", kurį naudojate 12 veiksme.
Dialogo lange Duomenų importavimas pasirinkite Tik kurti ryšį, tada pasirinkite Gerai.
Atidarykite užklausą, kurią norite filtruoti naudodami reikšmę iš lentelės FilterCellValue, kuri buvo anksčiau įkelta iš „Power Query“ rengyklė, pažymėdami langelį duomenyse, o tada pasirinkdami Užklausos>redagavimas. Daugiau informacijos rasite Užklausos kūrimas, įkėlimas arba redagavimas programoje "Excel".
Bet kurio stulpelio antraštėje pasirinkite filtro rodyklę, kad filtruotumėte duomenis, tada pasirinkite filtro komandą, pvz., Teksto filtrai>prasideda nuo. Rodomas dialogo langas Eilučių filtravimas .
Lauke Reikšmė įveskite bet kokią reikšmę, pvz., "G", tada pasirinkite Gerai. Šiuo atveju reikšmė yra laikinas reikšmės, esančios lentelėje FilterCellValue, kurią įvedate kitame veiksme, vietos rezervavimo ženklas.
Norėdami rodyti visą formulę, pasirinkite rodyklę dešinėje formulės juostos pusėje. Štai formulės filtro sąlygos pavyzdys:
= Table.SelectRows(#"Pakeistas tipas", each Text.StartsWith([Name], "G"))
Pasirinkite filtro reikšmę. Formulėje pasirinkite "G".
Naudodami "M Intellisense", įveskite kelias pirmąsias sukurtos lentelės FilterCellValue raides ir pasirinkite ją iš pasirodžiusio sąrašo.
Pasirinkite Namas,>Uždaryti>, Uždaryti & Įkelti.
Rezultatas
Dabar užklausa naudos reikšmę iš sukurtos "Excel" lentelės užklausos rezultatams filtruoti. Norėdami naudoti naują reikšmę, atlikdami 1 veiksmą redaguokite pradinės "Excel" lentelės langelio turinį, pakeiskite "G" į "V", tada atnaujinkite užklausą.
Parametrų užklausų naudojimo valdymas
Galite nustatyti, ar parametrų užklausos leidžiamos, ar ne.
- "„Power Query“ rengyklė" pasirinkite Failo>parinktys ir parametrai>Užklausų parinktys>„Power Query“ rengyklė.
- Kairėje esančioje srityje, dalyje GLOBAL, pasirinkite „Power Query“ rengyklė.
- Dešinėje esančioje srityje, dalyje Parametrai, pasirinkite arba išvalykite parinktį Visada leisti parametrizavimą duomenų šaltinio ir transformavimo dialogo languose.