Pravljenje Power Query formula u programu Excel

Funkcija Power Query uređivač obezbeđuje upit za podatke i iskustvo oblikovanja za Excel koje možete da koristite za preošli podatke iz mnogih izvora podataka. Da biste prikazali Power Query uređivač,uvezite podatke iz spoljnih izvora podataka u Excel radni list, izaberite ćeliju u podacima, a zatim izaberite stavku >Uredi. Sledi rezime glavnih komponenti.

1. The Power Query uređivač ribbon that you use to shape your data

2. Okno "Upiti" koje koristite za pronalaženje izvora podataka i tabela

3. Kontekstualni meniji koji su prikladne prečice za komande na traci

4. Pregled podataka koji prikazuje rezultate koraka primenjenih na podatke

5. Okno "Postavke upita" koje navodi svojstva i svaki korak u upitu

U pozadini, svaki korak u upitu zasnovan je na formuli koja je vidljiva u polju za formulu.

Možda ćete ponekad želeti da izmenite ili kreirate formulu. Formule koriste jezik Power Query formule koji možete da koristite za pravljenje jednostavnih i složenih izraza. Više informacija o sintaksi, argumentima, primedbi, funkcijama i primerima potražite u članku Power Query jezik M formule.

Korišćenje liste fudbalskih šampionata kao primera, koristite Power Query preuzmete neobrađene podatke koje ste pronašli na veb lokaciji i pretvorite ih u dobro oblikovanu tabelu. Pogledajte kako se kreiraju koraci upita i odgovarajuće formule za svaki zadatak u oknu Postavke upita u okviru Primenjeni koraci i na traci formula.

Procedura

1. Da biste uvezli podatke, izaberite stavku Podaci> Sa veba, unesite "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" u polje URL , a zatim kliknite na dugme U redu.

2. U dijalogu Navigator izaberite tabelu Rezultati [Uredi] sa leve strane, a zatim izaberite stavku Transformiši podatke na dnu. Pojavljuje Power Query uređivač.

3. Da biste promenili podrazumevano ime upita, u oknu Postavke upita, u okviru Svojstva izbrišite "Rezultati [Uredi]", a zatim unesite "UEFA šampioni".

4. Da biste uklonili neželjene kolone, izaberite prvu, četvrtu i petu kolonu, a zatim izaberite stavku Početak> Ukloni kolonu > Ukloni druge kolone.

5. Da biste uklonili neželjene vrednosti, izaberite stavku Kolona1, izaberite Početak> Zameni vrednosti, unesite "detalje" u polje Vrednosti za pronalaženje, a zatim kliknite na dugme U redu.

6. Da biste uklonili redove koji u njima sadrže reč "Godina", izaberite strelicu filtera u koloni 1, opozovite izbor u polju za potvrdu pored stavke "Godina", a zatim kliknite na dugme U redu.

7. Da biste preimenovali zaglavlja kolona, kliknite dvaput na svako od njih, a zatim promenite "Kolona1" u "Godina", "Kolona4" u "Dobitnik", a "Kolona5" u "Konačni rezultat".

8. Da biste sačuvali upit, izaberite stavku Početak > Zatvori & Učitaj.

Rezultat

Sledeća tabela je rezime svakog primenjenog koraka i odgovarajuće formule.

Korak i zadatak upita	Formula
Izvor Povezivanje sa izvorom podataka na vebu	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navigacija Izaberite tabelu za povezivanje	=Source{2}[Data]
Promenjeni tip Promena tipova podataka (Power Query se 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}})
Uklonjene druge kolone Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Zamenjena vrednost Zamena vrednosti za čišćenje vrednosti u izabranoj koloni	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filtrirani redovi Filtriranje vrednosti u koloni	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Preimenovane kolone Promenjena zaglavlja kolona da bi bila smislena	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Važno    Budite pažljivi prilikom uređivanja koraka Izvor, Navigacija i Promenjeni tip zato što ih Power Query da definišu i podese izvor podataka.

Prikazivanje ili skrivanje trake sa formulama

Polje za formulu se podrazumevano prikazuje, ali ako nije vidljivo, možete ponovo da ga prikažete.

• Izaberite stavku > Raspored > polje za formulu.

Edit a formula in the formula bar

