Saznajte kako kombinirati više izvora podataka (Power Query)

U ovom zadatku uvozite proizvode iz datoteke Proizvodi i Orders.xlsx (preuzeta i preimenovana iznad) u radnu knjigu programa Excel, promašuje retke u zaglavlja stupaca, uklanja neke stupce i učitava upit na radni list.

Prvi korak: povezivanje s radnom knjigom programa Excel

1. Stvorite radnu knjigu programa Excel.

2. Odaberite Podatkovni > Dohvati podatke > iz datoteke >iz radne knjige.

3. U dijaloškom okviru Uvoz podataka potražite i pronađite datoteku Products.xlsx koju ste preuzeli, a zatim odaberite Otvori.

4. U oknu Navigator dvokliknite tablicu Proizvodi. Prikazat će se Uređivač upita power Uređivač upita power.

Drugi korak: pregledajte korake upita

Prema zadanim Power Query automatski dodaje nekoliko koraka kao praktičnost. Pregledajte svaki korak u odjeljku Primijenjeni koraci u oknu Postavke upita da biste saznali više.

1. Desnom tipkom miša kliknite korak Izvor, a zatim odaberite Uređivanje postavki. Taj je korak stvoren prilikom uvoza radne knjige.

2. Desnom tipkom miša kliknite korak navigacije, a zatim odaberite Uređivanje postavki. Taj je korak stvoren kada ste odabrali tablicu iz dijaloškog okvira Navigacija.

3. Desnom tipkom miša kliknite korak Promijenjena vrsta, a zatim odaberite Uređivanje postavki. Taj je korak stvorio Power Query koji je odgodila vrste podataka svakog stupca. Odaberite strelicu dolje desno od trake formule da biste vidjeli cijelu formulu.

Treći korak: uklanjanje drugih stupaca tako da ostanu prikazani samo oni koji vas zanimaju

U ovom ćete koraku ukloniti sve stupce osim IDProizvoda, NazivProizvoda, IDKategorije i JediničnaKoličina.

1. U pretpregledu podataka odaberite stupce IDproizvoda, NazivProizvoda, IDkategoije i KoličinaPerUnit (koristite Ctrl + klik ili Shift + klik).

2. Odaberite Ukloni stupce > Ukloni druge stupce.

   

Četvrti korak: učitavanje upita o proizvodima

U ovom koraku upit Proizvodi učitavaju se na radni list programa Excel.

• Odaberite Polazno > Zatvori & učitaj. Upit će se pojaviti na novom radnom listu programa Excel.

Sažetak: Power Query koraci stvoreni u 1. zadatku

Dok izvodite aktivnosti upita u Power Query, koraci upita stvaraju se i navode u oknu Postavke upita na popisu Primijenjeni koraci. Uz svaki korak upita vezana je odgovarajuća formula značajke Power Query, a naziva se još i jezikom "M". Dodatne informacije o formulama Power Query potražite u članku Stvaranje Power Query formula u programu Excel.

Zadatak	Korak upita	Formula
Uvoz radne knjige programa Excel	Izvor	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Odabir tablice Proizvodi	Navigaciju	= Izvor{[Stavka="Proizvodi",Vrsta="Tablica"]}[Podaci]
Power Query automatski otkriva vrste podataka stupca	Promijenjena vrsta	= 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 stupaca tako da ostanu prikazani samo oni koji vas zanimaju	Uklonjeni drugi stupci	= Table.SelectColumns(FirstRowAsHeader,{"PRODUCTID", "ProductName", "CategoryID", "QuantityPerUnit"})

U ovom zadatku uvozite podatke u radnu knjigu programa Excel iz oglednog OData sažetka sadržaja tvrtke Northwind na http://services.odata.tvrtka ili ustanova/Northwind/Northwind.svc,proširite tablicu sustava Order_Details, uklonite stupce, izračunajte ukupni zbroj retka, pretvorite DatumNarudžbe, grupirajte retke prema ID-uproizvoda i godini, preimenujte upit i onemogućite preuzimanje upita u radnu knjigu programa Excel.

Prvi korak: povezivanje s OData sažetkom sadržaja

1. Odaberite Podaci > dohvatite podatke > iz drugih izvora >iz OData sažetka sadržaja.

2. U dijaloški okvir OData sažetak sadržaja unesite URL OData sažetka sadržaja tvrtke Northwind.

