U ovom praktičnom vodiču pomoću Uređivač upita dodatka Power Query uvezite 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. izvesti korake za transformaciju i prikupljanje te kombinirati podatke iz obaju izvora da biste stvorili izvješće o ukupnoj prodaji po proizvodu i godini .
Za dovršetak 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 u prethodnom odjeljku) u radnu knjigu programa Excel. Zatim promaknite retke u zaglavlja stupaca, uklonite neke stupce i učitajte 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điteProducts.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 pa 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, IDKkategorije 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, on stvara korake upita i navodi ih 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 dokumentaciji dodatka Power Query.
| 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 u http://services.odata.org/Northwind/Northwind.svc, proširiti tablicu Order_Details, ukloniti stupce, izračunati ukupni zbroj u retku, transformirati DatumNarudžbe, grupirati retke prema IDProizvoda i Godina, preimenovati upit i onemogućiti 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 (Power Query)).
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čna cijena 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. - Odaberite 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 i unesite Godina ili
- Desnom tipkom miša kliknite stupac DatumNarudžbe , odaberite Preimenuj pa unesite Godina.
Šesti korak: grupiranje redaka prema kriterijima IDProizvoda i Godina
U pretpregledu podataka odaberite Godina i Order_Details.IDProizvoda.
Desnom tipkom miša kliknite jedno od zaglavlja, a zatim 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, imate upit Ukupna prodaja za OData sažetak sadržaja tvrtke Northwind.
Sažetak: Koraci za Power Query stvoreni u drugom zadatku
Dok u značajci Power Query izvodite aktivnosti vezane uz upite, on stvara korake upita i navodi ih 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 dokumentaciji dodatka Power Query.
| Zadatak | Korak upita | Formula |
|---|---|---|
| Povezivanje s OData sažetkom sadržaja | Source | = OData.Feed("http://services.odata.tvrtka ili ustanova/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. Operaciju spajanja možete izvesti na bilo kojem upitu Power Query tabličnog oblika, bez obzira na izvor podataka. Dodatne informacije o kombiniranju izvora podataka potražite u članku Kombiniranje više upita (Power Query).
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 idite 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 postaje 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 članku Postavljanje razina privatnosti (Power Query)).
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 (
) pokraj tablice 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 koraku učitat ćete 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 za 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 dokumentaciji 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}}) |