1. Da biste otvorili upit, pronađite neki koji je prethodno učitan iz Power Query uređivač, izaberite ćeliju u podacima, a zatim izaberite stavku Upit > Uredi. Više informacija potražite u članku Kreiranje, učitavanje ili uređivanje upita u programu Excel.

2. U oknu Postavke upita , u okviru Primenjeni koraci izaberite korak koji želite da uredite.

3. U polju za formulu pronađite i promenite vrednosti parametra, a zatim kliknite na ikonu Unesite ili pritisnite taster Enter. Na primer, promenite ovu formulu da biste zadržali i kolonu2:
   
   Pre: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Posle:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Izaberite ikonu Unesite ili pritisnite taster Enter da biste videli nove rezultate prikazane u pregledu podataka.

5. Da biste videli rezultat u Excel radnom listu, izaberite stavku Početak > Zatvori & Učitaj.

Kreiranje formule u polju za formulu

Da biste prikazali jednostavan primer formule, hajde da konvertovanje tekstualne vrednosti u odgovarajuća slova pomoću funkcije Text.Proper.

1. Da biste otvorili prazan upit, u programu Excel izaberite stavku> Preuzmi podatke > iz drugih izvora > prazan upit. Više informacija potražite u članku Kreiranje, učitavanje ili uređivanje upita u programu Excel.

2. U polje za formulu unesite=Text.Proper("text value"), a zatim izaberite ikonu Enter pritisnite taster Enter.
   
   Rezultati se prikazuju u pregledu podataka .

3. Da biste videli rezultat u Excel radnom listu, izaberite stavku Početak > Zatvori & Učitaj.

Rezultat:

 Kada kreirate formulu, Power Query proverava sintaksu formule. Međutim, kada umetnete, prerasporedite ili izbrišete međuko drugi korak u upitu, može doći do preloma upita.  Uvek proverite rezultate u pregledu podataka.

Važno    Budite pažljivi prilikom uređivanja koraka Izvor, Navigacija i Promenjeni tip zato što ih Power Query da definišu i podese izvor podataka.

Uređivanje formule pomoću dijaloga

Ovaj metod koristi dijaloge koji se razlikuju u zavisnosti od koraka. Ne morate da znate sintaksu formule.

1. Da biste otvorili upit, pronađite neki koji je prethodno učitan iz Power Query uređivač, izaberite ćeliju u podacima, a zatim izaberite stavku Upit > Uredi. Više informacija potražite u članku Kreiranje, učitavanje ili uređivanje upita u programu Excel.

2. U oknu Postavke upita, u okviru Primenjeni koraci izaberite ikonu Uredi postavke koraka koji želite da uredite ili kliknite desnim tasterom miša na korak, a zatim izaberite stavku Uredi postavke.

3. U dijalogu unesite promene, a zatim kliknite na dugme U redu.

Umetanje koraka

Kada dovršite korak upita koji preslišava podatke, korak upita se dodaje ispod trenutnog koraka upita. ali kada umetnete korak upita u sredinu koraka, može doći do greške u narednim koracima. Power Query prikazuje upozorenje Umetni korak kada pokušate da umetnete novi korak i novi korak menja polja, kao što su imena kolona, koja se koriste u bilo kom od koraka koji slede umetnuti korak.

1. U oknu Postavke upita , u okviru Primenjeni koraci izaberite korak ispred novog koraka i njegove odgovarajuće formule.

2. Izaberite ikonu Dodaj korak sa leve strane polja za formulu. Druga mogućnost je da kliknete desnim tasterom miša na korak, a zatim izaberete stavku Umetni korak posle. Kreira se nova formula u formatu :
   
   = <nameOfTheStepToReference>, kao što je =Production.WorkOrder.

3. Otkucajte novu formulu koristeći format:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Na primer, pretpostavimo da imate tabelu sa kolonom "Gender" i želite da dodate kolonu sa vrednošću "Ms". ili "g.", u zavisnosti od roda osobe. Formula bi bila:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Promena redosleda koraka

• U oknu Postavke upita, u okviru Primenjenikoraci kliknite desnim tasterom miša na korak, a zatim izaberite stavku Premesti nagore ili Premesti nadole.

Izbriši korak

• Izaberite ikonu Izbriši sa leve strane koraka ili kliknite desnim tasterom miša na korak, a zatim izaberite stavku Izbriši ili Izbriši do kraja. Ikona izbriši dostupna je i sa leve strane polja za formulu.

