Možda ste dobro upoznati s parametarskim upitima uz njihovo korištenje u SQL-u ili programu Microsoft Query. No parametri Power Query sadrže ključne razlike:
- Parametre je moguće koristiti u svim koracima upita. Osim što funkcioniraju kao filtar podataka, parametri se mogu koristiti i za određivanje stavki kao što su put datoteke ili naziv poslužitelja.
- Parametri ne traže unos. Umjesto toga, njihovu vrijednost možete brzo promijeniti pomoću dodatka Power Query. Možete čak i pohraniti i dohvatiti vrijednosti iz ćelija u programu Excel.
- Parametri se spremaju u jednostavan parametarski upit, ali su odvojeni od podatkovnih upita u kojima se koriste. Nakon stvaranja u upite možete po potrebi dodavati parametar.
Napomena Ako želite drugi način stvaranja parametarskih upita, pročitajte članak Stvaranje parametarskog upita u programu Microsoft Query.
Stvaranje parametra
Pomoću parametara možete automatski promijeniti vrijednost upita i izbjeći svako uređivanje upita radi promjene vrijednosti. Samo je potrebno promijeniti vrijednost parametra. Kada stvorite parametar, on se sprema u poseban parametarski upit koji možete jednostavno promijeniti izravno iz programa Excel.
Odabir podataka>Dohvaćanje podataka>Drugi izvori>Pokrenite uređivač dodatka Power Query.
U uređivač dodatka Power Query odaberite Polazno>Upravljanje parametrima > Novi parametri.
U dijaloškom okviru Upravljanje parametrima odaberite Novo.
Po potrebi postavite sljedeće:
Naziv To bi trebalo odražavati funkciju parametra, ali neka bude što kraće. Opis On može sadržavati bilo kakve pojedinosti koje će korisnicima olakšati pravilnu upotrebu parametra. Obavezno Učinite nešto od sljedećeg:
Bilo koja vrijednost U parametarski upit možete unijeti bilo koju vrijednost bilo koje vrste podataka.
Popis vrijednosti Vrijednosti možete ograničiti na određeni popis tako da ih unesete u malu rešetku. Morate odabrati i zadanu vrijednost i trenutnu vrijednost u nastavku.
Upit Odaberite upit s popisom koji nalikuje strukturiranom stupcu popisa razdvojenom zarezima i zatvorenom u vitičaste zagrade.
Polje stanja problema, primjerice, može imati tri vrijednosti: {"Novo", "U tijeku", "Zatvoreno"}. Upit s popisom morate prethodno stvoriti tako da otvorite napredni uređivač (odaberite Početni>napredni uređivač), uklonite predložak koda, unesete popis vrijednosti u oblik popisa upita i odaberete Gotovo.
Kada završite sa stvaranjem parametra, u vrijednostima parametara prikazat će se upit popisa.Vrsta Određuje vrstu podataka parametra. Predložene vrijednosti Ako želite, dodajte popis vrijednosti ili navedite upit da biste predložili ulazne podatke. Zadana vrijednost Pojavljuje se samo ako je mogućnost Predložene vrijednostipostavljena na Popis vrijednosti i određuje koja je stavka popisa zadana. U tom slučaju morate odabrati zadanu vrijednost. Trenutna vrijednost Ako je parametar prazan, upit možda neće vratiti rezultate, ovisno o tome gdje koristite parametar. Ako je odabrana postavka Obavezno , trenutna vrijednost ne može biti prazna. Da biste stvorili parametar, odaberite U redu.
Promjena izvora podataka pomoću parametra
Evo kako možete upravljati promjenama mjesta izvora podataka i sprječavati pogreške prilikom osvježavanja. Uz pretpostavku, na primjer, da imate sličnu shemu i izvor podataka, stvorite parametar da biste jednostavno promijenili izvor podataka i spriječili pogreške prilikom osvježavanja podataka. Ponekad se mijenja poslužitelj, baza podataka, mapa, naziv datoteke ili mjesto. Možda upravitelj baze podataka povremeno zamijeni poslužitelj, CSV datoteke mjesečno ode u drugu mapu ili se pak morate jednostavno prebacivati između okruženja za razvoj/testiranje i proizvodnju.
Prvi korak: stvaranje parametarskog upita
U sljedećem primjeru nekoliko CSV datoteka uvozite pomoću operacije uvoza mape (Select Data>Get Data>From FilesFrom>Folder) iz mape C:\DataFilesCSV1. No ponekad se kao mjesto za ispuštanje datoteka povremeno koristi druga mapa, C:\DataFilesCSV2. Parametar u upitu možete koristiti kao zamjensku vrijednost za neku drugu mapu.
Odaberite Polazno>Upravljanje parametrima>Novi parametar.
U dijaloški okvir Upravljanje parametrima unesite sljedeće podatke:
Naziv CSVFileDrop Opis Alternativno mjesto za ispuštanje datoteke Obavezno Da Vrsta Tekst Predložene vrijednosti Bilo koja vrijednost Trenutna vrijednost C:\DataFilesCSV1 Odaberite U redu.
Drugi korak: dodavanje parametra u podatkovni upit
- Da biste postavili naziv mape kao parametar, u odjeljku Postavke upita u odjeljku Koraci upita odaberite Izvor, a zatim Uredi postavke.
- Provjerite je li mogućnost Put datoteke postavljena na Parametar, a zatim na padajućem izborniku odaberite parametar koji ste upravo stvorili.
- Odaberite U redu.
Treći korak: ažuriranje vrijednosti parametra
Mjesto mape upravo se promijenilo, pa sada možete jednostavno ažurirati parametarski upit.
- Odabir podatkovnih>veza & Upiti> na karticiUpiti, desnom tipkom miša kliknite parametarski upit, a zatim odaberite Uređivanje.
- Unesite novo mjesto u okvir Trenutna vrijednost , npr. C:\DataFilesCSV2.
- Odaberite Polazno>Zatvori & Učitaj.
- Da biste potvrdili rezultate, dodajte nove podatke u izvor podataka, a zatim osvježite podatkovni upit ažuriranim parametrom (Odaberiosvježisve podatke>).
Filtriranje podataka pomoću parametra
Ponekad morate na jednostavan način promijeniti filtar upita da biste dobili različite rezultate bez uređivanja upita ili izrade neznatno različitih kopija istoga upita. U ovom primjeru promijenili ćemo datum da bismo praktično promijenili filtar podataka.
Da biste otvorili upit, pronađite prethodno učitani iz uređivač dodatka Power Query, odaberite ćeliju u podacima, a zatim odaberite Uređivanje upita>. Dodatne informacije potražite u odjeljku Stvaranje, učitavanje i uređivanje upita u programu Excel.
Odaberite strelicu filtra u zaglavlju bilo kojeg stupca da biste filtrirali podatke, a zatim odaberite naredbu filtra kao što je Filtri>datuma/vremena nakon. Prikazat će se dijaloški okvir Filtriranje redaka .
Odaberite gumb s lijeve strane okvira Vrijednost , a zatim učinite nešto od sljedećeg:
- Da biste koristili postojeći parametar, odaberite parametar, a zatim na popisu koji će se pojaviti zdesna odaberite željeni parametar.
- Da biste koristili novi parametar, odaberite Novi parametar i stvorite parametar.
Unesite novi datum u okvir Trenutna vrijednost , a zatim odaberite Polazno>Zatvori & Učitaj.
Da biste potvrdili rezultate, dodajte nove podatke u izvor podataka, a zatim osvježite podatkovni upit ažuriranim parametrom (Odaberiosvježisve podatke>). Promijenite, primjerice, vrijednost filtra na neki drugi datum da biste vidjeli nove rezultate.
U okvir Trenutna vrijednost unesite novi datum.
Odaberite Polazno>Zatvori & Učitaj.
Da biste potvrdili rezultate, dodajte nove podatke u izvor podataka, a zatim osvježite podatkovni upit ažuriranim parametrom (Odaberiosvježisve podatke>).
Filtriranje podataka pomoću vrijednosti ćelije
U ovom se primjeru vrijednost parametra upita čita iz ćelije u radnoj knjizi. Ne morate mijenjati parametarski upit, samo ažurirajte vrijednost ćelije. Na primjer, želite filtrirati stupac prema prvom slovu, no jednostavno promijeniti njegovu vrijednost u bilo koje slovo od A do Ž.
Na radnom listu u radnoj knjizi u koju je učitan upit koji želite filtrirati stvorite tablicu programa Excel s dvije ćelije: zaglavljem i vrijednošću.
Mojfiltar G Odaberite ćeliju u tablici programa Excel, a zatim odaberiteDohvaćanje> podataka >iz tablice/raspona. Pojavit će se uređivač dodatka Power Query.
U okviru Naziv okna Postavke upita s desne strane promijenite naziv upita tako da bude smisleniji, primjerice FilterCellValue.
Da biste proslijedili vrijednost u tablici, a ne samu tablicu, desnom tipkom miša kliknite vrijednost u pretpregledu podataka, a zatim odaberite Pretraživanje kroz razine naniže.
Obratite pozornost na to da se formula promijenila u= #"Changed Type"{0}[MyFilter]
Kada tablicu programa Excel koristite kao filtar u 10. koraku, Power Query kao uvjet filtra referencira vrijednost iz tablice. Izravna referenca na tablicu programa Excel uzrokovala bi pogrešku.Odaberite Polazno,>Zatvori & Učitaj>,Zatvori & Učitaj u. Sada imate parametar upita naziva "FilterCellValue" koji ste koristili u 12. koraku.
U dijaloškom okviru Uvoz podataka odaberite mogućnost Samo stvori vezu, a zatim U redu.
Otvorite upit koji želite filtrirati pomoću vrijednosti u tablici VrijednostĆelijeFiltra, koja je prethodno uređivač dodatka Power Query, tako da odaberete ćeliju u podacima, a zatim Uređivanje upita>. Dodatne informacije potražite u odjeljku Stvaranje, učitavanje i uređivanje upita u programu Excel.
Odaberite strelicu filtra u zaglavlju bilo kojeg stupca da biste filtrirali podatke, a zatim odaberite naredbu filtra kao što je Filtri>teksta započinju sa. Prikazat će se dijaloški okvir Filtriranje redaka .
Unesite bilo koju vrijednost u okvir vrijednosti , npr. "G", a zatim odaberite U redu. U ovom slučaju vrijednost je privremeno rezervirano mjesto za vrijednost u tablici FilterCellValue koju unesete u sljedećem koraku.
Odaberite strelicu s desne strane trake formule da bi se prikazala cijela formula. Evo primjera uvjeta filtra u formuli:
= Table.SelectRows(#"Promijenjena vrsta", each Text.StartsWith([Naziv], "G"))
Odaberite vrijednost filtra. U formuli odaberite "G".
Pomoću značajke M Intellisense unesite prvih nekoliko slova tablice FilterCellValue koju ste stvorili, a zatim je odaberite na popisu koji će se pojaviti.
Odaberite Polazno,>Zatvori>,Zatvori & Učitaj.
Rezultat
U upitu se sada vrijednost iz tablice programa Excel koju ste stvorili koristi za filtriranje rezultata upita. Da biste koristili novu vrijednost, u prvom koraku uredite sadržaj ćelije u izvornoj tablici programa Excel, promijenite "G" u "V", a zatim osvježite upit.
Upravljanje korištenjem parametarskih upita
Možete odrediti jesu li parametarski upiti dopušteni ili nisu.
- U uređivač dodatka Power Query odaberite Mogućnosti datoteke>i postavke> Mogućnosti >upitauređivač dodatka Power Query.
- U oknu s lijeve strane u odjeljku GLOBALNO odaberite uređivač dodatka Power Query.
- U oknu s desne strane u odjeljku Parametri potvrdite ili poništite okvir Uvijek dopusti parametrizaciju u izvoru podataka i dijaloškim okvirima za transformaciju.