Stvaranje parametarskog upita (Power Query)

Pomoću parametra možete automatski promijeniti vrijednost u upitu i izbjeći uređivanje upita svaki put da biste promijenili vrijednost. Samo promijenite vrijednost parametra. Kada stvorite parametar, on se sprema u poseban parametarski upit koji možete jednostavno promijeniti izravno iz programa Excel.

1. Odaberite > Dohvati podatke > druge izvore >pokretanje uređivač dodatka Power Query.

2. U odjeljku uređivač dodatka Power Query odaberite Polazno > Upravljanje parametrima > novi parametri.

3. U dijaloškom okviru Upravljanje parametrima odaberite Novo.

4. Po potrebi postavite sljedeće:

   Naziv	To bi trebalo odražavati funkciju parametra, ali ga zadržati što kraće.
   Opis	To može sadržavati sve pojedinosti koje će korisnicima olakšati pravilno korištenje 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 itrenutnu vrijednost u nastavku. Upit Odaberite upit s popisom koji izgleda kao strukturirani stupac popisa odvojen zarezima i zagradama. Na primjer, polje Stanja problema može imati tri vrijednosti: {"Novo", "U tijeku", "Zatvoreno"}. Upit s popisom morate stvoriti unaprijed tako da otvorite napredni uređivač (odaberite Polazno > napredni uređivač), uklonite predložak koda, unesete popis vrijednosti u obliku popisa upita, a zatim odaberete Gotovo. Kada završite sa stvaranjem parametra, upit s popisom prikazuje se u vrijednostima parametara.
   Vrsta	To određuje vrstu podataka parametra.
   Predložene vrijednosti	Ako želite, dodajte popis vrijednosti ili navedite upit da biste naveli prijedloge za unos.
   Zadana vrijednost	To se prikazuje samo ako je predložene vrijednosti postavljeno na Popis vrijednosti i određuje koja je stavka popisa zadana. U tom slučaju morate odabrati zadanu vrijednost.
   Trenutna vrijednost	Ovisno o tome gdje koristite parametar, ako je to prazno, upit možda neće vratiti rezultate. Ako je odabrana mogućnost Obavezno, trenutna vrijednost ne može biti prazna.

5. Da biste stvorili parametar, odaberite U redu.

Evo kako upravljati promjenama mjesta izvora podataka i spriječiti pogreške osvježavanja. Pretpostavimo, primjerice, sličnu shemu i izvor podataka, stvorite parametar da biste jednostavno promijenili izvor podataka i spriječili pogreške osvježavanja podataka. Ponekad se poslužitelj, baza podataka, mapa, naziv datoteke ili mjesto mijenjaju. Možda upravitelj baze podataka povremeno zamijeni poslužitelj, mjesečna kap CSV datoteka prelazi u drugu mapu ili se morate jednostavno prebacivati između okruženja za razvoj/testiranje/produkciju.

Prvi korak: stvaranje parametarskog upita

U sljedećem primjeru imate nekoliko CSV datoteka koje uvozite pomoću operacije uvoza mapa (odaberite Podaci> Dohvati podatke> Iz datoteka > Iz mape) iz mape C:\DataFilesCSV1. No ponekad se druga mapa povremeno koristi kao mjesto za ispuštanje datoteka C:\DataFilesCSV2. Parametar u upitu možete koristiti kao zamjensku vrijednost za drugu mapu.

1. Odaberite Polazno > Upravljanje parametrima > novom parametru.

2. U dijaloški okvir Upravljanje parametrima unesite sljedeće podatke:

   Naziv	CSVFileDrop
   Opis	Mjesto za ispuštanje alternativnih datoteka
   Obavezno	Da
   Vrsta	Tekst
   Predložene vrijednosti	Bilo koja vrijednost
   Trenutna vrijednost	C:\DataFilesCSV1

3. Odaberite U redu.

Drugi korak: dodavanje parametra u podatkovni upit

1. Da biste postavili naziv mape kao parametar, u odjeljku Postavke upita u odjeljku Koraci upita odaberite Izvor, a zatim Uređivanje postavki.

2. Provjerite je li mogućnost Put datoteke postavljena na Parametar, a zatim s padajućeg popisa odaberite parametar koji ste upravo stvorili.

3. Odaberite U redu.

Treći korak: ažuriranje vrijednosti parametra

Mjesto mape se upravo promijenilo, pa sada možete jednostavno ažurirati parametarski upit.

1. Odaberite > Veze & upiti > upita, desnom tipkom miša kliknite parametarski upit, a zatim odaberite Uređivanje.

2. U okvir Trenutna vrijednost unesite novo mjesto , npr. C:\DataFilesCSV2.

3. Odaberite Polazno > Zatvori & Učitaj.

4. Da biste potvrdili rezultate, dodajte nove podatke u izvor podataka, a zatim osvježite podatkovni upit ažuriranim parametrom ( odaberite Podaci> Osvježi sve).

Ponekad želite jednostavan način promjene filtra upita radi dobivanja različitih rezultata bez uređivanja upita ili stvaranja neznatno različitih kopija istog upita. U ovom primjeru mijenjamo datum da bismo praktično promijenili filtar podataka.

