Morda ste dobro seznanjeni s parametrskimi poizvedbami z njihovo uporabo v SQL ali Microsoft Query. Vendar pa imajo parametri Power Query ključne razlike:
- Parametre lahko uporabite v katerem koli koraku poizvedbe. Poleg tega, da delujejo kot podatkovni filter, lahko parametre uporabite tudi za določanje stvari, kot so pot datoteke ali ime strežnika.
- Parametri ne zahtevajo vnosa. Namesto tega lahko hitro spremenite njihovo vrednost z dodatkom Power Query. Vrednosti lahko celo shranite in pridobite iz celic v Excelu.
- Parametri so shranjeni v preprosti poizvedbi s parametri, vendar so ločeni od podatkovnih poizvedb, v katerih so uporabljeni. Ko je ustvarjen, lahko poizvedbam po potrebi dodate parameter.
Opomba Če želite ustvariti poizvedbe s parametri na drug način, glejte Ustvarjanje poizvedbe s parametri v programu Microsoft Query.
Ustvarjanje parametra
S parametrom lahko samodejno spremenite vrednost v poizvedbi in se izognete vsakičnemu urejanju poizvedbe, da bi spremenili vrednost. Samo spremenite vrednost parametra. Ko ustvarite parameter, se shrani v posebno poizvedbo za parametre, ki jo lahko priročno spremenite neposredno iz Excela.
Izberite Podatki>Pridobi podatke>Drugi viri>Zaženite urejevalnik Power Query.
V urejevalniku Power Query izberite Domov>Upravljanje parametrov > Novi parametri.
V pogovornem oknu Upravljanje parametrov izberite Novo.
Po potrebi nastavite naslednje:
Ime To bi moralo odražati funkcijo parametra, vendar naj bo čim krajše. Opis To lahko vsebuje vse podrobnosti, ki bodo ljudem pomagale pravilno uporabljati parameter. Obvezno Naredite nekaj od tega:
Katera koli vrednost V poizvedbo parametrov lahko vnesete poljubno vrednost katerega koli podatkovnega tipa.
Seznam vrednot Vrednosti lahko omejite na določen seznam tako, da jih vnesete v majhno mrežo. Spodaj morate izbrati tudi privzeto vrednost in trenutno vrednost .
Poizvedba Izberite poizvedbo seznama, ki je podobna strukturiranemu stolpcu seznama , ločenemu z vejicami in zaprtemu v oklepaje.
Polje »Stanje težav« ima lahko na primer tri vrednosti: {»Novo«, »V teku«, »Zaprto«}. Poizvedbo s seznamom morate vnaprej ustvariti tako, da odprete napredni urejevalnik (izberite>napredni urejevalnik), odstranite predlogo kode, vnesete seznam vrednosti v obliki seznama poizvedbe in nato izberete Dokončano.
Ko končate z ustvarjanjem parametra, je poizvedba seznama prikazana v vrednostih parametrov.Vrsta To določa podatkovni tip parametra. Priporočene vrednosti Po želji dodajte seznam vrednosti ali določite poizvedbo, da ponudite predloge za vnos. Privzeta vrednost To se prikaže le, če je možnost Predlagane vrednostinastavljena na Seznam vrednosti in določa, kateri element seznama je privzeti. V tem primeru morate izbrati privzeto. Trenutna vrednost Če je parameter prazen, poizvedba morda ne bo vrnila rezultatov, odvisno od tega, kje uporabljate parameter. Če je izbrana možnost Zahtevano , možnost Trenutna vrednost ne more biti prazna. Če želite ustvariti parameter, izberite V redu.
Uporaba parametra za spreminjanje vira podatkov
Tukaj je opisan način za upravljanje sprememb lokacij virov podatkov in preprečevanje napak pri osveževanju. Če na primer predpostavimo, da imate podobno shemo in vir podatkov, ustvarite parameter za preprosto spreminjanje vira podatkov in preprečevanje napak pri osveževanju podatkov. Včasih se spremeni strežnik, zbirka podatkov, mapo, ime datoteke ali mesto. Morda upravitelj baze podatkov občasno zamenja strežnik, mesečni padec datotek CSV gre v drugo mapo ali pa morate enostavno preklapljati med razvojnim/testnim/produkcijskim okoljem.
1. korak: Ustvarjanje poizvedbe s parametri
V tem primeru imate več datotek CSV, ki jih uvozite s postopkom uvoza mape (Select Data>Get>Data From FilesFrom>Folder) iz mape C:\DataFilesCSV1. Včasih pa se kot mesto za spuščanje datotek občasno uporabi druga mapa, C:\DataFilesCSV2. Parameter v poizvedbi lahko uporabite kot nadomestno vrednost za drugo mapo.
Izberite Domov>Upravljanje parametrov>Nov parameter.
V pogovorno okno Upravljanje parametrov vnesite te podatke:
Ime CSVFileDrop Opis Nadomestno mesto za spuščanje datoteke Obvezno Da Vrsta Text (Besedilo) Priporočene vrednosti Katera koli vrednost Trenutna vrednost C:\DataFilesCSV1 Izberite V redu.
2. korak: Dodajanje parametra v podatkovno poizvedbo
- Če želite nastaviti ime mape kot parameter, v razdelku Nastavitve poizvedbe v razdelku Koraki poizvedbe izberite Vir in nato Uredi nastavitve.
- Prepričajte se, da je možnost Pot datoteke nastavljena na Parameter, nato pa na spustnem seznamu izberite parameter, ki ste ga pravkar ustvarili.
- Izberite V redu.
3. korak: posodobite vrednost parametra
Mesto mape se je pravkar spremenilo, zato lahko zdaj preprosto posodobite poizvedbo s parametri.
- Izberite Zavihek Podatkovne>povezave &Poizvedbe poizvedbe>, z desno tipko miške kliknite poizvedbo s parametri in nato izberite Uredi.
- V polje Trenutna vrednost vnesite novo mesto, na primer C:\DataFilesCSV2.
- Izberite Domov>Zapri & Nalaganje.
- Če želite potrditi rezultate, dodajte nove podatke v vir podatkov in nato osvežite podatkovno poizvedbo s posodobljenim parametrom (IzberiteOsvežipodatke> vse).
Uporaba parametra za filtriranje podatkov
Včasih želite spremeniti filter poizvedbe na preprost način, da dobite različne rezultate, ne da bi morali poizvedbo urediti ali ustvariti nekoliko drugačne kopije iste poizvedbe. V tem primeru spremenimo datum, da priročno spremenimo podatkovni filter.
Če želite odpreti poizvedbo, poiščite poizvedbo, ki je bila predhodno naložena iz urejevalnika Power Query, izberite celico v podatkih in nato izberite Uredi poizvedbo>. Če želite več informacij, glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excelu.
Izberite puščico filtra v glavi katerega koli stolpca, da filtrirate podatke, nato pa izberite ukaz filtra, na primer Filteri>datuma/časa po. Prikaže se pogovorno okno Filtriraj vrstice .
Izberite gumb levo od polja Vrednost in naredite nekaj od tega:
- Če želite uporabiti obstoječi parameter, izberite Parameter in nato na seznamu, ki je prikazan na desni, izberite želeni parameter.
- Če želite uporabiti nov parameter, izberite Nov parameter in nato ustvarite parameter.
V polje Trenutna vrednost vnesite nov datum in nato izberite Osnovno>Zapri & naloži.
Če želite potrditi rezultate, dodajte nove podatke v vir podatkov in nato osvežite podatkovno poizvedbo s posodobljenim parametrom (IzberiteOsvežipodatke> vse). Če želite na primer prikazati nove rezultate, spremenite vrednost filtra na drug datum.
Vnesite nov datum v polje Trenutna vrednost .
Izberite Domov>Zapri & Nalaganje.
Če želite potrditi rezultate, dodajte nove podatke v vir podatkov in nato osvežite podatkovno poizvedbo s posodobljenim parametrom (IzberiteOsvežipodatke> vse).
Uporaba vrednosti celice za filtriranje podatkov
V tem primeru je vrednost v parametru poizvedbe prebrana iz celice v delovnem zvezku. Ni vam treba spreminjati poizvedbe s parametri, samo posodobite vrednost celice. Stolpec želite na primer filtrirati po prvi črki, vendar preprosto spremenite vrednost v poljubno črko od A do Ž.
Na delovnem listu v delovnem zvezku, v katerem je naložena poizvedba, ki jo želite filtrirati, ustvarite Excelovo tabelo z dvema celicama: glavo in vrednostjo.
MyFilter G Izberite celico v Excelovi tabeli, nato pa izberite »Dobi>podatke>iz tabele/obsega«. Prikaže se urejevalnik Power Query.
V polju »Ime « v podoknu z nastavitvami poizvedbe na desni strani spremenite ime poizvedbe tako, da bo bolj smiselno, na primer »FilterCellValue«.
Če želite posredovati vrednost v tabeli in ne tabele, jo kliknite z desno tipko miške v predogledu podatkov in izberite »Prikaz na ravni z več podrobnostmi«.
Opazili boste, da se je formula spremenila v= #"Changed Type"{0}[MyFilter]
Ko v 10. koraku uporabite Excelovo tabelo kot filter, se Power Query sklicuje na vrednost tabele kot na pogoj za filter. Neposredna referenca na Excelovo tabelo bi povzročila napako.Izberite »Osnovno>« Zapri & naloži>Zapri & naloži. Zdaj imate parameter poizvedbe z imenom »FilterCellValue«, ki ga uporabite v 12. koraku.
V pogovornem oknu »Uvoz podatkov « izberite »Ustvari samo povezavo« in nato izberite »V redu«.
Odprite poizvedbo, ki jo želite filtrirati z vrednostjo v tabeli »FilterCellValue«, ki je bila že naložena iz urejevalnik Power Query, in sicer tako, da izberete celico v podatkih in nato »Urejanje poizvedbe«>. Če želite več informacij , glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excelu.
Izberite puščico filtra v kateri koli glavi stolpca, s katero želite filtrirati podatke, in nato izberite ukaz filtra, kot je »Filter> besedilase začne s«. Prikaže se pogovorno okno » Vrstice filtra «.
Vnesite katero koli vrednost v polje za vrednost, na primer »G«, in nato izberite »V redu«. V tem primeru je vrednost začasna ograda za vrednost v tabeli »FilterCellValue«, ki jo vnesete v naslednjem koraku.
Izberite puščico na desni strani vnosne vrstice, da prikažete celotno formulo. Tu je primer pogoja filtra v formuli:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Izberite vrednost filtra. V formuli izberite "G".
Z ukazom M Intellisense vnesite prvih nekaj črk tabele »FilterCellValue«, ki ste jo ustvarili, in jo nato izberite na seznamu, ki se pojavi.
Izberite »Osnovno>«, zaprite>& nalaganje.
Rezultat
Poizvedba zdaj uporabi vrednost v Excelovi tabeli, ki ste jo ustvarili, za filtriranje rezultatov poizvedbe. Če želite uporabiti novo vrednost, uredite vsebino celice v izvirni Excelovi tabeli v 1. koraku, spremenite "G" v "V" in nato osvežite poizvedbo.
Nadzor uporabe poizvedb s parametri
Nadzirate lahko, ali so poizvedbe s parametri dovoljene ali ne.
- V urejevalniku Power Query izberite »Možnosti datoteke>«in »Nastavitve>«Možnosti> poizvedbe urejevalnik Power Query.
- V podoknu na levi strani, v razdelku GLOBALNO izberite urejevalnik Power Query.
- V podoknu na desni strani, v razdelku »Parametri« potrdite ali počistite polje »Vedno dovoli parametrizacijo« v pogovornih oknih vira podatkov in za pretvorbo.