Morda dobro poznate poizvedbe s parametri z njihovo uporabo v strežniku SQL ali Microsoft Query. Vendar pa se parametri Power Query bistveno razlikujejo:
- Parametre lahko uporabite v katerem koli koraku poizvedbe. Poleg tega, da parametri delujejo kot podatkovni filter, jih lahko uporabite tudi za določanje stvari, kot sta pot datoteke ali ime strežnika.
- Parametri ne pozivajo k vnosu. Namesto tega lahko hitro spremenite njihovo vrednost z dodatkom Power Query. Lahko celo shranite in pridobite vrednosti 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 po potrebi dodate parameter poizvedbam.
Opomba Če želite drugačen način ustvarjanja poizvedb s parametri, glejte »Ustvarjanje poizvedbe s parametri« v Microsoft Query.
Ustvarjanje parametra
S parametrom lahko samodejno spremenite vrednost v poizvedbi, tako da se izognete vsakokrskemu urejanju poizvedbe, da spremenite vrednost. Spremenite le vrednost parametra. Ko ustvarite parameter, je ta shranjen v posebno poizvedbo s parametri, ki jo lahko preprosto spremenite neposredno v Excelu.
Izberite podatke>Pridobivanje podatkov>Drugi viri>Zaženite urejevalnik Power Query.
V urejevalniku Power Query izberite Domača stran>Upravljanje parametrov > Novi parametri.
V pogovornem oknu »Upravljanje parametrov « izberite »Novo«.
Po potrebi nastavite naslednje:
Ime To mora odražati funkcijo parametra, vendar naj bo čim krajša. Opis Ta lahko vsebuje katere koli podrobnosti, ki bodo uporabnikom pomagale pravilno uporabiti parameter. Obvezno Naredite nekaj od tega:
Poljubna vrednost V poizvedbo s parametri lahko vnesete katero koli vrednost poljubnega podatkovnega tipa.
Seznam vrednosti Vrednosti lahko omejite na določen seznam tako, da jih vnesete v majhno mrežo. Spodaj morate izbrati tudi privzeto intrenutno vrednost .
Poizvedba Izberite poizvedbo s seznamom, ki je podobna strukturiranemu stolpcu seznama , ki je ločen z vejicami in obdan z zavitimi oklepaji.
V polju »Stanje težave« so lahko na primer tri vrednosti: {"Novo", "V teku", "Zaprto"}. Poizvedbo seznama morate najprej 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 ustvarite parameter, je poizvedba s seznamom prikazana v vrednosti parametra.Vrsta To določa podatkovni tip parametra. Predlagane vrednosti Če želite, dodajte seznam vrednosti ali določite poizvedbo, da ponudite predloge za vnos. Privzeta vrednost To je prikazano le, če je možnost »Predlagane vrednosti « nastavljena na »Seznam vrednosti« in določa, kateri element seznama je privzeti. V tem primeru morate izbrati privzeto možnost. Trenutna vrednost Če je parameter prazen, poizvedba morda ne bo vrnila nobenih rezultatov, odvisno od tega, kje ga uporabite. Če je izbrana možnost »Obvezno «, polja »Trenutna vrednost « ne morejo biti prazna. Če želite ustvariti parameter, izberite »V redu«.
Spreminjanje vira podatkov s parametrom
Tukaj je opisan način, kako upravljate spremembe mest virov podatkov in preprečite napake pri osveževanju. Če na primer privzamete podobno shemo in vir podatkov, ustvarite parameter, s katerim boste preprosto spremenili vir podatkov in preprečili napake pri osveževanju podatkov. Včasih se spremeni strežnik, zbirka podatkov, mapa, ime datoteke ali mesto. Morda upravitelj zbirke podatkov občasno zamenja strežnik, mesečna kapljica datotek CSV gre v drugo mapo ali pa morate preprosto preklopiti med razvojnim/preskusnim/produkcijskim okoljem.
1. korak: Ustvarite poizvedbo s parametri
V tem primeru imate več datotek CSV, ki jih uvozite s postopkom uvoza mape (Select Data>>Get Data From FilesFrom>) iz mape C:\DataFilesCSV1. Včasih pa se kot mesto za odlaganje datotek občasno uporabi druga mapa, C:\DataFilesCSV2. Parameter v poizvedbi lahko uporabite kot nadomestno vrednost za drugo mapo.
Izberite »Osnovno«>Upravljanje parametrov>»Nov parameter«.
V pogovorno okno »Upravljanje parametrov « vnesite te podatke:
Ime CSVFileDrop Opis Nadomestno mesto za opustitev datoteke Obvezno Da Vrsta Text (Besedilo) Predlagane vrednosti Poljubna vrednost Trenutna vrednost C:\DataFilesCSV1 Izberite V redu.
2. korak: dodajanje parametra v podatkovno poizvedbo
- Če želite nastaviti ime mape kot parameter, v nastavitvah poizvedbe v razdelku Koraki poizvedbe izberite »Vir« in nato » Uredi nastavitve«.
- Prepričajte se, da je pot datoteke nastavljena na »Parameter« in nato na spustnem seznamu izberite parameter, ki ste ga pravkar ustvarili.
- Izberite V redu.
3. korak: Posodobitev vrednosti parametra
Mesto mape se je pravkar spremenilo, tako da lahko zdaj preprosto posodobite poizvedbo s parametri.
- Select Data>Connections & Queries>Queries tab , z desno tipko miške kliknite poizvedbo s parametri in nato izberite »Uredi«.
- Vnesite novo mesto v polje » Trenutna vrednost «, na primer C:\DataFilesCSV2.
- Izberite »Osnovno>« Zapri & naloži.
- Če želite potrditi rezultate, dodajte nove podatke v vir podatkov, nato pa osvežite podatkovno poizvedbo s posodobljenim parametrom (Select>Data Refresh All).
Filtriranje podatkov s parametrom
Včasih želite preprosto spremeniti filter poizvedbe tako, da pridobite različne rezultate, ne da bi morali poizvedbo urejati ali ustvarjati nekoliko različne kopije iste poizvedbe. V tem primeru spremenimo datum, da priročno spremenimo filter podatkov.
Če želite odpreti poizvedbo, poiščite poizvedbo, ki je bila prej naložena iz urejevalnik Power Query, izberite celico v podatkih in nato izberite »Urejanjepoizvedbe>«. Če želite več informacij , glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excelu.
Izberite puščico filtra v kateri koli glavi stolpca, da filtrirate podatke, in nato izberite ukaz filtra, kot je Datum/čas filtrira>po. Prikaže se pogovorno okno » Vrstice filtra «.
Izberite gumb levo od polja » Vrednost « in naredite nekaj od tega:
- Če želite uporabiti obstoječi parameter, izberite parameter in nato na seznamu na desni izberite želeni parameter.
- Če želite uporabiti nov parameter, izberite »Nov parameter« in nato ustvarite parameter.
Vnesite nov datum v polje »Trenutna vrednost« in izberite »Osnovno>«Zapri & naloži.
Če želite potrditi rezultate, dodajte nove podatke v vir podatkov, nato pa osvežite podatkovno poizvedbo s posodobljenim parametrom (Select>Data Refresh All). Če na primer spremenite vrednost filtra na drug datum, da si ogledate nove rezultate.
Vnesite nov datum v polje » Trenutna vrednost« .
Izberite »Osnovno>« Zapri & naloži.
Če želite potrditi rezultate, dodajte nove podatke v vir podatkov, nato pa osvežite podatkovno poizvedbo s posodobljenim parametrom (Select>Data Refresh All).
Filtriranje podatkov z vrednostjo celice
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. Na primer, stolpec želite filtrirati po prvi črki, vendar preprosto spremeniti 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.