3. Odaberite U redu.

4. U oknu Navigator dvokliknite tablicu Narudžbe .

Drugi korak: proširivanje tablice Detalji_narudžbe

U ovom ćete koraku proširiti tablicu Detalji_narudžbe povezanu s tablicom Narudžbe da biste stupce IDProizvoda, JediničnaCijena i Količina iz tablice Detalji_narudžbe uvrstili u tablicu Narudžbe. Radnjom Proširi stupci iz povezane tablice kombiniraju se s predmetnom tablicom. Kada se upit pokrene, reci iz povezane tablice (Order_Details) kombiniraju se u retke s primarnom tablicom (Narudžbe).

U Power Query stupcu koji sadrži povezanu tablicu u ćeliji se nalazi vrijednost Zapisili Tablica. Ti se stupci nazivaju strukturiranim stupcima. Zapis označava jedan povezani zapis i predstavlja odnosjedan-prema-jedan s trenutnim podacima ili primarnom tablicom. Tablica označava povezanu tablicu i predstavlja odnos jedan-prema-više s trenutnom ili primarnom tablicom. Strukturirani stupac predstavlja odnos u izvoru podataka koji ima relacijski model. Strukturirani stupac, primjerice, označava entitet s pridruživanjem vanjskog ključa u OData sažetku sadržaja ili odnosu vanjskog ključa u SQL Server bazi podataka.

Kad proširite tablicu Detalji_narudžbe, u tablicu Narudžbe dodaju se tri nova stupca i dodatni reci, jedan za svaki redak u ugniježđenoj ili povezanoj tablici.

1. U pretpregledu podataka pomaknite se vodoravno do Order_Details stupca.

2. U stupcu Order_Details odaberite ikonu proširi ().

3. Na padajućem popisu Proširi učinite sljedeće:

   1. Odaberite (Odaberi sve stupce) da biste očistili sve stupce.

   2. Odaberite IDproizvoda, JediničnaCijena i Količina.

   3. Odaberite U redu.

      

      Napomena: U Power Query tablice povezane sa stupcem možete proširiti i zbrojite stupce povezane tablice prije proširivanja podataka u tablici predmeta. Dodatne informacije o izvođenju radnji agregacije potražite u članku Agregacija podataka iz stupca.

Treći korak: uklanjanje drugih stupaca tako da ostanu prikazani samo oni koji vas zanimaju

U ovom koraku uklonit ćete sve stupce osim stupaca DatumNarudbe, IDproizvoda, JediničnaCijena i Količina. 

1. U pretpregledu podataka odaberite sljedeće stupce:

   1. Odaberite prvi stupac IDNarudžbe.

   2. Shift + klik na zadnji stupac, Dostavljač.

   3. Pritisnite Ctrl pa kliknite stupce DatumNarudžbe, Detalji_narudžbe.IDproizvoda, Detalji_narudžbe.JediničnaCijena i Detalji_narudžbe.Količina.

2. Desnom tipkom miša kliknite zaglavlje odabranog stupca, a zatim odaberite Ukloni druge stupce.

Četvrti korak: računanje ukupnog zbroja u retku za svaki redak tablice Detalji_narudžbe

U ovom ćete koraku stvoriti prilagođeni stupac za izračun ukupnog zbroja u retku svakog retka tablice Detalji_narudžbe.

1. U pretpregledu podataka odaberite ikonu tablice () u gornjem lijevom kutu pretpregleda.

2. Kliknite Dodaj prilagođeni stupac.

3. U dijaloškom okviru Prilagođeni stupac u okvir Prilagođena formula stupca unesite [Order_Details.JediničnaCijena] * [Order_Details.Količina].

4. U okvir Novi naziv stupca unesite Ukupni zbroj retka.

5. Odaberite U redu.

Peti korak: pretvaranje stupca DatumNarudžbe s godinom

U ovom ćete koraku transformirati stupac DatumNarudžbe tako da prikazuje godinu datuma narudžbe.

1. U pretpregledu podataka desnom tipkom miša kliknite stupac DatumNarudžbe , a zatim odaberite Pretvori > godina.

2. Preimenujte stupac DatumNarudžbe u Godina:

   1. dvokliknite stupac DatumNarudžbe pa unesite Godina ili

   2. Right-Click stupcu DatumNarudžbe odaberite Preimenuj, a zatim unesite Godina.

