Možda ste prilično upoznati sa parametarskim upitima kada se koriste u SQL-u ili programu Microsoft Query. Međutim, Power Query parametri imaju ključne razlike:
- Parametri se mogu koristiti u bilo kom koraku upita. Pored toga što parametri funkcionišu kao filter podataka, mogu se koristiti i za navođenje putanja datoteke ili imena servera.
- Parametri ne traže unos. Umesto toga, možete brzo da promenite njihovu vrednost pomoću programskog dodatka Power Query. Čak možete da skladištite i preuzimate vrednosti iz ćelija u programu Excel.
- Parametri se čuvaju u jednostavnom parametarskom upitu, ali su odvojeni od upita za podatke u kojima se koriste. Kada je kreiran, možete da dodate parametar u upite po potrebi.
Napomena Ako želite drugi način za kreiranje parametarskih upita, pogledajte članak " Kreiranje parametarskog upita u programu Microsoft Query".
Kreiranje parametra
Parametar možete da koristite da biste automatski promenili vrednost u upitu i izbegli uređivanje upita svaki put kada promenite vrednost. Samo menjate vrednost parametra. Kada kreirate parametar, on se čuva u posebnom parametarskom upitu koji možete jednostavno da promenite direktno iz programa Excel.
Izbor podataka>Get Data>Drugi izvori>Pokrenite Power Query uređivač.
U Power Query uređivač izaberite stavku "Početna>""Upravljanje parametrima>", "Novi parametri".
U dijalogu "Upravljanje parametrima " izaberite stavku "Novo".
Podesite sledeće po potrebi:
Ime Ovo bi trebalo da odražava funkciju parametra, ali neka bude što kraće. Opis On može da sadrži sve detalje koji će ljudima pomoći da ispravno koriste parametar. Obavezno Uradite nešto od sledećeg:
Bilo koja vrednost U parametarski upit možete da unesete bilo koju vrednost bilo kog tipa podataka.
Lista vrednosti Vrednosti možete da ograničite na određenu listu tako što ćete ih uneti u malu koordinatnu mrežu. Takođe morate da izaberete podrazumevanu vrednost i trenutnu vrednost ispod.
Upit Izaberite upit liste koji liči na strukturiranu kolonu liste razdvojenu zarezima i stavljenu u velike zagrade.
Na primer, polje "Status problema" može da ima tri vrednosti: {"Novo", "U toku", "Zatvoreno"}. Morate unapred da kreirate upit liste tako što ćete otvoriti Napredni uređivač (izaberite stavku>Napredni uređivač), ukloniti predložak koda, uneti listu vrednosti u format liste upita, a zatim izabrati Gotovo.
Kada završite sa kreiranjem parametra, upit liste se prikazuje u vrednostima parametara.Tip Ovo navodi tip podataka parametra. Predložene vrednosti Ako želite, dodajte listu vrednosti ili navedite upit da biste obezbedili predloge za unos. Podrazumevana vrednost Ovo se pojavljuje samo ako je Predložene vrednostipostavljeno na Listu vrednosti i navodi koja stavka liste je podrazumevana. U tom slučaju morate da odaberete podrazumevanu postavku. Trenutna vrednost U zavisnosti od toga gde koristite parametar, ako je prazan, upit možda neće vratiti rezultate. Ako je izabrana opcija "Obavezno ", trenutna vrednost ne može da bude prazna. Da biste kreirali parametar, kliknite na dugme "U redu".
Korišćenje parametra za promenu izvora podataka
Evo načina za upravljanje promenama lokacija izvora podataka i sprečavanje grešaka osvežavanja. Na primer, ako pretpostavimo sličnu šemu i izvor podataka, kreirajte parametar da biste lako promenili izvor podataka i doprineli sprečavanju grešaka pri osvežavanju podataka. Ponekad se menjaju server, baza podataka, fascikla, ime datoteke ili lokacija. Možda menadžer baze podataka povremeno zameni server, mesečna kap CSV datoteka ode u drugu fasciklu ili treba da se lako prebacujete između okruženja za razvoj/testiranje/proizvodnju.
1. korak: Kreiranje parametarskog upita
U sledećem primeru imate nekoliko CSV datoteka koje uvozite pomoću operacije uvoza fascikle (Select Data> GetData>From FilesFrom>Folder) iz fascikle C:\DataFilesCSV1. Ali ponekad se druga fascikla povremeno koristi kao lokacija za otpuštanje datoteka, C:\DataFilesCSV2. Parametar možete da koristite u upitu kao zamensku vrednost za drugu fasciklu.
Izaberite stavku Početak>Upravljanje parametrima>Novi parametar.
Unesite sledeće informacije u dijalog "Upravljanje parametrima ":
Ime CSVFileDrop Opis Alternativna lokacija za otpuštanje datoteka Obavezno Da Tip Tekst Predložene vrednosti Bilo koja vrednost Trenutna vrednost C:\DataFilesCSV1 Izaberite dugme U redu.
2. korak: Dodavanje parametra u upit za podatke
- Da biste podesili ime fascikle kao parametar, u okviru "Postavke upita", u okviru "Koraci upita" izaberite stavku "Izvor", a zatim stavku "Uredi postavke".
- Uverite se da je opcija "Putanja datoteke " postavljena na vrednost "Parametar", a zatim sa padajuće liste izaberite parametar koji ste upravo kreirali.
- Izaberite dugme U redu.
3. korak: ažuriranje vrednosti parametra
Lokacija fascikle se upravo promenila, tako da sada možete jednostavno da ažurirate parametarski upit.
- Izaberite karticu"Podaci za povezivanje" & "Upiti> upita", kliknite desnim tasterom miša na parametarski upit, a zatim izaberite stavku "Uredi".>
- Unesite novu lokaciju u polje " Trenutna vrednost", na primer C:\DataFilesCSV2.
- Izaberite stavku "Početak>","Zatvori & učitavanje".
- Da biste potvrdili rezultate, dodajte nove podatke u izvor podataka, a zatim osvežite upit podataka ažuriranim parametrom (Izaberite stavku "Osvežisve" podataka>).
Korišćenje parametra za filtriranje podataka
Ponekad vam je potreban jednostavan način za promenu filtera upita da biste dobili različite rezultate bez uređivanja upita ili pravljenja neznatno drugačijih kopija istog upita. U ovom primeru menjamo datum kako bismo promenili filter podataka.
Da biste otvorili upit, pronađite prethodno učitan iz Power Query uređivač, izaberite ćeliju u podacima, a zatim izaberite stavku "Uredi upit>". Dodatne informacije potražite u članku "Kreiranje, učitavanje ili uređivanje upita u programu Excel".
Izaberite strelicu filtera u bilo kom zaglavlju kolone da biste filtrirali podatke, a zatim izaberite komandu filtera, kao što je "Filteri>datuma/vremena posle". Pojavljuje se dijalog "Filtriranje redova ".
Kliknite na dugme sa leve strane polja " Vrednost ", a zatim uradite nešto od sledećeg:
- Da biste koristili postojeći parametar, izaberite parametar, a zatim izaberite željeni parametar sa liste koja se pojavljuje sa desne strane.
- Da biste koristili novi parametar, izaberite stavku "Novi parametar", a zatim kreirajte parametar.
Unesite novi datum u polje "Trenutna vrednost", a zatim izaberite stavku "Početak>""Zatvori & Učitaj".
Da biste potvrdili rezultate, dodajte nove podatke u izvor podataka, a zatim osvežite upit podataka ažuriranim parametrom (Izaberite stavku "Osvežisve" podataka>). Na primer, promenite vrednost filtera na drugi datum da biste videli nove rezultate.
Unesite novi datum u polje "Trenutna vrednost".
Izaberite stavku "Početak>","Zatvori & učitavanje".
Da biste potvrdili rezultate, dodajte nove podatke u izvor podataka, a zatim osvežite upit podataka ažuriranim parametrom (Izaberite stavku "Osvežisve" podataka>).
Korišćenje vrednosti ćelije za filtriranje podataka
U ovom primeru, vrednost parametra upita se čita iz ćelije u radnoj svesci. Ne morate da menjate parametarski upit, samo ažurirate vrednost ćelije. Na primer, želite da filtrirate kolonu po prvom slovu, ali da lako promenite vrednost u bilo koje slovo od A do Ž.
Na radnom listu u radnoj svesci u koji je učitan upit koji želite da filtrirate kreirajte Excel tabelu sa dve ćelije: zaglavljem i vrednošću.
Omiljeno G Izaberite ćeliju u Excel tabeli, a zatim izaberite stavku "Podaci>preuzimaju podatke>iz tabele/opsega". Pojavljuje se Power Query uređivač.
U polju " Ime " okna " Postavke upita " sa desne strane promenite ime upita tako da ima više smisla, kao što je "FilterCellValue".
Da biste prosledili vrednost u tabeli, a ne samu tabelu, kliknite desnim tasterom miša na vrednost u prikazu podataka "Pregled podataka", a zatim izaberite stavku "Dubinsko pretraživanje".
Primetićete da se formula promenila u= #"Changed Type"{0}[MyFilter]
Kad koristite Excel tabelu kao filter u 10. koraku, Power Query upućuje na vrednost tabele kao uslov filtera. Direktno upućivanje na Excel tabelu izazvalo bi grešku.Izaberite stavku Početak>, Zatvori & Učitaj,>Zatvori & Učitaj u. Sada imate parametar upita pod imenom "FilterCellValue" koji koristite u 12. koraku.
U dijalogu " Uvoz podataka " izaberite stavku " Samo kreiraj vezu", a zatim kliknite na dugme "U redu".
Otvorite upit koji želite da filtrirate sa vrednošću u tabeli FilterCellValue, koja je prethodno učitana iz Power Query uređivač, tako što ćete izabrati ćeliju u podacima, a zatim izabratistavku "Urediupit>". Dodatne informacije potražite u članku "Kreiranje, učitavanje ili uređivanje upita u programu Excel".
Izaberite strelicu filtera u bilo kom zaglavlju kolone da biste filtrirali podatke, a zatim izaberite komandu filtera, kao što je "Filteri> za tekstpočinju sa". Pojavljuje se dijalog "Filtriranje redova ".
Unesite bilo koju vrednost u polje za vrednost , na primer "G", a zatim kliknite na dugme "U redu". U ovom slučaju, vrednost je privremeni čuvar mesta za vrednost u tabeli FilterCellValue koju unosite u sledećem koraku.
Izaberite strelicu sa desne strane polja za formulu da biste prikazali celu formulu. Evo primera uslova filtera u formuli:
= Table.SelectRows(#"Promenjeni tip", each Text.StartsWith([Ime], "G"))
Izaberite vrednost filtera. U formuli izaberite "G".
Pomoću tehnologije M Intellisense unesite prvih nekoliko slova tabele FilterCellValue koju ste kreirali, a zatim je izaberite sa liste koja se pojavljuje.
Izaberite stavku Početak>,zatvorite>, zatvorite & učitavanje.
Rezultat
Upit sada koristi vrednost u Excel tabeli koju ste kreirali za filtriranje rezultata upita. Da biste koristili novu vrednost, uredite sadržaj ćelije u originalnoj Excel tabeli u 1. koraku, promenite "G" u "V", a zatim osvežite upit.
Kontrolisanje upotrebe parametarskih upita
Možete da kontrolišete da li su parametarski upiti dozvoljeni ili nisu.
- U Power Query uređivač izaberite stavke "Opcije datoteke>"i "Postavke>".Opcije> upita Power Query uređivač.
- U oknu sa leve strane, u okviru GLOBALNO izaberite stavku Power Query uređivač.
- U oknu sa desne strane, u okviru Parametri potvrdite ili opozovite izbor Uvek dozvoli parametrizaciju u izvoru podataka i dijalozima transformacije.