Koristeći Power Query uređivač, kreirali ste Power Query formule sve vreme. Hajde da vidimo kako Power Query funkcioniše tako što ću ga posmatrati ispod haube. Možete da saznate kako da ažurirate ili dodate formule tako što ćete posmatrati Power Query uređivač na delu.  Možete čak i da prenete sopstvene formule sa naprednim uređivačem.           

Power Query uređivač obezbeđuje upit za podatke i iskustvo sa Excel koje možete da koristite za pre oblik podataka iz mnogih izvora podataka. Da biste prikazali prozor Power Query uređivača, uvezite podatke iz spoljnih izvora podataka iz Excel radnog lista, izaberite ćeliju u podacima, a zatim izaberite stavku Upit >Uredi. Sledi rezime glavnih komponenti.

Delovi uređivača upita

  1. The Power Query Editor 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 praktične prečice za komande na traci

  4. The Data Preview that displays the results of the steps applied to the data

  5. The Query Postavke pane that lists properties and each step in the query

Iza kulisa svaki korak u upitu je zasnovan na formuli koja je vidljiva u traci sa formulama.

Query Editor Formula Sample

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

Koristeći listu fudbalskih titula kao primer koristite Power Query da biste preuzeli neodoljne podatke koje ste pronašli na veb lokaciji i pretvorili ih u dobro oblikovanu tabelu. Pogledajte kako se kreiraju koraci upita i odgovarajuće formule za svaki zadatak u oknu "Upite Postavke" u okviru "Primenjeni koraci" i "Polje za formulu".

Pregledač ne podržava video zapis.

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 u dnu. Pojavljuje se Power Query uređivač.

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

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

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

  6. Da biste uklonili redove koji u njima imaju reč "Godina", izaberite strelicu filtera u koloni1, opozovite izbor u polju za potvrdu pored stavke "Godina", a zatim izaberite stavku U redu.

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

  8. Da biste sačuvali upit, izaberite stavku> Zatvori & učitavanje.

Rezultat

Rezultati ovog koraka – prvih nekoliko redova

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

Korak i zadatak upita

Formula

Izvor

Povezivanje u izvor podataka na vebu

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Navigacija

Izaberite tabelu da biste se povezali

=Source{2}[Data]

Promenjeni tip

Promena tipova podataka (koje Power Query automatski izvršava)

= 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 tako da budu smislena

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Važno    Vodite računa o uređivanje koraka "Izvor", "Navigacija"i "Promenjeni tip" zato što ih je kreirao Power Query za definisanje i podešavanje   izvora podataka.

Prikazivanje ili skrivanje trake za formulu

Polje za formulu se podrazumevano pokazuje, ali ako nije vidljiv, možete ponovo da ga prikazate.

  • Izaberite stavku Prikaz > Raspored > Polje za formulu.

Edit a formula in the formula bar

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

  2. U oknu Upit Postavke u okviru Primenjeni koraciizaberite korak koji želite da uredite.

  3. U polju za formulu pronađite i promenite vrednosti parametra, a zatim izaberite ikonu Unesite Ikona "Unesite" levo od polja za formulu u programu Power Query ili pritisnite taster Enter. Na primer, promenite ovu formulu tako da zadrži i Kolona2:

    Pre: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    Posle:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Izaberite ikonu Ikona "Unesite" levo od polja za formulu u programu Power Query Enter ili pritisnite taster Enter da biste videli nove rezultate prikazane u pregledu podataka.

  5. Da biste videli rezultat u radnom listu Excel, izaberite stavku Početna> Zatvori & učitavanje.

Kreiranje formule u traci sa formulama

Da bi se dao jednostavan primer formule, hajde da konvertovanje tekstualne vrednosti u odgovarajuća sla pomoću funkcije Text.Proper.

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

  2. U polje za formulu unesite=Text.Proper("text value"), a zatim kliknite na ikonu Enter Ikona "Unesite" levo od polja za formulu u programu Power Query pritisnite taster Enter.

    Rezultati se prikazuju u pregledu podataka.

  3. Da biste videli rezultat u radnom listu Excel, izaberite stavku Početna> Zatvori & učitavanje.

Rezultat:

Text.Proper

 Kada kreirate formulu, Power Query proverava sintaksu formule. Međutim, kada umetnete, preuredite ili izbrišete međukorak u upitu, može se prelomiti upit.  Uvek proverite rezultate u pregledu podataka.

Važno    Vodite računa o uređivanje koraka "Izvor", "Navigacija"i "Promenjeni tip" zato što ih je kreirao Power Query za definisanje i podešavanje   izvora 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 onaj koji je prethodno učitan iz programnog uređivača Power Query, izaberite ćeliju u podacima, a zatim izaberite stavku Upit > Uredi. Više informacija potražite u temi Kreiranje, učitavanje ili uređivanje upita u programu Excel.

  2. U oknu Upit Postavke, u okviru Primenjeni koraci izaberite ikonu Postavke Ikona „Postavke“ koraka koji želite da uredite ili kliknite desnim tasterom miša na njega, a zatim izaberite stavku Uredi Postavke.

  3. U dijalogu unošite promene, a zatim kliknite na dugme U redu.

Umetanje koraka