1. Da biste otvorili upit, pronađite prethodno učitan iz uređivač dodatka Power Query, odaberite ćeliju u podacima, a zatim Upit> Uređivanje. Dodatne informacije potražite u članku Stvaranje, učitavanje ili uređivanje upita u programu Excel.

2. Odaberite strelicu filtra u bilo kojem zaglavlju stupca da biste filtrirali podatke, a zatim odaberite naredbu filtra, npr. Filtri datuma / vremena> nakon. Pojavit će se dijaloški okvir Filtriranje redaka.
   
   

3. Odaberite gumb lijevo od okvira Vrijednost , a zatim učinite nešto od sljedećeg:

   • Da biste koristili postojeći parametar, odaberite Parametar, a zatim s popisa koji se prikazuje s desne strane odaberite željeni parametar.

   • Da biste koristili novi parametar, odaberite Novi parametar, a zatim stvorite parametar.

4. U okvir Trenutna vrijednost unesite novi datum , a zatim odaberite Polazno > Zatvori & učitaj.

5. Da biste potvrdili rezultate, dodajte nove podatke u izvor podataka, a zatim osvježite podatkovni upit ažuriranim parametrom ( odaberite Podaci> Osvježi sve). Primjerice, promijenite vrijednost filtra u drugi datum da biste vidjeli nove rezultate.

6. Unesite novi datum u okvir Trenutna vrijednost.

7. Odaberite Polazno > Zatvori & Učitaj.

8. Da biste potvrdili rezultate, dodajte nove podatke u izvor podataka, a zatim osvježite podatkovni upit ažuriranim parametrom ( odaberite Podaci> Osvježi sve).

U ovom se primjeru vrijednost u parametru upita čita iz ćelije u radnoj knjizi. Ne morate mijenjati parametarski upit, već samo ažurirajte vrijednost ćelije. Želite, primjerice, filtrirati stupac po prvom slovu, ali jednostavno promijeniti vrijednost u bilo koje slovo od A do Ž.

1. Na radnom listu radne knjige u koju je učitan upit koji želite filtrirati stvorite tablicu programa Excel s dvije ćelije: zaglavljem i vrijednošću.
    

   MyFilter
   G

2. Odaberite ćeliju u tablici programa Excel, a zatim odaberite > Dohvati podatke > iz tablice/raspona. Prikazat će uređivač dodatka Power Query zaslona.

3. U okviru Naziv okna Postavke upita na desnoj strani promijenite naziv upita tako da bude smisleniji, kao što je FilterCellValue. 

4. 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žoj.

   Primijetit ćete da se formula promijenila u = #"Changed Type"{0}[MyFilter]

   Kada tablicu programa Excel koristite kao filtar u 10. koraku, Power Query vrijednost Tablica referencira kao uvjet filtra. Izravna referenca na tablicu programa Excel uzrokla bi pogrešku.

5. Odaberite Polazno > Zatvori & učitaj > zatvori & učitaj u. Sada imate parametar upita pod nazivom "FilterCellValue" koji koristite u 12. koraku.

6. U dijaloškom okviru Uvoz podataka odaberite Samo stvori vezu, a zatim U redu.

7. Otvorite upit koji želite filtrirati vrijednošću u tablici FilterCellValue, koji je prethodno učitan iz uređivač dodatka Power Query, odabirom ćelije u podacima, a zatim odabirom mogućnosti Upit> Uređivanje. Dodatne informacije potražite u članku Stvaranje, učitavanje ili uređivanje upita u programu Excel.

8. Odaberite strelicu filtra u bilo kojem zaglavlju stupca da biste filtrirali podatke, a zatim odaberite naredbu filtra, npr . Filtri teksta > počinje sa. Pojavit će se dijaloški okvir Filtriranje redaka. 

9. U okvir Vrijednost unesite bilo koju vrijednost, npr. "G", a zatim odaberite U redu. U tom je slučaju vrijednost privremeno rezervirano mjesto za vrijednost u tablici FilterCellValue koju unesete u sljedećem koraku.

10. Odaberite strelicu na desnoj strani trake formule da biste prikazali cijelu formulu. Evo primjera uvjeta filtra u formuli:
    
    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

11. Odaberite vrijednost filtra. U formuli odaberite "G".

12. Pomoću značajke M Intellisense unesite prvih nekoliko slova tablice FilterCellValue koju ste stvorili, a zatim je odaberite s popisa koji će se prikazati.

13. Odaberite Polazno > Zatvori > Zatvori & učitaj.

Rezultat

Upit sada koristi vrijednost u tablici programa Excel koju ste stvorili za filtriranje rezultata upita. Da biste koristili novu vrijednost, uredite sadržaj ćelije u izvornoj tablici programa Excel u 1. koraku, promijenite "G" u "V", a zatim osvježite upit.

Možete kontrolirati jesu li parametarski upiti dopušteni ili nisu dopušteni.

1. U uređivač dodatka Power Query odaberite Mogućnosti > mogućnosti i postavke >upita >uređivač dodatka Power Query.

2. U oknu s lijeve strane u odjeljku GLOBALNO odaberite uređivač dodatka Power Query.

3. U oknu na desnoj strani u odjeljku Parametri odaberite ili poništite okvir Uvijek dopusti parametarizaciju u dijaloškim okvirima izvora podataka i pretvorbe.