Šesti korak: grupiranje redaka prema kriterijima IDProizvoda i Godina

1. U pretpregledu podatakaodaberite Godinai Order_Details.IDProizvoda.

2. Right-Click zaglavlja pa odaberite Grupiraj po.

3. U dijaloškom okviru Grupiraj prema učinite sljedeće:

   1. U tekstni okvir Novi naziv stupca unesite Total Sales.

   2. Na padajućem izborniku Operacija odaberite Zbroj.

   3. Na padajućem izborniku Stupac odaberite Line Total

4. Odaberite U redu.

   

Sedmi korak: preimenovanje upita

Prije uvoza podataka o prodaji u Excel preimenujte upit:

• U oknu Postavke upita u okvir Naziv unesiteUkupna prodaja.

Rezultati: Konačni upit za zadatak 2

Kad dovršite sve korake, imat ćete upit Ukupna prodaja za OData sažetak sadržaja tvrtke Northwind.

Sažetak: Power Query koraci stvoreni u 2. zadatku 

Dok izvodite aktivnosti upita u Power Query, koraci upita stvaraju se i navode u oknu Postavke upita na popisu Primijenjeni koraci. Uz svaki korak upita vezana je odgovarajuća formula značajke Power Query, a naziva se još i jezikom "M". Dodatne informacije o formulama Power Query potražite u članku Dodatne informacije Power Query formulama.

