Saznajte kako da kombinujete više izvora podataka (Power Query)

U ovom zadatku uvozite proizvode iz datoteke Proizvodi i Orders.xlsx (preuzeti i preimenovani iznad) u Excel radnu svesku, podignite nivo redova u zaglavlja kolona, uklonite neke kolone i učitate upit u radni list.

1. korak: Povezivanje sa Excel radnom sveskom

1. Kreirajte Excel radnu svesku.

2. Izaberite stavku Podaci > Preuzmi podatke >iz datoteke > iz radne sveske.

3. U dijalogu Uvoz podataka potražite i pronađite datoteku koju ste Products.xlsx preuzeli, a zatim izaberite stavku Otvori.

4. U oknu Navigator kliknite dvaput na tabelu Proizvodi . Pojavljuje se power Uređivač upita.

2. korak: Ispitivanje koraka upita

Podrazumevano Power Query automatski dodaje nekoliko koraka kao pogodnost za vas. Ispitajte svaki korak u okviru Primenjeni koraci u oknu Postavke upita da biste saznali više.

1. Kliknite desnim tasterom miša na korak Izvor i izaberite stavku Uredi postavke. Ovaj korak je kreiran kada ste uvezli radnu svesku.

2. Kliknite desnim tasterom miša na korak navigacije i izaberite stavku Uredi postavke. Ovaj korak je kreiran kada ste tabelu izabrali iz dijaloga "Navigacija ".

3. Kliknite desnim tasterom miša na korak Promenjeni tip i izaberite stavku Uredi postavke. Ovaj korak je kreiran Power Query koji je izvodio tipove podataka svake kolone. Kliknite na strelicu nadole sa desne strane polja za formulu da biste videli kompletnu 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.

1. U pregledu podataka izaberite kolone ID proizvoda, Ime Proizvoda, IDKategorije i KoličinaPerUnit (koristite Ctrl+klik ili Shift+klik).

2. Izaberite stavku Ukloni kolone > ukloni ostale kolone.

   

4. korak: Učitavanje upita proizvoda

U ovom koraku učitavate upit Proizvodi u Excel radni list.

• Izaberite stavku Početak > Zatvori & učitavanje. Upit se pojavljuje u novom Excel radnom listu.

Rezime: Power Query koraci kreirani u 1. zadatku

Dok obavljate aktivnosti upita u programu Power Query, koraci upita se kreiraju i nabrajaju 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 formulama Power Query potražite u članku Kreiranje Power Query u programu Excel.

Zadatak	Korak upita	Formula
Uvoz Excel radne sveske	Izvor	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Izbor tabele "Proizvodi"	Navigacija	= Izvor{[Stavka="Proizvodi",Kind="Tabela"]}[Podaci]
Power Query automatski otkriva tipove podataka kolona	Promenjeni 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"})

U ovom zadatku uvozite podatke u Excel radnu svesku iz uzorka Northwind OData feeda u programu http://services.odata.org/Northwind/Northwind.svc,razvijete Order_Details tabelu, uklonite kolone, izračunate ukupnu vrednost reda, transformišete DatumPorudžbine, grupišete redove po ID-u proizvoda i godini, preimenujete upit i onemogućite preuzimanje upita u Excel radnu svesku.

1. korak: Povezivanje sa OData feedom

1. Izaberite stavku > Preuzmi podatke > iz drugih izvora >iz OData feeda.

2. U dijalogu OData feed unesite URL za Northwind OData feed.

3. Izaberite dugme U redu.

4. U oknu Navigator kliknite dvaput na tabelu Porudžbine .

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. Kada se upit pokrene, redovi iz srodne tabele (Order_Details) kombinuju se u redove sa primarnom tabelom (Porudžbine).

U Power Query kolona koja sadrži povezanu tabelu ima vrednost Zapis iliTabela u ćeliji. To se nazivaju strukturirane kolone. Zapis ukazuje na jedan povezani zapis i predstavlja relacijujedan-na-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 odnosu sporednog ključa u SQL Server bazi podataka.

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.

1. U pregledu podataka pomerite se horizontalno do Order_Details kolone.

2. U koloni Order_Details izaberite ikonu za razvijanje ().

3. Na padajućem meniju Proširivanje:

   1. Izaberite stavku (Izaberi sve kolone) da biste obrisali sve kolone.

   2. Izaberite stavke ID proizvoda, CenaPoJedinici i Količina.

   3. Izaberite dugme U redu.

      

      Napomena: U Power Query da proširite tabele povezane iz kolone i agregirate kolone povezane tabele pre nego što proširite podatke 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. 

1. U pregledu podataka izaberite sledeće kolone: 

   1. Izaberite prvu kolonu, IDporudžbine.

   2. Shift+klik na poslednju kolonu, Špeditera.

   3. Ctrl+klik na kolone DatumPorudžbine, Detalji_porudžbine.IDProizvoda, Detalji_porudžbine.CenaPoJedinici i Detalji_porudžbine.Količina.

2. Kliknite desnim tasterom miša na izabrano zaglavlje kolone i izaberite stavku Ukloni druge 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.

1. U pregledu podataka izaberite ikonu tabele () u gornjem levom uglu pregleda.

2. Izaberite stavku Dodaj prilagođenu kolonu.

