Stvaranje formula dodatka Power Query u programu Excel

uređivač dodatka Power Query omogućuje stvaranje upita o podacima i oblikovanje za Excel koje možete koristiti za preoblikovanje podataka iz brojnih izvora podataka. Da biste prikazali prozor uređivač dodatka Power Query, uvezite podatke iz vanjskih izvora podatakana radni list programa Excel, odaberite ćeliju u podacima, a zatim odaberite Upit > uređivanje. Slijedi sažetak glavnih komponenti.

1. Vrpca uređivač dodatka Power Query koju koristite za oblikovanje podataka

2. okno Upiti koje koristite za pronalaženje izvora podataka i tablica

3. Kontekstni izbornici koji su praktični prečaci za naredbe na vrpci

4. Pretpregled podataka koji prikazuje rezultate koraka primijenjenih na podatke

5. okno Postavke upita s popisom svojstava i svaki korak upita

Svaki se korak upita u pozadini temelji na formuli koja je vidljiva na traci formule.

Katkad ćete poželjeti izmijeniti ili stvoriti formulu. Formule koriste jezik Power Query Formula Language, koji omogućuje sastavljanje jednostavnih i kompleksnih izraza. Dodatne informacije o sintaksi, argumentima, primjedbama, funkcijama i primjerima potražite u jeziku za formule Power Query M.

Na primjer upotrijebite Power Query da biste pomoću neobrađenih podataka pronađenih na web-mjestu pretvorili u dobro oblikovanu tablicu. U oknu Postavke upita u odjeljku Primijenjeni koraci i na traci formule možete pratiti kako se za svaki zadatak stvaraju koraci upita i odgovarajuće formule.

Procedura

1. Da biste uvezli podatke, odaberite Podaci > S weba unesite "http://en.wikipedia. tvrtka ili ustanova/wiki/UEFA_European_Football_Championship" u okvir URL, a zatim odaberite U redu.

2. U dijaloškom okviru Navigator s lijeve strane odaberite tablicu Rezultati [Uređivanje], a zatim pri dnu odaberite Pretvori podatke . Pojavit će se uređivač Power Query.

3. Da biste promijenili zadani naziv upita, u oknu Postavke upita u odjeljku Svojstva izbrišite "Rezultati [uređivanje]", a zatim unesite "UEFA šampioni".

4. Da biste uklonili neželjene stupce, odaberite prvi, četvrti i peti stupac, a zatim odaberite Polazno > Ukloni stupac > Ukloni druge stupce.

5. Da biste uklonili neželjene vrijednosti, odaberite Stupac1, odaberite Polazno > Zamijeni vrijednosti, unesite "pojedinosti" u okvir Vrijednosti koje treba pronaći, a zatim odaberite U redu.

6. Da biste uklonili retke koji sadrže riječ "Godina", odaberite strelicu filtra u stupcu Stupac1, poništite potvrdni okvir pokraj stavke "Godina", a zatim odaberite U redu.

7. Da biste preimenovali zaglavlja stupaca, dvokliknite svako od njih, a zatim promijenite "Stupac1" u "Godina", "Stupac4" u "Pobjednik" i "Stupac5" u "Konačni rezultat".

8. Da biste spremili upit, odaberite Polazno > Zatvori & Učitaj.

Rezultat

U sljedećoj je tablici prikazan sažetak svih primijenjenih koraka i odgovarajuće formule.

Korak i zadatak upita	Formula
Izvor Povezivanje s izvorom podataka na webu	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navigacija Odabir tablice za povezivanje	=Source{2}[Data]
Promijenjena vrsta Promjena vrsta podataka (što Power Query automatski radi)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
Uklonjeni su drugi stupci Uklanjanje drugih stupaca tako da ostanu prikazani samo oni koji vas zanimaju	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Zamijenjena vrijednost Zamjena vrijednosti radi čišćenja vrijednosti u odabranom stupcu	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filtrirani reci Filtriranje vrijednosti u stupcu	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Preimenovani stupci Promijenjena su smislena zaglavlja stupaca	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Važno    Budite pažljivi prilikom uređivanja koraka za izvor, navigaciju i promijenjenu vrstu jer ih stvara Power Query radi definiranja i postavljanja izvora podataka.

Prikaz ili skrivanje trake formule

Traka formule prikazuje se prema zadanim postavkama, ali ako nije vidljiva, možete je ponovno prikazati.

• Odabir prikaza > rasporeda > trake formule.

