Napomena: Power Query je poznat kao Preuzimanje i transformacija u programu Excel 2016. Informacije koje su ovde navedene važi za obe. Da biste saznali više, pogledajte članak Preuzmi i prenesi u programu Excel 2016.
U ovom uputstvu ćete koristiti uređivač upita za Power Query da biste uvezli podatke iz lokalne Excel datoteke koja sadrži informacije o proizvodu i iz OData feeda koji sadrži informacije o nalogu proizvoda. Izvršićete korake transformacije i agregacije i kombinovaćete podatke iz oba izvora kako biste kreirali izveštaj Ukupna prodaja po proizvodu i godini.
Da biste obavili ovo uputstvo, potrebne su vam proizvode i porudžbine . U dijalogu Sačuvaj kao, datoteci dajte ime Proizvodi i porudžbine.xlsx.
U ovom uputstvu
-
4. korak: Izračunavanje zbira reda za svaki red „Detalji_porudžbine“
-
5. korak: Transformacija kolone sa godinama „DatumPorudžbine“
-
8. korak: Onemogućavanje preuzimanja upita u Excel radnu svesku
-
3. zadatak: Kombinovanje upita „Proizvodi“ i „Ukupna prodaja“
1. zadatak: Uvoz proizvoda u Excel radnu svesku
U ovom zadatku uvozite proizvode iz proizvoda i porudžbine. xlsx datoteke u Excel radnu svesku.
1. korak: Povezivanje sa Excel radnom sveskom
-
Kreirajte Excel radnu svesku.
-
Na kartici POWER QUERY na traci izaberite stavke Iz datoteke > Iz programa Excel.
-
U dijalogu za pregledanje Excel pronađite ili otkucajte putanju datoteke „Proizvodi i porudžbine.xlsx“ da biste uvezli datoteku ili se povezali sa njom.
-
U oknu Navigator kliknite dvaput na radni list Proizvodi ili izaberite stavku Proizvodi i stavku Uredi upit. Kada uredite upit ili se povežete sa novim izvorom podataka, pojavljuje se prozor Uređivač upita.
Napomena: Za veoma kratak video o tome kako da prikažete uređivač upita pogledajte kraj ovog članka.
2. korak: Podizanje nivoa prvog reda na zaglavlja kolona tabele
U koordinatnoj mreži Pregled upita prvi red tabele ne sadrži imena kolona tabele. Da biste podigli nivo prvog reda na zaglavlja za kolonu tabele:
-
Kliknite na ikonu tabele (
) u gornjem levom uglu pregleda podataka. -
Izaberite stavku Koristi prvi red kao zaglavlja.
3. korak: Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone
U ovom koraku treba da uklonite sve kolone osim kolona IDProizvoda, ImeProizvoda, IDKategorije i KoličinaPoJedinici.
-
U koordinatnoj mreži Pregled upita izaberite kolone IDProizvoda, ImeProizvoda, IDKategorije i KoličinaPoJedinici (koristite Ctrl+klik ili Shift+klik).
-
Na traci Uređivača upita izaberite stavke Ukloni kolone > Ukloni druge kolone ili kliknite desnim tasterom miša na zaglavlje kolone, a zatim izaberite stavku Ukloni druge kolone.
Kreirani Power Query koraci
Dok u programskom dodatku Power Query obavljate aktivnosti vezane za upite, u oknu Postavke upita, na listi PRIMENJENI KORACI kreiraju se i navode koraci upita. Za svaki korak postoji odgovarajuća Power Query formula, poznata i pod nazivom „M“ jezik. Više informacija o jeziku Power Query formula potražite u članku Saznajte više o Power Query formulama.
Zadatak | Korak upita | Formula |
|---|---|---|
|
Povezivanje sa Excel radnom sveskom |
Izvor |
Source{[Name="Products"]}[Data] |
|
Podizanje nivoa prvog reda na zaglavlja za kolonu tabele |
FirstRowAsHeader |
(Products) |
|
Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone |
RemovedOtherColumns |
(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
4. korak: Uvoz upita o proizvodima
U ovom koraku uvozite upit Proizvodi u Excel radnu svesku.
-
Na traci Uređivača upita kliknite na dugme Primeni i zatvori. Rezultati se prikazuju na novom Excel radnom listu.
2. zadatak: Uvoz podataka o porudžbini iz OData feeda
U ovom zadatku uvozite podatke u Excel radnu svesku iz uzorka Northwind OData feeda na lokaciji http://services.odata.org/Northwind/Northwind.svc.
1. korak: Povezivanje sa OData feedom
-
Na kartici POWER QUERY na traci izaberite stavke Iz drugih izvora > Iz OData feeda.
-
U dijalogu OData feed unesite URL za Northwind OData feed.
-
Kliknite na dugme U redu.
-
U oknu Navigator kliknite dvaput na tabelu Porudžbine ili izaberite stavku Porudžbine, a zatim kliknite na dugme Uredi.
Napomena: Kada zadržite pokazivač miša iznad tabele, prikazuje se padajući pregled tabele.
2. korak: Proširivanje tabele „Detalji_porudžbine“
U ovom koraku treba da proširite tabelu Detalji_porudžbine koja je povezana sa tabelom Porudžbine da biste kombinovali kolone IDProizvoda, CenaPoJedinici i Količina iz tabele Detalji_porudžbine u tabeli Porudžbine. Operacija Proširivanje kombinuje kolone iz srodne tabele u tabeli teme. Kad se upit pokrene, redovi iz srodne tabele (Detalji_porudžbine) kombinuju se u redovima iz tabele teme (Porudžbine).
U programskom dodatku Power Query kolona koja sadrži vezu ka srodnoj tabeli ima vezu Unos ili Tabela. Veza unosa se pokreće sa jednim srodnim zapisom i predstavljajednu relaciju sa tabelom "Tema".Veza tabele se pokreće sa povezanom tabelom i predstavlja relaciju "jedan-prema-više" sa tabelom "Tema". Karika predstavlja svojstva navigacije u izvoru podataka u relacionom modelu. Za OData feed, svojstva navigacije predstavljaju entitet sa asocijacijom sporednog ključa. U bazi podataka, kao što je SQL Server, svojstva navigacije predstavljaju relacije stranih ključeva u bazi podataka.
Proširivanje veze tabele „Detalji_porudžbine“
Kad proširite tabelu Detalji_porudžbine, tri nove kolone i dodatni redovi dodaju se u tabelu Porudžbine, po jedan za svaki red u ugnežđenoj ili srodnoj tabeli.
-
U oknu Pregled upita pomerite se do kolone Detalji_porudžbine.
-
U koloni Order_Details kliknite na ikonu Razvij (
). -
Na padajućem meniju Proširivanje:
-
Izaberite stavku (Izaberi sve kolone) da biste obrisali sve kolone.
-
Izaberite kolone IDProizvoda, CenaPoJedinici i Količina.
-
Kliknite na dugme U redu.
Napomena: U programskom dodatku Power Query možete da proširite tabele povezane sa kolonom kao i da izvršite operacije agregacije za kolone srodne tabele pre proširivanja podataka u tabeli teme. Više informacija o tome kako da izvršite agregatne operacije potražite u članku Agregacija podataka iz kolone.
-
3. korak: Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone
U ovom koraku uklanjate sve kolone osim kolona DatumPorudžbine, IDProizvoda, CenaPoJedinici i Količina. U prethodnom zadatku koristili ste opciju Ukloni druge kolone. U ovom zadatku uklanjate izabrane kolone.
Uklanjanje izabranih kolona
-
U oknu Pregled upita izaberite sve kolone:
-
Kliknite na prvu kolonu (IDPorudžbine).
-
Shift+klik na poslednju kolonu (Otpremnik).
-
Ctrl+klik na kolone DatumPorudžbine, Detalji_porudžbine.IDProizvoda, Detalji_porudžbine.CenaPoJedinici i Detalji_porudžbine.Količina.
-
-
Kliknite desnim tasterom miša na izabrano zaglavlje kolone i izaberite stavku Ukloni kolone.
4. korak: Izračunavanje zbira reda za svaki red „Detalji_porudžbine“
U ovom koraku kreirate prilagođenu kolonu radi izračunavanja zbira reda za svaki red Detalji_porudžbine.
Izračunavanje zbira reda za svaki red „Detalji_porudžbine“
-
U oknu Pregled upita kliknite na ikonu tabele (
) u gornjem levom uglu pregleda. -
Izaberite stavke Umetni kolonu > Prilagođeno.
-
U dijalogu Umetanje prilagođene kolone, u okviru za tekst Formula prilagođene kolone unesite [Detalji_porudžbine.CenaPoJedinici] * [Detalji_porudžbine.Količina].
-
U okvir za tekst Ime nove kolone unesite Zbir reda.
-
Kliknite na dugme U redu.
5. korak: Transformacija kolone sa godinama „DatumPorudžbine“
U ovom koraku treba da transformišete kolonu DatumPorudžbine kako bi se prikazala godina datuma porudžbine.
-
U koordinatnoj mreži Pregled kliknite desnim tasterom miša na kolonu DatumPorudžbine i izaberite stavke Transformacija > Godina.
-
Preimenujte kolonu DatumPorudžbine u Godina:
-
Kliknite dvaput na kolonu DatumPorudžbine i unesite reč Godina ili
-
Kliknite desnim tasterom miša na kolonu DatumPorudžbine, izaberite stavku Preimenuj i unesite reč Godina.
-
6. korak: Grupisanje redova po ID-u proizvoda i godini
-
U koordinatnoj mreži Pregled upita izaberite kolone Godina i Detalji_Porudžbine.IDProizvoda.
-
Kliknite desnim tasterom miša na neko od zaglavlja i izaberite stavku Grupiši po.
-
U dijalogu Grupisanje po:
-
U okviru za tekst Ime nove kolone unesite ime Ukupna prodaja.
-
Na padajućem meniju Operacija izaberite stavku Zbir.
-
Na padajućem meniju Kolona izaberite stavku Zbir reda.
-
-
Kliknite na dugme U redu.
7. korak: Preimenovanje upita
Pre nego što uvezete podatke o prodaji u Excel, dajte upitu ime Ukupna prodaja:
-
U oknu Postavke upita, u okvir za tekst Ime unesite Ukupna prodaja.
Konačni rezultati upita
Kad završite svaki korak, imaćete upit „Ukupna prodaja“ preko Northwind OData feeda.
Kreirani Power Query koraci
Dok u programskom dodatku Power Query obavljate aktivnosti vezane za upite, u oknu Postavke upita, na listi PRIMENJENI KORACI kreiraju se i navode koraci upita. Za svaki korak postoji odgovarajuća Power Query formula, poznata i pod nazivom „M“ jezik. Više informacija o jeziku Power Query formula potražite u članku Saznajte više o Power Query formulama.
Zadatak | Korak upita | Formula |
|---|---|---|
|
Povezivanje sa OData feedom |
Izvor |
Source{[Name="Orders"]}[Data] |
|
Proširivanje tabele Detalji_Porudžbine |
Proširivanje tabele „Detalji_Porudžbine“ |
(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"}) |
|
Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone |
RemovedColumns |
(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"}) |
|
Izračunavanje zbira reda za svaki red tabele Detalji_Porudžbine |
InsertedColumns |
(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) |
|
Transformacija kolone „DatumPorudžbine“ za prikazivanje godine |
RenamedColumns |
(InsertedCustom,{{"Custom", "Line Total"}}) |
|
TransformedColumn |
(RenamedColumns,{{"OrderDate", Date.Year}}) |
|
|
RenamedColumns1 |
(TransformedColumn,{{"OrderDate", "Year"}}) |
|
|
Grupisanje redova po ID-u proizvoda i godini |
GroupedRows |
Tabela. grupa
|
8. korak: Onemogućavanje preuzimanja upita u Excel radnu svesku
Pošto upit Ukupna prodaja ne predstavlja konačni izveštaj Ukupna prodaja po proizvodu i godini, onemogućite preuzimanje upita u Excel radnu svesku. Kad je opcija Učitaj u radnu svesku postavljena na vrednost Isključeno u oknu Postavke upita, rezultat podataka ovog upita se ne preuzima, ali se upit i dalje može kombinovati sa drugim upitima kako bi se dobio željeni rezultat. U sledećem zadatku ćete saznati kako se ovaj upit kombinuje sa upitom „Proizvodi“.
Onemogućavanje preuzimanja upita
-
U oknu Postavke upita opozovite izbor u polju za potvrdu Učitaj u radni list.
-
Na traci Uređivača upita kliknite na dugme Primeni i zatvori. U oknu Upiti radne sveske upit Ukupna prodaja prikazuje vrednost Učitavanje je onemogućeno.
3. zadatak: Kombinovanje upita „Proizvodi“ i „Ukupna prodaja“
Power Query omogućava vam da kombinujete više upita, njihovim objedinjavanjem ili dodavanjem. Operacija Objedinjavanje izvršava se na bilo kom Power Query upitu sa tabelarnim oblikom, bez obzira na izvor podataka iz kojeg podaci potiču. Više informacija o kombinovanju izvora podataka potražite u članku Kombinovanje više upita.
U ovom zadatku treba da kombinujete upite Proizvodi i Ukupna prodaja pomoću koraka upita Objedinjavanje i Proširivanje.
1. korak: Objedinjavanje kolone „IDProizvoda“ u upitu „Ukupna prodaja“
-
U Excel radnoj svesci idite do upita Proizvodi na listu List2.
-
Na kartici UPIT na traci kliknite na dugme Objedini.
-
U dijalogu Objedinjavanje izaberite tabelu Proizvodi kao primarnu tabelu i Ukupna prodaja kao drugu ili srodni upit za objedinjavanje. Ukupna prodaja postaje nova proširiva kolona.
-
Da bi se tabela Ukupna prodaja podudarala sa tabelom Proizvodi po koloni IDProizvoda, izaberite kolonu IDProizvoda iz tabele Proizvodi i kolonu Detalji_porudžbine.IDProizvoda iz tabele Ukupna prodaja.
-
U dijalogu Nivoi privatnosti:
-
Izaberite stavku Organizacioni za nivo izolacije privatnosti za oba izvora podataka.
-
Kliknite na dugme Sačuvaj.
-
-
Kliknite na dugme U redu.
Napomena o bezbednosti: Nivoi privatnosti sprečavaju korisnike da slučajno kombinuju podatke iz više izvora podataka, koji su možda privatni ili za organizaciju. U zavisnosti od upita korisnik može slučajno da pošalje podatke iz privatnog izvora podataka drugom izvoru podataka koji je možda zlonameran. Power Query analizira svaki izvor podataka i postavlja mu definisani nivo privatnosti: javni, organizacijski i privatni. Više informacija o nivoima privatnosti potražite u članku Nivoi privatnosti.
Pošto kliknete na dugme U redu, operacija Objedinjavanje kreira upit. Rezultat upita sadrži sve kolone iz primarne tabele (Proizvodi) i jednu kolonu koja sadrži vezu navigacije ka srodnoj tabeli (Ukupna prodaja). Operacija Proširivanje dodaje nove kolone u primarnu ili tabelu teme iz srodne tabele.
2. korak: Proširivanje kolone objedinjavanja
U ovom koracima proširite kolonu za objedinjavanje sa imenom Newcolumn da biste kreirali dve nove kolone u upitu proizvodi : godina i Ukupna prodaja.
Proširivanje veze tabele „NovaKolona“
-
U koordinatnoj mreži upita Preview kliknite na ikonu Razvij novo kolone (
). -
Na padajućem meniju Proširivanje:
-
Izaberite stavku (Izaberi sve kolone) da biste obrisali sve kolone.
-
Kliknite na stavke Godina i Ukupna prodaja.
-
Kliknite na dugme U redu.
-
-
Preimenujte te dve kolone u Godina i Ukupna prodaja.
-
Sortirajte po opadajućem redosledu po koloni Ukupna prodaja da biste saznali koji su proizvodi i koje godine imali najveću količinu prodaje.
-
Preimenujte upit u Ukupna prodaja po proizvodu.
Kreirani Power Query koraci
Dok izvršavate aktivnosti Objedinjavanje upita u programskom dodatku Power Query, koraci upita se kreiraju i navode u oknu Postavke upita, na listi PRIMENJENI KORACI. Za svaki korak postoji odgovarajuća Power Query formula, poznata i pod nazivom „M“ jezik. Više informacija o jeziku Power Query formula potražite u članku Saznajte više o Power Query formulama.
Zadatak | Korak upita | Formula |
|---|---|---|
|
Objedinjavanje kolone „IDProizvoda“ u upitu „Ukupna prodaja“ |
Izvor (izvor podataka za operaciju Objedinjavanje) |
(Products,{"ProductID"},#"Total Sales",{"Order_Details.ProductID"},"NewColumn") |
|
Proširivanje kolone objedinjavanja |
ExpandNewColumn |
(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"}) |
|
RenamedColumns |
(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}}) |
|
|
SortedRows |
(RenamedColumns,{{"Total Sales", Order.Descending}}) |
3. korak: Učitavanje upita „Ukupna prodaja po proizvodu“ u Excel model podataka
U ovom koraku treba da onemogućite opciju Učitaj u radni list i učitate upit u Excel model podataka kako biste izgradili izveštaj povezan sa rezultatom upita. Pored učitavanja rezultata upita u Excel radni list, Power Query vam omogućava da učitate rezultat upita u Excel model podataka. Kad učitate podatke u Excel model podataka, možete da koristite Power Pivot i Power View za dalju analizu podataka.
Učitavanje upita „Ukupna prodaja po proizvodu“ u Excel model podataka
-
U oknu Postavke upita opozovite izbor u polju za potvrdu Učitaj u radni list i potvrdite izbor u polju za potvrdu Učitaj u model podataka.
-
Da biste učitali upit u Excel model podataka, kliknite na dugme Primeni i zatvori.
Upit „Konačna ukupna prodaja po proizvodu“
Kad izvršite svaki korak, imaćete upit Ukupna prodaja po proizvodu koji kombinuje podatke iz datoteke „Proizvodi i porudžbine.xlsx“ i Northwind OData feeda. Ovaj upit može da se primeni na Power Pivot model. Pored toga, promene upita u programskom dodatku Power Query menjaju i osvežavaju dobijenu tabelu u Power Pivot modelu.
Napomena: Uređivač upita se pojavljuje samo kada učitavate, uređujete ili kreirate novi upit pomoću programskog dodatka Power Query. Sledeći video prikazuje prozor uređivača upita koji se pojavljuje nakon uređivanja upita iz Excel radne sveske. Da biste pregledali Uređivač upita bez učitavanja ili uređivanja postojećeg upita radne sveske, iz odeljka "učitavanje spoljnih podataka " na kartici Power Query izaberite stavku iz drugih izvora > prazan upit. Sledeći video prikazuje jedan način za prikazivanje uređivača upita.
Napomena: Ova stranica je prevedena automatski i može da sadrži gramatičke greške ili netačnosti. Naš cilj je da ovaj sadržaj bude koristan. Možete li da nam javite da li su informacije bile korisne? Ovo je članak na engleskom jeziku za referencu.