Zadatak	Korak upita	Formula
Povezivanje s OData sažetkom sadržaja	Source	= OData.Feed("http://services.odata.tvrtka ili ustanova/Northwind/Northwind.svc", null, [Implementacija="2.0"])
Odabir tablice	Navigacija	= Izvor{[Naziv="Narudžbe"]}[Podaci]
Proširivanje tablice Order_Details	Proširivanje tablice Order_Details	= Table.ExpandTableColumn(Narudžbe, "Order_Details", {"IDProizvoda", "JediničnaCijena", "Količina"}, {"Order_Details.IDProizvoda", "Order_Details.JediničnaCijena", "Order_Details.Količina"})
Uklanjanje drugih stupaca tako da ostanu prikazani samo oni koji vas zanimaju	RemovedColumns	= Table.RemoveColumns(#"Expand Order_Details",{"ORDERID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Računanje ukupnog zbroja u retku za svaki redak tablice Detalji_narudžbe	Dodano 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])
Promijeni u smisleniji naziv, Lne Total	Preimenovani stupci	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Pretvaranje stupca OrderDate tako da prikazuje godinu	Izdvojena godina	= Table.TransformColumns(#"Grupirani reci",{{"Godina", Date.Year, Int64.Type}})
Promijeni u  smisleniji nazivi, DatumNarudžbe i Godina	Preimenovani stupci 1	Table.RenameColumns (TransformedColumn,{{"DatumNarudžbe", "Godina"}})
Grupiranje redaka prema ProductID i Year	GroupedRows	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

Power Query omogućuje kombiniranje većeg broja upita spajanjem ili dodavanjem. Operacija spajanja izvodi se u dodatku Power Query na bilo kojem upitu tabličnog oblika, neovisno o izvoru podataka iz kojeg podaci dolaze. Dodatne informacije o kombiniranju izvora podataka potražite u članku Kombiniranje više upita.

U ovom zadatku kombinirate upite Proizvodi i Ukupna prodaja pomoću upita Spajanje i Proširivanje, a zatim upit Ukupna prodaja po proizvodu učitavate u podatkovni model programa Excel.

Prvi korak: spajanje stupca IDProizvoda i upita Ukupna prodaja

1. U radnoj knjizi programa Excel pomaknite se do upita Proizvodi na kartici Radni list Proizvodi.

2. Odaberite ćeliju u upitu, a zatim Upit > spajanje.

3. U dijaloškom okviru Spajanje odaberite Proizvodi kao primarnu tablicu, a zatim Kao sekundarni ili povezani upit za spajanje odaberite Ukupna prodaja. Ukupna prodaja postat će novi strukturirani stupac s ikonom proširenja.

4. Da bi upit Ukupna prodaja odgovarao upitu Proizvodi prema stupcu IDproizvoda, u tablici Proizvodi odaberite stupac IDproizvoda te stupac Detalji_narudžbe.IDproizvoda u tablici Ukupna prodaja.

5. U dijaloškom okviru Razine zaštite privatnosti učinite sljedeće:

   1. Odaberite Organizacijski kao razinu zaštite privatnosti za oba izvora podataka.

   2. Odaberite Spremi.

6. Odaberite U redu.

   Napomena o sigurnosti: Postavka Razine zaštite privatnosti korisniku onemogućuje slučajno kombiniranje podataka iz više izvora podataka koji su možda privatni ili pak pripadaju tvrtci ili ustanovi. Ovisno o upitu korisnik može nenamjerno poslati podatke iz privatnog izvora podataka nekom drugom, koji pak može biti zlonamjeran. Power Query analizira svaki izvor podataka i klasificira ga prema definiranim razinama zaštite privatnosti: javna, od tvrtke ili ustanove i privatna. Dodatne informacije o razinama privatnosti potražite u članku Postavljanje razina privatnosti.

   

Rezultat

Operacija spajanja stvara upit. Rezultat upita sadrži sve stupce iz primarne tablice (Proizvodi) i jedan strukturirani stupac tablice s povezanom tablicom (Ukupna prodaja). Odaberite ikonu Proširi da biste dodali nove stupce u primarnu tablicu iz sekundarne ili povezane tablice.

Drugi korak: proširivanje spojenog stupca

U ovom koraku spojeni stupac s nazivom NewColumn proširite da biste stvorili dva nova stupca u upitu Proizvodi: Godinai Ukupna prodaja.

1. U pretpregledu podatakaodaberite Proširi ikonu () uz stavku NewColumn.

2. Na padajućem popisu Proširi:

   1. Odaberite (Odaberi sve stupce) da biste očistili sve stupce.

   2. Odaberite Godina i Ukupna prodaja.

   3. Odaberite U redu.

3. Preimenujte ta dva stupca u Godina i Ukupna prodaja.

4. Da biste saznali koji su proizvodi i u kojim su godinama proizvodi dobili najveći volumen prodaje, odaberite Sortiraj silazno po ukupnojprodaji.

5. Promijenite naziv upita u Ukupna prodaja po proizvodu.

Rezultat

Treći korak: učitavanje upita Ukupna prodaja po proizvodu u podatkovni model programa Excel

U ovom ćete koraku učitati upit u podatkovni model programa Excel da biste stvorili izvješće povezano s rezultatom upita. Nakon učitavanja podataka u podatkovni model programa Excel možete koristiti Power Pivot da biste dodatno analizirali podatke.

1. Odaberite Polazno > Zatvori & Učitaj.

2. U dijaloškom okviru Uvoz podataka obavezno odaberite Dodaj ove podatke u podatkovni model. Da biste saznali više o korištenju ovog dijaloškog okvira, odaberite upitnik (?).

Rezultat

Imate upit Ukupna prodaja po proizvodu koji kombinira podatke iz datoteke Products.xlsx Northwind OData sažetka sadržaja. Ovaj se upit primjenjuje na model dodatka Power Pivot. Osim toga, promjene upita mijenjaju i osvježavaju konačnu tablicu u podatkovnom modelu.

Sažetak: Power Query korake stvorene u 3. zadatku

Dok izvodite aktivnosti spajanja upita u Power Query, koraci upita stvaraju se i navode u oknu Postavke upita na popisu Primijenjeni koraci. Uz svaki korak upita vezana je odgovarajuća formula značajke Power Query, a naziva se još i jezikom "M". Dodatne informacije o formulama Power Query potražite u članku Dodatne informacije Power Query formulama.

Zadatak	Korak upita	Formula
Spajanje stupca IDproizvoda s upitom Ukupna prodaja	Source (izvor podataka za operaciju Spoji)	= Table.NestedJoin(Products, {"PRODUCTID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Proširivanje stupca za spajanje	Proširena ukupna prodaja	= Table.ExpandTableColumn(Izvor, "Ukupna prodaja", {"Godina", "Ukupna prodaja"}, {"Ukupna prodaja.Godina", "Ukupna prodaja.Ukupna prodaja"})
Preimenovanje dva stupca	Preimenovani stupci	= Table.RenameColumns(#"Proširena ukupna prodaja",{{"Ukupna prodaja.Godina", "Godina"}, {"Ukupna prodaja.Ukupna prodaja", "Ukupna prodaja"}})
Sortiraj ukupnu prodaju uzlaznim redoslijedom	Sortirani reci	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})