E dit a formula in the formula bar

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

2. U oknu Postavke upita u odjeljku Primijenjeni koraci odaberite korak koji želite urediti.

3. Na traci formule pronađite i promijenite vrijednosti parametara, a zatim odaberite ikonu Enter ili pritisnite Enter. Na primjer, promijenite ovu formulu tako da zadržite i Stupac2:
   
   Prije: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Nakon:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Odaberite ikonu Enter ili pritisnite Enter da bi se novi rezultati prikazali u pretpregledu podataka.

5. Da biste rezultat vidjeli na radnom listu programa Excel, odaberite Polazno > Zatvori & Učitaj.

Stvaranje formule u traci formule

U primjeru jednostavne formule početna slova riječi tekstne vrijednosti pomoću funkcije Text.Properpretvorit ćemo u velika slova.

1. Da biste otvorili prazan upit, u programu Excel odaberite Podaci > Dohvati podatke> iz drugih izvora > prazan upit. Dodatne informacije potražite u odjeljku Stvaranje, učitavanje i uređivanje upita u programu Excel.

2. U traku formule unesite =Text.Proper("text value"), a zatim odaberite ikonu Enter ili pritisnite Enter.
   
   Rezultati se prikazuju u pretpregledu podataka.

3. Da biste rezultat vidjeli na radnom listu programa Excel, odaberite Polazno > Zatvori & Učitaj.

Rezultat:

Kada stvorite formulu, Power Query provjerava valjanost sintakse formule. No kada umetnete, promijenite redoslijed ili izbrišete međukorak upita, mogli biste uzrokovati potencijalni kvar upita. Uvijek provjerite rezultate u pretpregledu podataka.

Važno    Budite pažljivi prilikom uređivanja koraka za izvor, navigaciju i promijenjenu vrstu jer ih stvara Power Query radi definiranja i postavljanja izvora podataka.

Uređivanje formule pomoću dijaloškog okvira

Na taj se način koriste dijaloški okviri koji ovise o koraku. Ne morate znati sintaksu formule.

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

2. U oknu Postavke upita u odjeljku Primijenjeni koraci odaberite ikonu Uređivanje postavki ikonu koraka koji želite urediti ili desnom tipkom miša kliknite korak, a zatim odaberite Uređivanje postavki.

3. Napravite promjene u dijaloškom okviru pa odaberite U redu.

Umetanje koraka

Kada dovršite korak upita koji preoblikuje podatke, korak upita dodaje se ispod trenutnog koraka upita. No kada usred koraka umetnete korak upita, može doći do pogreške u sljedećim koracima. Power Query prikazuje upozorenje o koraku umetanja kada pokušate umetnuti novi korak, a novi korak mijenja polja, npr. nazive stupaca, koja se koriste u bilo kojem koraku koji slijedi nakon umetnutog koraka.

1. U oknu Postavke upita u odjeljku Primijenjeni koraci odaberite korak koji želite da neposredno prethodi novom koraku i njegovoj odgovarajućoj formuli.

2. Odaberite ikonu Dodaj korak lijevo od trake formule. Možete i desnom tipkom miša kliknuti korak, a zatim odabrati Umetni korak nakon. Stvorit će se nova formula u obliku :
   
   = <nameOfTheStepToReference>, primjerice =Production.WorkOrder.

3. Upišite novu formulu u sljedećem obliku:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Pretpostavite, primjerice, da imate tablicu sa stupcem Spol i da želite dodati stupac s vrijednošću "Ms." ili "Mr.", ovisno o spolu osobe. Formula bi bila sljedeća:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Promjena redoslijeda koraka

• U oknu Postavke upita u odjeljku Primijenjeni koraci desnom tipkom miša kliknite korak, a zatim odaberite Premjesti prije ili Premjesti nakon.

Izbriši korak

• Odaberite ikonu Izbriši s lijeve strane koraka ili desnom tipkom miša kliknite korak, a zatim odaberite Izbriši ili Izbriši do kraja. Ikona Izbriši nalazi se i lijevo od trake formule.

U ovom ćemo primjeru pretvoriti tekst u stupcu u velika slova pomoću kombinacije formula u napredni uređivač.

Ako, primjerice, imate tablicu programa Excel pod nazivom Narudžbe sa stupcem NazivProizvoda koji želite pretvoriti u velika slova u velika slova.

Prije:

Poslije:

