U ovom uputstvu možete da koristite Uređivač upita programskog dodatka 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 porudžbini 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 izvršili ovo uputstvo, potrebna vam je radna sveska "Proizvodi ". U dijalogu Sačuvaj kao, datoteci dajte ime Proizvodi i porudžbine.xlsx.
1. zadatak: Uvoz proizvoda u Excel radnu svesku
U ovom zadatku uvozite proizvode iz datoteke "Proizvodi i Orders.xlsx (preuzete i preimenovane iznad) u Excel radnu svesku, podignete nivo redova na naslove kolona, uklonite neke kolone i učitate upit u radni list.
1. korak: Povezivanje sa Excel radnom sveskom
- Kreirajte Excel radnu svesku.
- Izaberite podatke>Preuzmite podatke>iz datoteke>iz radne sveske.
- U dijalogu " Uvoz podataka " potražite i pronađite Products.xlsx datoteku koju ste preuzeli, a zatim izaberite stavku "Otvori".
- U oknu " Navigator " kliknite dvaput na tabelu "Proizvodi ". Pojavljuje se Power Query uređivač.
2. korak: Ispitivanje koraka upita
Power Query podrazumevano automatski dodaje nekoliko koraka kako bi vam olakšao. Da biste saznali više, pregledajte svaki korak u odeljku Primenjeni koraci u oknu " Postavke upita ".
- Kliknite desnim tasterom miša na korak " Izvor" i izaberite stavku "Uredi postavke". Ovaj korak je kreiran kada ste uvezli radnu svesku.
- Kliknite desnim tasterom miša na korak za navigaciju i izaberite stavku "Uredi postavke". Ovaj korak je kreiran kada ste izabrali tabelu u dijalogu "Navigacija ".
- Kliknite desnim tasterom miša na korak " Promenjeno tipovanje " i izaberite stavku "Uredi postavke". Ovaj korak je napravio Power Query koji je izveo tipove podataka za svaku kolonu. Kliknite na strelicu nadole sa desne strane polja za formulu da biste videli celu formulu.
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 pregledu podataka izaberite kolone "ID proizvoda","ImeProizvoda", "ID kategorije" i "KoličinaPoJedinici " (koristite kombinaciju tastera Ctrl+klik ili Shift+klik).
- Izaberite stavku "Ukloni kolone>" Ukloni druge kolone.
4. korak: Učitavanje upita o proizvodima
U ovom koraku učitavate upit "Proizvodi " u Excel radni list.
- Izaberite stavku "Početak>","Zatvori & učitavanje". Upit se pojavljuje na novom Excel radnom listu.
Rezime: Power Query koraci kreirani u 1. zadatku
Dok u programskom dodatku Power Query obavljate aktivnosti vezane za upite, u oknu "Postavke upita" kreiraju se i navode koraci upita, na listi "Primenjeni koraci". Za svaki korak postoji odgovarajuća Power Query formula, poznata i pod nazivom „M“ jezik. Više informacija o Power Query formulama potražite u članku "Pravljenje Power Query formula u programu Excel.
| Zadatak | Korak upita | Formula |
|---|---|---|
| Uvoz Excel radne sveske | Izvor | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true) |
| Izaberite tabelu "Proizvodi" | Navigacija | = Izvor{[Stavka = "Proizvodi",Vrsta = "Tabela"]}[Podaci] |
| Power Query automatski otkriva tipove podataka kolona | Promenjen tip | = Table.TransformColumnTypes( Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}}) |
| Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone | Uklonjene druge kolone | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
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 http://services.odata.org/Northwind/Northwind.svc, proširujete Order_Details tabelu, uklanjate kolone, izračunavate ukupnu vrednost reda, transformišete datum porudžbine, grupišete redove po ID-u proizvoda i godini, preimenujete upit i onemogućavate preuzimanje upita u Excel radnu svesku.
1. korak: Povezivanje sa OData feedom
- Izaberite podatke>Preuzmite podatke>iz drugih izvora>iz OData feeda.
- U dijalogu OData feed unesite URL za Northwind OData feed.
- Izaberite dugme U redu.
- U oknu "Navigator " kliknite dvaput na tabelu "Porudžbine ".
2. korak: Proširivanje Order_Details tabele
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 (Order_Details) kombinuju se u redovima sa primarnom tabelom (Porudžbine).
U programskom dodatku Power Query kolona koja sadrži srodnu tabelu u ćeliji ima vrednost "Zapis" ili "Tabela". Takve su takozvane strukturirane kolone. Zapis ukazuje na jedan srodni zapis i predstavlja relaciju jedan-prema-jedan sa trenutnim podacima ili primarnom tabelom. Tabela ukazuje na povezanu tabelu i predstavlja relaciju jedan-prema-više sa trenutnom ili primarnom tabelom. Strukturirana kolona predstavlja relaciju u izvoru podataka koji ima relacioni model. Na primer, strukturirana kolona ukazuje na entitet sa povezivanjem sporednog ključa u OData feedu ili relaciju sporednog ključa u SQL Server bazi podataka.
Kada proširite tabelu Order_Details , tri nove kolone i dodatni redovi dodaju se u tabelu "Porudžbine ", po jedan za svaki red u ugnežđenoj ili srodnoj tabeli.
U pregledu podataka pomerite se horizontalno do kolone Order_Details .
U koloni Order_Details izaberite ikonu za proširivanje (
).Na padajućem meniju Proširivanje:
Izaberite stavku (Izaberi sve kolone) da biste obrisali sve kolone.
Izaberite IDProizvoda, cenu po jedinici i količinu.
Izaberite dugme U redu.
Napomena
U programskom dodatku Power Query možete da proširite tabele povezane iz kolone i skupite kolone povezane 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 Pregledu podataka izaberite sledeće kolone:
- Izaberite prvu kolonu, IDPorudžbine.
- Shift+klik na poslednju kolonu, Špediteri.
- 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 druge kolone".
4. korak: izračunavanje zbira reda za svaki Order_Details red
U ovom koraku kreirate prilagođenu kolonu radi izračunavanja zbira reda za svaki red Detalji_porudžbine.
- U prikazu podataka izaberite ikonu tabele (
) u gornjem levom uglu pregleda. - Izaberite stavku "Dodaj prilagođenu kolonu".
- U dijalogu "Prilagođena kolona ", u polje "Formula prilagođene kolone " unesite [Order_Details.JediničnaCena] * [Order_Details.Količina].
- U polje "Ime nove kolone " unesite "Zbir reda".
- Izaberite 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 prikazu podataka kliknite desnim tasterom miša na kolonu "DatumPorudžbine" i izaberitestavku "Godinatransformacije>".
Preimenujte kolonu DatumPorudžbine u Godina:
- Kliknite dvaput na kolonu DatumPorudžbine i unesite reč Godina ili
- Right-Click u koloni "DatumPorudžbine " izaberite stavku "Preimenuj" i unesite stavku "Godina".
6. korak: Grupisanje redova po ID-u proizvoda i godini
U prikazu podataka izaberite stavke "Godina " i Order_Details.IDProizvoda.
Right-Click jedno 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.
Izaberite dugme U redu.
Pre nego što uvezete podatke o prodaji u Excel, preimenujte upit:
- U oknu "Postavke upita ", u polje "Ime " unesite "Ukupna prodaja".
Rezultati: Konačni upit za 2. zadatak
Kad završite svaki korak, imaćete upit „Ukupna prodaja“ preko Northwind OData feeda.
Rezime: Power Query koraci kreirani u 2. zadatku
Dok u programskom dodatku Power Query obavljate aktivnosti vezane za upite, u oknu "Postavke upita" kreiraju se i navode koraci upita, na listi "Primenjeni koraci". Za svaki korak postoji odgovarajuća Power Query formula, poznata i pod nazivom „M“ jezik. Više informacija o Power Query formulama potražite u članku Saznajte više o Power Query formulama.
| Zadatak | Korak upita | Formula |
|---|---|---|
| Povezivanje sa OData feedom | Izvor | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"]) |
| Select a table | Navigacija | = izvor{[name="Porudžbine"]}[Podaci] |
| Proširivanje tabele Detalji_Porudžbine | Proširivanje tabele „Detalji_Porudžbine“ | = Table.ExpandTableColumn(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 | = Table.RemoveColumns(#"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“ | Dodato je prilagođeno |
= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) |
| Promena u smislenije ime, Lne Total | Preimenovane kolone | = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}}) |
| Transformacija kolone „DatumPorudžbine“ za prikazivanje godine | Izdvojena godina | = Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}}) |
| Promenite u smislenija imena, "DatumPorudžbine" i "Godina" |
Preimenovane kolone 1 |
Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}}) |
| Grupisanje redova po ID-u proizvoda i godini | GroupedRows | = Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}}) |
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 kombinujete upite "Proizvodi" i "Ukupna prodaja" pomoću upitaobjedinjavanja i operacije proširivanja , a zatim učitavate upit "Ukupna prodaja po proizvodu" u Excel model podataka.
1. korak: Objedinjavanje kolone "IDProizvoda" u upitu "Ukupna prodaja"
U Excel radnoj svesci idite do upita " Proizvodi " na kartici radnog lista "Proizvodi ".
Izaberite ćeliju u upitu, a zatim izaberite stavku "Objedini upit>".
U dijalogu Objedinjavanje izaberite stavku "Proizvodi " kao primarnu tabelu i stavku "Ukupna prodaja " kao sekundarni ili srodni upit za objedinjavanje. "Ukupna prodaja " postaje nova strukturirana kolona sa ikonom za proširivanje.
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.
- Izaberite stavku Sačuvaj.
Izaberite dugme U redu.
Napomena
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 "Podešavanje nivoa privatnosti".
Rezultat
Operacija objedinjavanja kreira upit. Rezultat upita sadrži sve kolone iz primarne tabele (proizvodi) i jednu strukturiranu kolonu tabele do srodne tabele (Ukupna prodaja). Izaberite ikonu " Proširi" da biste u primarnu tabelu dodali nove kolone iz sekundarne ili srodne tabele.
2. korak: Proširivanje objedinjene kolone
U ovom koraku treba da proširite objedinjenu kolonu sa imenom "NovaKolona" kako biste napravili dve nove kolone u upitu "Proizvodi ": Godina i Ukupna prodaja.
U prikazu podataka, izaberite ikonu za proširivanje (
) pored stavke "NovaKolona".Na padajućoj listi " Razvijanje ":
- Izaberite stavku (Izaberi sve kolone) da biste obrisali sve kolone.
- Izaberite stavke "Godina " i "Ukupna prodaja".
- Izaberite dugme U redu.
Preimenujte te dve kolone u Godina i Ukupna prodaja.
Da biste saznali koji su proizvodi i koje godine imali najveću količinu prodaje, izaberite stavku "Sortiraj po opadajućem redosledupo ukupnoj prodaji".
Preimenujte upit u Ukupna prodaja po proizvodu.
Rezultat
3. korak: Učitavanje upita "Ukupna prodaja po proizvodu" u Excel model podataka
U ovom koraku učitate upit u Excel model podataka kako biste izgradili izveštaj povezan sa rezultatom upita. Kad učitate podatke u Excel model podataka, možete da koristite Power Pivot za dalju analizu podataka.
- Izaberite stavku "Početak>","Zatvori & učitavanje".
- U dijalogu "Uvoz podataka " uverite se da ste izabrali "Dodaj ove podatke u model podataka". Za više informacija o korišćenju ovog dijaloga, izaberite znak pitanja (?).
Rezultat
Imate upit " Ukupna prodaja po proizvodu " koji kombinuje podatke iz Products.xlsx datoteke i Northwind OData feeda. Ovaj upit se primenjuje na Power Pivot model. Pored toga, promene upita menjaju i osvežavaju dobijenu tabelu u modelu podataka.
Rezime: Power Query koraci kreirani u 3. zadatku
Dok izvršavate aktivnosti objedinjavanja 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 Power Query formulama 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) | = Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter) |
| Proširivanje kolone objedinjavanja | Proširena ukupna prodaja | = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"}) |
| Preimenovanje dve kolone | Preimenovane kolone | = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}}) |
| Sortiraj ukupnu prodaju rastućim redosledom | Sortirani redovi | = Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}}) |