U ovom praktičnom vodiču pomoću Uređivač upita dodatka Power Query možete uvesti podatke iz lokalne datoteke programa Excel koja sadrži podatke o proizvodu te iz OData sažetka sadržaja koji sadrži podatke o narudžbi proizvoda. Izvest ćete korake za transformaciju i prikupljanje te kombinirati podatke iz obaju izvora da biste stvorili izvješće o ukupnoj prodaji po proizvodu i godini.
Za izvođenje ovog vodiča potrebna vam je radna knjiga Proizvodi . U dijaloškom okviru Spremi kao datoteci dajte naziv Proizvodi i narudžbe.xlsx.
Prvi zadatak: uvoz proizvoda u radnu knjigu programa Excel
U ovom ćete zadatku uvesti proizvode iz datoteke Proizvodi i Orders.xlsx (preuzete i preimenovane gore) u radnu knjigu programa Excel, promaknuti retke u zaglavlja stupaca, ukloniti neke stupce i učitati upit na radni list.
Prvi korak: povezivanje s radnom knjigom programa Excel
- Stvorite radnu knjigu programa Excel.
- Odaberite podatke>Dohvaćanje podataka>iz datoteke>iz radne knjige.
- U dijaloškom okviru Uvoz podataka potražite i pronađite Products.xlsx datoteku koju ste preuzeli, a zatim odaberite Otvori.
- U oknu navigatora dvokliknite tablicu Proizvodi . Pojavit će se uređivač dodatka Power Query.
Drugi korak: pregled koraka upita
Power Query po zadanom automatski dodaje nekoliko koraka kao pogodnost. Da biste saznali više, proučite svaki korak u odjeljku Primijenjeni koraci u oknu Postavke upita .
- Desnom tipkom miša kliknite korak Izvor pa odaberite Uređivanje postavki. Taj je korak stvoren prilikom uvoza radne knjige.
- Desnom tipkom miša kliknite korak navigacije , a zatim odaberite Uređivanje postavki. Taj je korak stvoren kada ste odabrali tablicu u navigacijskom dijaloškom okviru.
- Desnom tipkom miša kliknite korak Promijenjena vrsta pa odaberite Uređivanje postavki. Taj je korak stvorio Power Query koji je zaključio vrste podataka za svaki stupac. Odaberite strelicu prema dolje s desne strane 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.
- U pretpregledu podataka odaberite stupce IDProizvoda, NazivProizvoda, IDKkategorije i JediničnaKoličina (koristite Ctrl+klik ili Shift+klik).
- Odaberite Ukloni stupce>Ukloni druge stupce.
Četvrti korak: učitavanje upita o proizvodima
U ovom koraku učitat ćete upit Proizvodi na radni list programa Excel.
- Odaberite Polazno>Zatvori & Učitaj. Upit će se pojaviti na novom radnom listu programa Excel.
Sažetak: Koraci za Power Query stvoreni u prvom zadatku
Dok u značajci Power Query izvodite aktivnosti vezane uz upite, stvaraju se koraci upita 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 dodatka Power Query potražite u članku Stvaranje formula dodatka Power Query u programu Excel.
| Zadatak | Korak upita | Formula |
|---|---|---|
| Uvoz radne knjige programa Excel | Izvor | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true) |
| Odaberite tablicu Proizvodi | Navigacija | = Izvor{[Stavka="Proizvodi",Vrsta="Tablica"]}[Podaci] |
| Power Query automatski prepoznaje vrste podataka stupca | Promijenjena vrsta | = Table.TransformColumnTypes( Products_Table,{{"IDproizvoda", Int64.Type}, {"NazivProizvoda", type text}, {"IDdobavljača", Int64.Type}, {"IDkategorije", Int64.Type}, {"JediničnaJediničnaJedinica", upišite text}, {"JediničnaCijena", Type number}, {"JedinicaInSkladište", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Otkazano", vrsta logička}}) |
| Uklanjanje drugih stupaca tako da ostanu prikazani samo oni koji vas zanimaju | Uklonjeni su drugi stupci | = Table.SelectColumns(FirstRowAsHeader,{"IDproizvoda", "NazivProizvoda", "IDkategorije", "JediničnaKoličina"}) |
Drugi zadatak: uvoz podataka o narudžbi iz OData sažetka sadržaja
U ovom ćete zadatku uvesti podatke u radnu knjigu programa Excel iz oglednog Northwind OData sažetka sadržaja na adresi http://services.odata.tvrtka ili ustanova/Northwind/Northwind.svc, proširite tablicu Order_Details, uklonite stupce, izračunajte ukupni zbroj u retku, pretvorite DatumNarudžbe, grupirajte retke prema stupcima IDProizvoda i Godina, preimenujte upit i onemogućite preuzimanje upita u radnu knjigu programa Excel.
Prvi korak: povezivanje s OData sažetkom sadržaja
- Odabir podataka>Dohvaćanje podataka>iz drugih izvora>iz OData sažetka sadržaja
- U dijaloški okvir OData sažetak sadržaja unesite URL OData sažetka sadržaja tvrtke Northwind.
- Odaberite U redu.
- U oknu navigatora dvokliknite tablicu Narudžbe .
Drugi korak: proširivanje tablice Order_Details
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. Kad pokrenete upit, reci iz povezane tablice (Order_Details) uvrštavaju se među retke s primarnom tablicom (Narudžbe).
U dodatku Power Query stupac koji sadrži povezanu tablicu u ćeliji sadrži vrijednost Zapis ili Tablica. To se naziva strukturiranim stupcima. Zapis označava jedan povezani zapis i predstavlja odnos jedan-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ženim vanjskim ključem u OData sažetku sadržaja ili odnos vanjskog ključa u bazi podataka sustava SQL Server.
Kada proširite tablicu Order_Details , u tablicu Narudžbe dodaju se tri nova stupca i dodatni reci, jedan za svaki redak u ugniježđenoj ili povezanoj tablici.
U pretpregledu podataka vodoravno se pomaknite do Order_Details stupca.
U Order_Details stupcu odaberite ikonu za proširivanje (
Na padajućem popisu Proširi učinite sljedeće:
Odaberite (Odaberi sve stupce) da biste uklonili sadržaj svih stupaca.
Odaberite IDProizvoda, Jedinična cijena i Količina.
Odaberite U redu.
Napomena
U dodatku Power Query možete proširiti tablice na koje se odnose stupci i zbrojiti stupce povezane tablice prije proširivanja podataka u predmetnoj tablici. 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.
U pretpregledu podataka odaberite sljedeće stupce:
- Odaberite prvi stupac, IDNarudžbe.
- Shift+Kliknite zadnji stupac, Dostavljač.
- Pritisnite Ctrl pa kliknite stupce DatumNarudžbe, Detalji_narudžbe.IDproizvoda, Detalji_narudžbe.JediničnaCijena i Detalji_narudžbe.Količina.
Desnom tipkom miša kliknite zaglavlje odabranog stupca, a zatim odaberite Ukloni druge stupce.
Četvrti korak: izračun ukupnog zbroja u retku za svaki redak Order_Details
U ovom ćete koraku stvoriti prilagođeni stupac za izračun ukupnog zbroja u retku svakog retka tablice Detalji_narudžbe.
- U pretpregledu podataka odaberite ikonu tablice (
) u gornjem lijevom kutu pretpregleda. - Kliknite Dodaj prilagođeni stupac.
- U dijaloškom okviru Prilagođeni stupac u okvir formule Prilagođeni stupac unesite [Order_Details.JediničnaCijena] * [Order_Details.Količina].
- U okvir Naziv novog stupca unesite Ukupni zbroj u retku.
- 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.
U pretpregledu podataka desnom tipkom miša kliknite stupac DatumNarudžbe , a zatim odaberite Pretvori>godinu.
Preimenujte stupac DatumNarudžbe u Godina:
- dvokliknite stupac DatumNarudžbe pa unesite Godina ili
- Right-Click stupcu DatumNarudžbe , odaberite Preimenuj i unesite Godina.
Šesti korak: grupiranje redaka prema kriterijima IDProizvoda i Godina
U pretpregledu podataka odaberite Godina i Order_Details.IDProizvoda.
Right-Click jedno od zaglavlja pa odaberite Grupiraj po.
U dijaloškom okviru Grupiraj prema učinite sljedeće:
- U tekstni okvir Novi naziv stupca unesite Total Sales.
- Na padajućem izborniku Operacija odaberite Zbroj.
- Na padajućem izborniku Stupac odaberite Line Total
Odaberite U redu.
Sedmi korak: preimenovanje upita
Prije uvoza podataka o prodaji u Excel preimenujte upit:
- U oknu Postavke upita u okvir Naziv unesite Ukupnu prodaju.
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: Koraci dodatka Power Query stvoreni u drugom zadatku
Dok u značajci Power Query izvodite aktivnosti vezane uz upite, stvaraju se koraci upita 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 dodatka Power Query potražite u članku Informacije o formulama dodatka Power Query.
| Zadatak | Korak upita | Formula |
|---|---|---|
| Povezivanje s OData sažetkom sadržaja | Source | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="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",{"IDnarudžbe", "IDklijenta", "IDzaposlenika", "TraženiDatum", "DatumIsporuke", "NačinIsporuke", "Vozarina", "ImeZaIsporuku", "AdresaZaIsporuku", "GradZaIsporuku", "RegijaZaIsporuku", "PoštanskiBrojZaIsporuku", "DržavaZaIsporuku", "Klijent", "Zaposlenik", "Isporučitelj"}) |
| Računanje ukupnog zbroja u retku za svaki redak tablice Detalji_narudžbe | Prilagođeno |
= Table.AddColumn(RemovedColumns; "Prilagođeno", each [Order_Details.JediničnaCijena] * [Order_Details.Količina]) = Table.AddColumn(#"Prošireno Order_Details", "Ukupni zbroj u retku", each [Order_Details.JediničnaCijena] * [Order_Details.Količina]) |
| Promjena u smisleniji naziv, Lne Total | Preimenovani stupci | = Table.RenameColumns(InsertedCustom,{{"Prilagođeno", "Ukupni zbroj u retku"}}) |
| Pretvaranje stupca OrderDate tako da prikazuje godinu | Izdvojena godina | = Table.TransformColumns(#"Grupirani reci",{{"Godina", Date.Year, Int64.Type}}) |
| Promijeni u smisleniji nazivi, OrderDate i Year |
Preimenovani stupci 1 |
Table.RenameColumns (TransformedColumn,{{"DatumNarudžbe", "Godina"}}) |
| Grupiranje redaka prema ProductID i Year | GroupedRows | = Table.Group(RenamedColumns1, {"Godina", "Order_Details.IDProizvoda"}, {{"Ukupna prodaja", each List.Sum([Ukupni zbroj u retku]), type number}}) |
Treći zadatak: kombiniranje upita Proizvodi i Ukupna prodaja
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 ćete zadatku kombinirati upite Proizvodi i Ukupna prodaja pomoću upita spajanja i operacije proširivanja , a zatim učitati upit Ukupna prodaja po proizvodu u podatkovni model programa Excel.
Prvi korak: spajanje stupca IDProizvoda i upita Ukupna prodaja
U radnoj knjizi programa Excel prijeđite na upit Proizvodi na kartici radnog lista Proizvodi .
Odaberite ćeliju u upitu, a zatim Spajanje upita>.
U dijaloškom okviru Spajanje odaberite Proizvodi kao primarnu tablicu, a zatim Ukupna prodaja kao sekundarni ili povezani upit za spajanje. Ukupna prodaja postat će novi strukturirani stupac s ikonom za proširivanje.
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.
U dijaloškom okviru Razine zaštite privatnosti učinite sljedeće:
- Odaberite Organizacijski kao razinu zaštite privatnosti za oba izvora podataka.
- Odaberite Spremi.
Odaberite U redu.
Napomena
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 zaštite privatnosti potražite u odjeljku Postavljanje razina privatnosti.
Rezultat
Operacijom spajanja stvara se upit. Rezultat upita sadrži sve stupce iz primarne tablice (Proizvodi) i jedan strukturirani stupac tablice u povezanoj tablici (Ukupna prodaja). Odaberite ikonu Proširi da biste dodali nove stupce iz sekundarne ili povezane tablice u primarnu tablicu.
Drugi korak: proširivanje spojenog stupca
U ovom ćete koraku proširiti spojeni stupac s nazivom NoviStupac da biste u upitu o proizvodima stvorili dva nova stupca: Godina i Ukupna prodaja.
U pretpregledu podataka odaberite ikonu Proširi (
) uz stavku NoviStupac.Na padajućem popisu Proširivanje učinite sljedeće:
- Odaberite (Odaberi sve stupce) da biste uklonili sadržaj svih stupaca.
- Odaberite Godina i ukupnu prodaju.
- Odaberite U redu.
Preimenujte ta dva stupca u Godina i Ukupna prodaja.
Da biste saznali koji su proizvodi i u kojim su godinama ostvarili najveći obujam prodaje, odaberite Sortiraj silazno po ukupnoj prodaji.
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 sastavili izvješće povezano s rezultatima upita. Kada učitate podatke u podatkovni model programa Excel, možete koristiti Power Pivot za daljnju analizu podataka.
- Odaberite Polazno>Zatvori & Učitaj.
- U dijaloškom okviru Uvoz podataka obavezno odaberite mogućnost Dodaj ove podatke u podatkovni model. Za dodatne informacije o korištenju tog dijaloškog okvira odaberite upitnik (?).
Rezultat
Imate upit Ukupna prodaja po proizvodu u kojem su kombinirani podaci iz datoteke Products.xlsx i OData sažetka sadržaja tvrtke Northwind. Taj se upit primjenjuje na model dodatka Power Pivot. Promjene upita mijenjaju i osvježuju konačnu tablicu u podatkovnom modelu.
Sažetak: Koraci dodatka Power Query stvoreni u 3. zadatku
Dok u značajci Power Query izvodite aktivnosti spajanja upita, stvaraju se koraci upita 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 dodatka Power Query potražite u članku Informacije o formulama dodatka Power Query.
| Zadatak | Korak upita | Formula |
|---|---|---|
| Spajanje stupca IDproizvoda s upitom Ukupna prodaja | Source (izvor podataka za operaciju Spoji) | = Table.NestedJoin(Products, {"IDproizvoda"}, #"Ukupna prodaja", {"Order_Details.IDProizvoda"}, "Ukupna prodaja", 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.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(#"Preimenovani stupci",{{"Ukupna prodaja", Order.Ascending}}) |