Prilikom stvaranja naprednog upita stvarate niz koraka formule za upit na temelju izraza let . Izraz let koristite za dodjelu naziva i izračunavanje vrijednosti na koje se zatim poziva uvjet in , koji definira korak. U ovom se primjeru daje isti rezultat kao onaj u odjeljku "Stvaranje formule na traci formule".

let
Source = Text.Proper("hello world")
in
Source

Vidjet ćete da se svaki korak nadograđuje na prethodni korak i referira se na korak po nazivu. Podsjećamo da jezik za formule dodatka Power Query razlikuje velika i mala slova.

1. faza: Otvorite napredni uređivač

1. U programu Excel odaberite Podaci > Dohvati podatke > Drugi izvori > Prazni upit. Dodatne informacije potražite u odjeljku Stvaranje, učitavanje i uređivanje upita u programu Excel.

2. U uređivač dodatka Power Query odaberite Polazno > napredni uređivač, koji će se otvoriti s predloškom izraza let.

2. faza: definiranje izvora podataka

1. Stvorite izraz let pomoću funkcije Excel.CurrentWorkbook na sljedeći način:
   
   let
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
   Source
   
   

2. Da biste učitali upit na radni list, odaberite Gotovo, a zatim Polazno > Zatvori & Učitaj > Zatvori & učitaj.

Rezultat:

3. faza: pretvaranje prvog retka u zaglavlja

1. Da biste otvorili upit, na radnom listu odaberite ćeliju u podacima, a zatim odaberite Upit > uređivanje. Dodatne informacije potražite u članku Stvaranje, učitavanje i uređivanje upita u programu Excel (Power Query).

2. U uređivač dodatka Power Query odaberite Polazno > napredni uređivač koja će se otvoriti naredbom koju ste stvorili u 2. fazi: Definiranje izvora podataka.

3. U izrazu let dodajte #"Prvi redak kao zaglavlje" i Table.PromoteHeaders funkcioniraju na sljedeći način:
   
   let
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
   #"First Row as Header" = Table.PromoteHeaders(Source)
   in
   #"First Row as Header"

4. Da biste učitali upit na radni list, odaberite Gotovo, a zatim Polazno > Zatvori & Učitaj > Zatvori & učitaj.

Rezultat:

4. faza: promjena početnih početnih slova riječi u svim vrijednostima u stupcu u velika slova

1. Da biste otvorili upit, na radnom listu odaberite ćeliju u podacima, a zatim odaberite Upit > uređivanje. Dodatne informacije potražite u odjeljku Stvaranje, učitavanje i uređivanje upita u programu Excel.

2. U uređivač dodatka Power Query odaberite Polazno> napredni uređivač koji će se otvoriti naredbom koju ste stvorili u 3. fazi: Pretvaranje prvog retka u zaglavlja.

3. U izrazu let pretvorite početna slova riječi u svim vrijednostima u stupcu NazivProizvoda pomoću funkcije Table.TransformColumns koja se poziva na prethodni korak formule za upit "Prvi redak kao zaglavlje", dodavanjem #"Veliko prvo slovo svake Word" u izvor podataka, a zatim dodjeljivanjem #"Veliko prvo slovo svake Riječi" rezultatu naredbe in.
   
   let
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
   #"First Row as Header" = Table.PromoteHeaders(Source),
   #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
   #"Capitalized Each Word"

4. Da biste učitali upit na radni list, odaberite Gotovo, a zatim Polazno > Zatvori & Učitaj > Zatvori & učitaj.

Rezultat:

Ponašanje trake formule možete kontrolirati u uređivač dodatka Power Query u svim radnim knjigama.

Prikaz ili skrivanje trake formule

1. Odaberite> Mogućnosti i postavke> mogućnosti upita.

2. U lijevom oknu u odjeljku GLOBALNO odaberite uređivač dodatka Power Query.

3. U desnom oknu u odjeljku Raspored potvrdite ili poništite okvir Prikaz trake formule.

Uključivanje i isključivanje značajke M Intellisense

1. Odaberite Mogućnosti> Datoteka >Mogućnosti upita .

2. U lijevom oknu u odjeljku GLOBALNO odaberite uređivač dodatka Power Query.

3. U desnom oknu u odjeljku Formula potvrdite ili poništite okvir Omogući M Intellisense na traci formule, naprednom uređivaču i dijaloškom okviru prilagođenog stupca.

Napomena    Promjena te postavke stupit će na snagu kada sljedeći put otvorite prozor uređivač dodatka Power Query.