Kada dovršite korak upita koji preliva 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 Umetanje koraka kada pokušate da umetnete novi korak, a novi korak menja polja, kao što su imena kolona, koja se koriste u bilo kom koraku koji slede umetnut korak.

  1. U oknu Postavke upit, u okviru Primenjeni koraci izaberitekorak koji želite da pre njega pređite novim korakom i njegovom odgovarajućem formulom.

  2. Izaberite ikonu Ikona stavke „Funkcija“ dodaj korak sa leve strane polje za formulu. Druga mogućnost je da kliknete desnim tasterom miša na korak, a zatim izaberete stavku Umetni korak posle.Nova formula se kreira u formatu:

    = <nameOfTheStepToReference>, kao što je =Production.WorkOrder.

  3. Otkucajte novu formulu u formatu:

    =Class.Function(ReferenceStep[,otherparameters])

    Na primer, pretpostavimo da imate tabelu sa kolonom "Gender" i da ž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.")

Primer formule

Preuredite korak

  • U oknu Upiti Postavke Primenjeni koraci kliknitedesnim tasterom miša na korak, a zatim izaberite stavku Premesti nadole ili Premesti nadole.

Izbriši korak

  • Izaberite ikonu Izbriši korak 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 Izbriši korak dostupna je i sa leve strane polje za formulu.

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

Na primer, imate tabelu Excel", "Porudžbine", sa kolonom "Ime proizvoda" koju želite da konvertujete u odgovarajuća stupca. 

Pre:

Pre

Posle:

4. korak – Rezultat

Kada kreirate napredni upit, kreirate niz koraka formule za upit na osnovu izraza koji daje let.  Koristite izraz za dodelu imena i izračunavanje vrednosti na koje se zatim upućuje u odredbi, što definiše korak. Ovaj primer daje isti rezultat kao i onaj u odeljku "Kreiranje formule u traci sa formulama".

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

Videćete da se svaki korak nadogogđuje na prethodni korak tako što se imenom upućuje na korak. Podsetnik je da Power Query jezik formule ne sadrži velika i velika slova.

1. faza: Otvaranje naprednog uređivača

  1. U Excel stavku Podaci > Stavku Prikupiti podatke > Drugi izvori >Prazan upit. Više informacija potražite u temi Kreiranje, učitavanje ili uređivanje upita u programu Excel.

  2. U Power Query uređivaču izaberite stavku> Napredni uređivač, koji se otvara sa predloškom let izraza.

Napredni uređivač2

2. faza: Definisanje izvora podataka

  1. Napravite izraz koji će koristiti Excel. Funkcija CurrentWorkbook na sledećilet


        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
    in
        Source

    1. korak – Napredni uređivač

  2. Da biste učitali upit u radni list, izaberite stavkuGotovo , a zatim izaberite Stavku Početna > Zatvori & Učitaj > Zatvori & učitavanje.

Rezultat:

1. korak – Rezultat

3. faza: Podizanje 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 temi Kreiranje, učitavanje ili uređivanje upita u programu Excel (Power Query).

  2. U Power Query uređivaču izaberite karticu Matična> Napredni uređivač, koja se otvara sa izjavom koju ste kreirali u 2. fazi: Definisanje izvora podataka.

  3. U izrazu let, dodajte #"First Row as Header" i Table.PromoteHeaders funkciju na sledeći

    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 u radni list, izaberite stavkuGotovo , a zatim izaberite Stavku Početna > Zatvori & Učitaj > Zatvori & učitavanje.

Rezultat:

3. korak – Rezultat

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

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

  2. U Power Query uređivaču izaberite stavku Matična > Napredni uređivač, koji se otvara izrazom koji ste kreirali u 3. fazi: Podizanjeprvog reda na zaglavlja.

  3. U izrazu let expression, convert each ProductName column value to proper text by using the Table.TransformColumns function, refered to previous "First Row as Header" formula formula, adding #"Capitalized Each Word" to the data source, and then assigning #"Capitalized Each Word" to the result.

    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 u radni list, izaberite stavkuGotovo , a zatim izaberite Stavku Početna > Zatvori & Učitaj > Zatvori & učitavanje.

Rezultat:

4. korak – Rezultat

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

Prikazivanje ili skrivanje trake za formulu

  1. Izaberite stavke Datoteka> Opcije i Postavke > Opcije 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 formulu Prikaži polje za formulu.

Uključivanje ili isključivanje M Intellisense

  1. Izaberite stavke Datoteka> Opcije i Postavke > Opcije upita.

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

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

Napomišite    Promena ove postavke stupiće na snagu kada sledeći put otvorite prozor Power Query uređivača.

Takođe pogledajte

Pomoć za Power Query Excel

Pravljenje i pozivanje prilagođene funkcije

Korišćenje liste primenjenih koraka (docs.com)

Korišćenje prilagođenih funkcija (docs.com)

Power Query M formule (docs.com)

Rad sa greškama (docs.com)

Potrebna vam je dodatna pomoć?

Unapredite veštine
Istražite obuku
Prvi nabavite nove funkcije
Pridružite se Microsoft insajdere

Da li su vam ove informacije koristile?

Koliko ste zadovoljni kvalitetom prevoda?
Šta je uticalo na vaše iskustvo?

Hvala vam na povratnim informacijama!

×