3. U dijalogu Prilagođena kolona, u polje formule Prilagođena kolona unesite [Order_Details.CenaPoJedinici] * [Order_Details.Količina].

4. U polje Ime nove kolone unesite Zbir reda.

5. 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.

1. U pregledu podataka kliknite desnim tasterom miša na kolonu DatumPorudžbine i izaberite stavku Transformacija > godina.

2. Preimenujte kolonu DatumPorudžbine u Godina:

   1. Kliknite dvaput na kolonu DatumPorudžbine i unesite reč Godina ili

   2. Right-Click koloni DatumPorudžbine izaberite stavku Preimenuj i unesite Godina.

6. korak: Grupisanje redova po ID-u proizvoda i godini

1. U pregledu podataka izaberite stavku Godinai Order_Details.ProductID.

2. Right-Click jedno od zaglavlja i izaberite stavku Grupiši po.

3. U dijalogu Grupisanje po:

   1. U okviru za tekst Ime nove kolone unesite ime Ukupna prodaja.

   2. Na padajućem meniju Operacija izaberite stavku Zbir.

   3. Na padajućem meniju Kolona izaberite stavku Zbir reda.

4. Izaberite dugme U redu.

   

7. korak: Preimenovanje upita

Pre nego što uvezete podatke o prodaji u Excel, preimenujte upit:

• U oknu Postavke upita , u polje Ime unesiteUkupna prodaja.

Rezultati: Konačni upit za zadatak 2

Kad završite svaki korak, imaćete upit „Ukupna prodaja“ preko Northwind OData feeda.

Rezime: Power Query koraci kreirani u 2. zadatku 

Dok obavljate aktivnosti upita u programu Power Query, koraci upita se kreiraju i nabrajaju u oknu Postavke upita, na listi Primenjeni koraci. Za svaki korak postoji odgovarajuća Power Query formula, poznata i pod nazivom „M“ jezik. Dodatne informacije o Power Query formulama potražite u članku Saznajte više Power Query formulama.

Zadatak	Korak upita	Formula
Povezivanje sa OData feedom	Izvor	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Primena="2,0"])
Select a table	Navigacija	= Izvor{[Ime="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 prilagođeno	= Table.AddColumn(RemovedColumn, "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}})
Promeni u  smislenijim imenima, 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}})

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 upita Objedinjavanje i Razvij, a zatim učitajte upit Ukupna prodaja po proizvodu u Excel model podataka.

1. korak: Objedinjavanje kolone „IDProizvoda“ u upitu „Ukupna prodaja“

1. U Excel radnoj svesci idite na upit Proizvodi na kartici radnog lista Proizvodi.

2. Izaberite ćeliju u upitu, a zatim izaberite stavku> Objedinjavanje.

3. U dijalogu Objedinjavanje izaberite stavku Proizvodi kao primarnu tabelu i izaberite stavku Ukupna prodaja kao sekundarni ili srodni upit za objedinjavanje.Ukupna prodaja postaće nova strukturirana kolona sa ikonom za razvijanje.

4. 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.

5. U dijalogu Nivoi privatnosti:

   1. Izaberite stavku Organizacioni za nivo izolacije privatnosti za oba izvora podataka.

   2. Izaberite stavku Sačuvaj.

6. Izaberite 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 Postavljanje nivoa privatnosti.

   

Rezultat

Operacija Objedinjavanje kreira upit. Rezultat upita sadrži sve kolone iz primarne tabele (Proizvodi) i jednu kolonu strukturiranu tabele do srodne tabele (Ukupna prodaja). Izaberite ikonu Razvij da biste dodali nove kolone u primarnu tabelu iz sekundarne ili srodne tabele.

2. korak: Proširivanje objedinjene kolone

U ovom koraku proširite objedinjenu kolonu sa imenom NovaKolumna da biste napravili dve nove kolone u upitu Proizvodi: Godina i Ukupna prodaja.

1. U pregledu podatakaizaberite stavku Razvij ikonu () pored stavke NovaKolumna.

2. Na padajućoj listi Razvij:

   1. Izaberite stavku (Izaberi sve kolone) da biste obrisali sve kolone.

   2. Izaberite stavke Godina i Ukupna prodaja.

   3. Izaberite dugme U redu.

3. Preimenujte te dve kolone u Godina i Ukupna prodaja.

4. Da biste saznali koji proizvodi i u kojim godinama su proizvodi dobili najveći obim prodaje, izaberite stavku Sortiraj po opadajućem redosledu poukupnoj prodaji.

5. 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čitavate upit u Excel model podataka da biste napravili izveštaj povezan sa rezultatom upita. Kada učitate podatke u Excel model podataka, možete da koristite Power Pivot da biste dodatno analizirali podatke.

1. Izaberite stavku Početak > Zatvori & učitavanje.

2. U dijalogu Uvoz podataka proverite da li ste izabrali stavku 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 Objedinjavanje upita u programu Power Query, koraci upita se kreiraju i nabrajaju u oknu Postavke upita, na listi Primenjeni koraci. Za svaki korak postoji odgovarajuća Power Query formula, poznata i pod nazivom „M“ jezik. Dodatne informacije o Power Query formulama potražite u članku Saznajte više 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 po rastućem redosledu	Sortirani redovi	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})