Stvaranje formula dodatka Power Query u programu Excel

Tablica uređivač dodatka Power Query sadrži podatkovni upit i iskustvo oblikovanja za Excel pomoću koje možete preoblikovanje podataka iz mnogih izvora podataka. Da biste prikazali uređivač dodatka Power Query,uvezite podatke iz vanjskih izvora podataka na radnom listu programa Excel, odaberite ćeliju u podacima, a zatim 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 svakog koraka u upitu

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

Katkad želite izmijeniti ili stvoriti formulu. Formule koriste jezik Power Query, koji možete koristiti za stvaranje jednostavnih i složenih izraza. Dodatne informacije o sintaksi, argumentima, primjedbama, funkcijama i primjerima potražite u članku Power Query M.

U primjeru pomoću popisa nogometnih prvenstva koristite Power Query neobrađene podatke koje ste pronašli na web-mjestu i pretvorite ih u dobro oblikovanu tablicu. Pogledajte kako se koraci upita i odgovarajuće formule stvaraju za svaki zadatak u oknu Postavke upita u odjeljku Primijenjeni koraci i na traci formule.

Procedura

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

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

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

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

5. Da biste uklonili neželjene vrijednosti, odaberite Stupac1, odaberite Polazno> Zamijeni vrijednosti, unesite "detalji" u okvir Vrijednosti za traženje, a zatim odaberite U redu.

6. Da biste uklonili retke s riječju "Godina", odaberite strelicu filtra u stupcu Stupac1, poništite potvrdni okvir uz "Godina", a zatim odaberite U redu.

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

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

Rezultat

Sljedeća tablica sažetak je svakog primijenjenog koraka i odgovarajuće formule.

Korak upita i zadatak	Formula
Izvor Povezivanje s web-izvorom podataka	= 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 (koje Power Query automatski)	= 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 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 zaglavlja stupaca u smislena	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Važno    Pažljivo uređujte korake Izvor, Navigacija  i Promijenjena vrsta jer ih je stvorio Power Query definirali i postavili izvor podataka.

Prikaz ili skrivanje trake formule

Traka formule prikazana je po zadanom, ali ako nije vidljiva, možete je ponovno reproducirati.

• Odaberite Prikaz > rasporeda > formule.

Edit a formula in the formula bar

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. 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. Promijenite, primjerice, ovu formulu da biste zadržali stupac2:
   
   Prije: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   nakon:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

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

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

Stvaranje formule u traci formule

U primjeru jednostavne formule pretvorimo tekstnu vrijednost u velika slova pomoću funkcije Text.Proper.

1. Da biste otvorili prazan upit, u programu Excel odaberite > Dohvati podatke >iz drugih izvora > prazan upit. Dodatne informacije potražite u članku Stvaranje, učitavanje ili 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 vidjeli rezultat na radnom listu programa Excel, odaberite Polazno > Zatvori & učitaj.

Rezultat:

 Kada stvorite formulu, Power Query provjerava valjanost sintakse formule. No prilikom umetanja, promjene redoslijeda ili brisanja posrednih koraka u upitu mogli biste prekinuti upit.  Uvijek provjerite rezultate u pretpregledu podataka.

Važno    Pažljivo uređujte korake Izvor, Navigacija  i Promijenjena vrsta jer ih je stvorio Power Query definirali i postavili izvor podataka.

Uređivanje formule pomoću dijaloškog okvira

Ta metoda koristi dijaloške okvire koji ovise o koraku. Ne morate znati sintaksu formule.

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. U oknu Postavke upita u odjeljku Primijenjeni koraci odaberite ikonu Uređivanje koraka koji želite urediti ili desnom tipkom miša kliknite korak, a zatim odaberite Uređivanje postavki.

3. U dijaloškom okviru unesite promjene, a zatim odaberite U redu.

Umetanje koraka

Kada dovršite korak upita koji preoblikovanje podataka, ispod trenutnog koraka upita dodaje se korak upita. no kada umetnete korak upita usred koraka, može doći do pogreške u sljedećim koracima. Power Query prikazuje upozorenje Umetni korak kada pokušate umetnuti novi korak, a novi korak mijenja polja, kao što su nazivi stupaca, koja se koriste u bilo kojem od koraka koji slijede umetnuti korak.

1. U oknu Postavke upita u odjeljku Primijenjeni koraci odaberite korak koji želite odmah prethoditi novom koraku i odgovarajućoj formuli.

2. Odaberite ikonu Dodaj korak s lijeve strane trake formule. Možete i desnom tipkom miša kliknuti korak, a zatim odabrati Umetni korak nakon. Stvara se nova formula u obliku := <nameOfTheStepToReference>
   
   , kao što je =Production.WorkOrder.

3. Upišite novu formulu u obliku:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Pretpostavimo, primjerice, da imate tablicu sa stupcem Spol i želite dodati stupac s vrijednošću "Ms". ili "g.", ovisno o spolu osobe. Formula bi bila:
   
   =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 goreili Premjesti dolje.

Izbriši korak

• Odaberite ikonu Izbriši s lijeve strane koraka ili desnom tipkom miša kliknite korak, a zatim odaberite Izbrišiili Izbriši do kraja. Ikona Izbriši dostupna je s lijeve strane trake formule.

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

Na primjer, imate tablicu programa Excel pod nazivom Narudžbe s stupcem Naziv ProductName koji želite pretvoriti u velika slova. 

Prije:

Nakon:

Kada stvorite napredni upit, stvarate niz koraka formule upita na temelju izraza let. Pomoću izraza let dodijelite nazive i izračunajte vrijednosti na koje se poziva uvjet in , koji definira korak. U ovom se primjeru vraća isti rezultat kao onaj u odjeljku "Stvaranje formule u traci formule".

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

Vidjet ćete da se svaki korak nadolažu na prethodni korak referateći se na korak po naziv. Kao podsjetnik, jezik Power Query razlikuje velika i mala slova.

1. faza: otvaranje napredni uređivač

1. U programu Excel odaberite> Dohvati podatke > drugi izvori >prazan upit. Dodatne informacije potražite u članku Stvaranje, učitavanje ili 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#x4

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

Rezultat:

3. faza: promicanje prvog retka u zaglavlja

1. Da biste otvorili upit, na radnom listu 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 (Power Query).

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

3. U izrazu let dodajte funkciju #"Prvi redak kao zaglavlje" i Table.PromoteHeaders na sljedeći način:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
       #"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 svake vrijednosti u stupcu u velika slova

1. Da biste otvorili upit, na radnom listu 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. U uređivač dodatka Power Query odaberite Polazno> napredni uređivač, koja će se otvoriti s izjavom koju ste stvorili u 3. fazi: povećajte razinu prvog retka u zaglavlja.

3. U izrazu let pretvorite svaku vrijednost stupca ProductName u odgovarajući tekst pomoću funkcije Table.TransformColumns, referiranjem na prethodni korak formule upita "Prvi redak kao zaglavlje", dodavanjem #"Veliko slovo svakog Word" u izvor podataka, a zatim dodjeljivanjem vrijednosti #"Veliko slovo svakog Word" rezultatu u obliku slova.
   
   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:

Možete upravljati ponašanjem trake formule u uređivač dodatka Power Query za sve radne knjige.

Prikaz ili skrivanje trake formule

1. Odaberite Mogućnosti > datoteka 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 odaberite ili poništite mogućnost Prikaži traku formule.

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

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

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

3. U desnom oknu u odjeljku Formula odaberite ili poništite mogućnost Omogući M Intellisense u traci formule, naprednom uređivaču i prilagođenom dijaloškom okviru stupaca.

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