U ovom primeru, hajde da konvertovanje teksta u koloni u odgovarajuća slova pomoću kombinacije formula u Napredni uređivač. 

Na primer, imate Excel tabelu pod imenom "Porudžbine" sa kolonom "Ime Proizvoda" koju želite da konvertujete u odgovarajuća slova. 

Pre:

Posle:

Kada kreirate napredni upit, kreirate niz koraka formule za upit na osnovu izraza za omogućavanje. Koristite izraz za dodeljivanje imena i izračunavanje vrednosti na koje se zatim upućuje u odredbi, koja definiše korak. Ovaj primer vraća isti rezultat kao onaj u odeljku "Kreiranje formule u polju za formulu".

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

Videćete da se svaki korak gradi na prethodni korak tako što ćete upućivanje na korak po ime. Podsetnik je Power Query jezik formule razlikuje mala i velika slova.

1. faza: Otvaranje Napredni uređivač

1. U programu Excel izaberite stavku Podaci > Preuzmi podatke > druge izvore > prazan upit. Više informacija potražite u članku Kreiranje, učitavanje ili uređivanje upita u programu Excel.

2. Na kartici Power Query uređivač izaberite stavku Početak> Napredni uređivač koji se otvara sa predloškom let izraza.

2. faza: Definisanje izvora podataka

1. Kreirajte izraz za omogućavanje pomoću funkcije Excel.CurrentWorkbook na sledeći način:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. Da biste učitali upit na radni list, izaberite stavku Gotovo, a zatim izaberite Početak> Zatvori & Učitaj > Zatvori & Učitaj.

Rezultat:

3. faza: Podizanja nivoa prvog reda na zaglavlja

1. Da biste otvorili upit, sa radnog lista izaberite ćeliju u podacima, a zatim izaberite stavku Upit> Uredi. Više informacija potražite u članku Kreiranje, učitavanje ili uređivanje upita u programu Excel (Power Query).

2. U oknu Power Query uređivač izaberite stavku Početak> Napredni uređivač koja se otvara sa izvodom koji ste kreirali u 2. fazi: Definisanje izvora podataka.

3. U izrazu za omogućavanje dodajte #"First Row as Header" i Table.PromoteHeaders funkcije na sledeć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, izaberite stavku Gotovo, a zatim izaberite Početak> Zatvori & Učitaj > Zatvori & Učitaj.

Rezultat:

4. faza: Promena svake vrednosti u koloni u odgovarajuća slova

1. Da biste otvorili upit, sa radnog lista izaberite ćeliju u podacima, a zatim izaberite stavku Upit> Uredi. Više informacija potražite u članku Kreiranje, učitavanje ili uređivanje upita u programu Excel.

2. U oknu Power Query uređivač izaberite stavku Početak> Napredni uređivač, koji se otvara sa izvodom koji ste kreirali u 3. fazi: Podizanja prvog reda na zaglavlja.

3. U izrazu dozvolite da konvertuje svaku vrednost kolone "ProductName" u odgovarajući tekst pomoću funkcije Table.TransformColumns koja upućuje na prethodni korak formule za upit "First Row as Header" i dodaje #"Capitalized Each Word" izvoru podataka, a zatim dodeljuje #"Capitalized Each Word" rezultatu.
   
   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, izaberite stavku Gotovo, a zatim izaberite Početak> Zatvori & Učitaj > Zatvori & Učitaj.

Rezultat:

Možete da kontrolišete ponašanje polja za formulu u dijalogu Power Query uređivač za sve radne sveske.

Prikazivanje ili skrivanje polja za formulu

1. Izaberite stavku Datoteke> opcije i postavke >upita.

2. U levom oknu, u okviru GLOBALNOizaberite stavku Power Query uređivač.

3. U desnom oknu, u okviru Raspored potvrdite ili opozovite izbor u polju za potvrdu Prikaži traku sa formulama.

Uključivanje ili isključivanje funkcije M Intellisense

1. Izaberite stavke Datoteke> opcije i postavke >upita .

2. U levom oknu, u okviru GLOBALNOizaberite stavku Power Query uređivač.

3. U desnom oknu, u okviru Formula potvrdite ili opozovite izbor u polju za potvrdu Omogući M Intellisense u polju za formulu, napredni uređivač i dijalog prilagođene kolone.

Napomena    Promena ove postavke će sleteti na snagu kada sledeći put otvorite Power Query uređivač prozor.