Napomena: Power Query poznat je pod nazivom Dohvaćanje i pretvaranje u programu Excel 2016. Ovdje navedene informacije odnose se na oba naziva. Dodatne informacije potražite u članku Funkcija dohvaćanja i pretvaranja u aplikaciji Excel 2016.
U ovom ćete vodiču pomoću uređivača upita Power Querya uvesti podatke iz lokalne datoteke programa Excel koja sadrži informacije o proizvodu i iz OData sažetka sadržaja koji sadrži informacije 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.
Da biste izvršili ovaj vodič, potrebni su vam radne knjige proizvodi i narudžbe . U dijaloškom okviru Spremi kao datoteci dajte naziv Proizvodi i narudžbe.xlsx.
U ovom praktičnom vodiču:
-
Drugi zadatak: uvoz podataka o narudžbi iz OData sažetka sadržaja
-
Četvrti korak: računanje ukupnog zbroja u retku za svaki redak tablice Detalji_narudžbe
-
Šesti korak: grupiranje redaka prema kriterijima IDProizvoda i Godina
-
Osmi korak: onemogućivanje preuzimanja upita u radnu knjigu programa Excel
-
Treći zadatak: kombiniranje upita Proizvodi i Ukupna prodaja
Prvi zadatak: uvoz proizvoda u radnu knjigu programa Excel
U ovom zadatku uvezite proizvode iz datoteke proizvodi i narudžbe. xlsx u radnu knjigu programa Excel.
Prvi korak: povezivanje s radnom knjigom programa Excel
-
Stvorite radnu knjigu programa Excel.
-
Na kartici vrpce POWER QUERY kliknite Iz datoteke > Iz programa Excel.
-
U dijaloškom okviru programa Excel za pregledavanje potražite ili upišite put do datoteke Proizvodi i narudžbe.xlsx da biste je uvezli ili umetnuli vezu na nju.
-
U oknu Navigator dvokliknite radni list Proizvodi ili kliknite Proizvodi, a zatim Uredi upit. Prilikom uređivanja upita ili povezivanja s novim izvorom podataka pojavljuje se prozor uređivača upita.
Napomena: Vrlo kratak videozapis o prikazu uređivača upita potražite na kraju ovog članka.
Drugi korak: pretvaranje prvog retka u zaglavlja stupaca tablice
U rešetci Pretpregled upita prvi redak tablice ne sadrži nazive stupaca. Da biste prvi redak tablice pretvorili u zaglavlja stupaca, učinite sljedeće:
-
Kliknite ikonu tablice (
) u gornjem lijevom kutu pretpregleda podataka. -
Kliknite Koristi prvi redak kao zaglavlja.
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 rešeci Pretpregled upita odaberite stupce IDproizvoda, NazivProizvoda, IDkategorije i JediničnaKoličina (pomoću tipke Ctrl ili Shift i klika mišem).
-
Na vrpci Uređivač upita kliknite Uklanjanje stupaca > Ukloni druge stupce ili desnom tipkom miša kliknite naslov stupca, a zatim kliknite Ukloni druge stupce.
Stvoreni koraci za Power Query
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 dodatka Power Query, a naziva se još i jezikom "M". Dodatne informacije o jeziku za formule dodatka Power Query potražite u članku Informirajte se o formulama dodatka Power Query.
Zadatak | Korak upita | Formula |
|---|---|---|
|
Povezivanje s radnom knjigom programa Excel |
Source |
Source{[Name="Proizvodi"]}[Data] |
|
Pretvaranje prvog retka u zaglavlja stupaca tablice |
FirstRowAsHeader |
(Proizvodi) |
|
Uklanjanje drugih stupaca tako da ostanu prikazani samo oni koji vas zanimaju |
RemovedOtherColumns |
(FirstRowAsHeader,{"IDproizvoda", "NazivProizvoda", "IDkategorije", "JediničnaKoličina"}) |
Četvrti korak: uvoz upita o proizvodima
U ovom koraku u radnu knjigu programa Excel uvozite upit Proizvodi.
-
Na vrpci uređivača upita kliknite Primijeni i zatvori. Rezultati se prikazuju na novom radnom listu programa Excel.
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.org/Northwind/Northwind.svc.
Prvi korak: povezivanje s OData sažetkom sadržaja
-
Na kartici vrpce POWER QUERY kliknite 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.
-
Kliknite U redu.
-
U oknu Navigator dvokliknite tablicu Narudžbe ili kliknite Narudžbe, a zatim Uredi .
Napomena: Kad zadržite pokazivač miša na tablici, prikazat će se potpaleta pretpregleda tablice.
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. Kad pokrenete upit, reci iz povezane tablice (Detalji_narudžbe) uvrštavaju se među retke predmetne tablice (Narudžbe).
U dodatku Power Query stupac koji sadrži vezu na povezanu tablicu sadrži vezu na unos ili tablicu. Veza za unos prelazi na jedan srodni zapis i predstavljajedan na jedan odnos s tablicom predmet.Veza tablice prelazi na povezanu tablicu i predstavlja odnos jedan-prema-više s tablicom predmet. Veza predstavlja svojstva navigacije u izvoru podataka unutar relacijskih modela. Za OData sažetke sadržaja svojstva navigacije predstavljaju entitet s Udruženjem vanjskog ključa. U bazi podataka, kao što je SQL Server, svojstva navigacije predstavljaju vanjske ključne odnose u bazi podataka.
Veza na proširivanje tablice Detalji_narudžbe
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.
-
U oknu Pretpregled upita pomaknite se na stupac Detalji_narudžbe.
-
U stupcu Order_Details kliknite ikonu proširenja (
). -
Na padajućem popisu Proširi učinite sljedeće:
-
kliknite (Odaberi sve stupce) da biste uklonili sadržaj svih stupaca
-
Kliknite IDproizvoda, JediničnaCijena i Količina.
-
Kliknite U redu.
Napomena: U dodatku Power Query možete proširiti tablice na koje stupci sadrže veze, a možete i izvoditi radnje agregacije na stupcima 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 prethodnom ste zadatku koristili naredbu Ukloni druge stupce. U ovom ćete zadatku ukloniti odabrane stupce.
Uklanjanje odabranih stupaca
-
U oknu Pretpregled upita odaberite sve stupce:
-
Kliknite prvi stupac (IDnarudžbe).
-
Pritisnite Shift pa kliknite posljednji stupac (Shipper).
-
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 pa kliknite Ukloni 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.
Računanje ukupnog zbroja u retku za svaki redak tablice Detalji_narudžbe
-
U oknu Pretpregled upita kliknite ikonu tablice (
) u gornjem desnom kutu pretpregleda. -
Kliknite Umetni stupac > Prilagođeni.
-
U dijaloški okvir Umetanje prilagođenog stupca u tekstni okvir Formula prilagođenog stupca unesite [Detalji_narudžbe.JediničnaCijena] * [Detalji_narudžbe.Količina].
-
U tekstni okvir Novi naziv stupca unesite Ukupni zbroj u retku.
-
Kliknite 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 rešetci pretpregleda desnom tipkom miša kliknite stupac DatumNarudžbe pa kliknite Transformiraj > Godina.
-
Preimenujte stupac DatumNarudžbe u Godina:
-
dvokliknite stupac DatumNarudžbe pa unesite Godina ili
-
desnom tipkom miša kliknite stupac DatumNarudžbe pa kliknite Preimenuj i unesite Godina.
-
Šesti korak: grupiranje redaka prema kriterijima IDProizvoda i Godina
-
U rešetci Pretpregled upita odaberite Godina i Detalji_narudžbe.IDProizvoda.
-
Desnom tipkom miša kliknite jedno od zaglavlja pa kliknite Grupiraj prema.
-
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
-
-
Kliknite U redu.
Sedmi korak: preimenovanje upita
Prije no što u Excel uvezete podatke o prodaji, upitu dodijelite naziv UkupnaProdaja:
-
U oknu Postavke upita u tekstni okvir Naziv unesite UkupnaProdaja.
Krajnji rezultati upita
Kad dovršite sve korake, imat ćete upit Ukupna prodaja za OData sažetak sadržaja tvrtke Northwind.
Stvoreni koraci za Power Query
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 dodatka Power Query, a naziva se još i jezikom "M". Dodatne informacije o jeziku za formule dodatka Power Query potražite u članku Informirajte se o formulama dodatka Power Query.
Zadatak | Korak upita | Formula |
|---|---|---|
|
Povezivanje s OData sažetkom sadržaja |
Source |
Source{[Name="Narudžbe"]}[Data] |
|
Proširivanje tablice Order_Details |
Proširivanje tablice Order_Details |
(Narudžbe, "Detalji_narudžbe", {"IDproizvoda", "JediničnaCijena", "Količina"}, {"Detalji_narudžbe.IDproizvoda", "Detalji_narudžbe.JediničnaCijena", "Detalji_narudžbe.Količina"}) |
|
Uklanjanje drugih stupaca tako da ostanu prikazani samo oni koji vas zanimaju |
RemovedColumns |
(#"Expand Detalji_narudžbe",{"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 |
InsertedColumns |
(RemovedColumns, "Prilagođeno", each [Detalji_narudžbe.JediničnaCijena] * [Detalji_narudžbe.Količina]) |
|
Pretvaranje stupca OrderDate tako da prikazuje godinu |
RenamedColumns |
(InsertedCustom,{{"Prilagođeno", "Ukupni zbroj u retku"}}) |
|
TransformedColumn |
(RenamedColumns,{{"DatumNarudžbe", Datum.Godina}}) |
|
|
RenamedColumns1 |
(TransformedColumn,{{"DatumNarudžbe", "Godina"}}) |
|
|
Grupiranje redaka prema ProductID i Year |
GroupedRows |
Tablica. Group
|
Osmi korak: onemogućivanje preuzimanja upita u radnu knjigu programa Excel
Budući da upit Ukupna prodaja ne predstavlja krajnje izvješće o ukupnoj prodaju po proizvodu i godini, onemogućite preuzimanje upita u radnu knjigu programa Excel. Kad je u oknu Postavke upita mogućnost Prenesi na radni list postavljena na Isključeno, podatkovni rezultat upita ne preuzima se, no upit se i dalje može kombinirati s drugim upitima radi dolaska do željenog rezultata. U sljedećem ćete zadatku naučiti kako kombinirati taj upit s upitom Proizvodi.
Onemogućivanje preuzimanja upita
-
U oknu Postavke upita poništite okvir Učitaj na radni list.
-
Na vrpci uređivača upita kliknite Primijeni i zatvori. U oknu Upiti radne knjige uz upit Ukupna prodaja prikazivat će se Preuzimanje je onemogućeno.
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 koraka upita Spajanje i Proširivanje.
Prvi korak: spajanje stupca IDProizvoda i upita Ukupna prodaja
-
U radnoj knjizi programa Excel prijeđite na upit Proizvodi na listu 2.
-
Na kartici UPIT na vrpci kliknite Spoji.
-
U dijaloškom okviru Spajanje odaberite Proizvodi kao primarnu tablicu, a Ukupna prodaja kao sekundarni ili povezani upit namijenjen spajanju. Ukupna prodaja postat će novi stupac s mogućnošću proširivanja.
-
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.
-
Kliknite Spremi.
-
-
Kliknite 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 informacija o razinama zaštite privatnosti potražite u članku Razine zaštite privatnosti.
Kad kliknete U redu, operacija Spoji stvara upit. Rezultat upita sadrži sve stupce iz primarne tablice (Proizvodi) te jedan stupac s vezom za navigaciju na povezanu tablicu (Ukupna prodaja). Radnja Proširi dodaje nove stupce iz povezane tablice u primarnu ili predmetnu tablicu.
Drugi korak: proširivanje stupca za spajanje
U ovom ćete koraku proširiti stupac za spajanje s nazivom Newcolumn da biste stvorili dva nova stupca u upitu proizvodi : godina i Ukupna prodaja.
Veza na proširivanje tablice NoviStupac
-
U rešetki pretpregleda upita kliknite ikonu proširenja newcolumn (
). -
Na padajućem popisu Proširi učinite sljedeće:
-
kliknite (Odaberi sve stupce) da biste uklonili sadržaj svih stupaca
-
Kliknite Godina i Ukupna prodaja.
-
Kliknite U redu.
-
-
Preimenujte ta dva stupca u Godina i Ukupna prodaja.
-
Sortirajte silazno prema stupcu Ukupna prodaja da biste saznali koji su proizvodi i kojih godina prodani u najvećoj količini.
-
Promijenite naziv upita u Ukupna prodaja po proizvodu.
Stvoreni koraci za Power Query
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 jeziku za formule značajke Power Query potražite u članku Informirajte se o formulama dodatka Power Query.
Zadatak | Korak upita | Formula |
|---|---|---|
|
Spajanje stupca IDproizvoda s upitom Ukupna prodaja |
Source (izvor podataka za operaciju Spoji) |
(Proizvodi,{"IDproizvoda"},#"Ukupna prodaja",{"Detalji_narudžbe.IDproizvoda"},"NoviStupac") |
|
Proširivanje stupca za spajanje |
ExpandNewColumn |
(Izvor, "NoviStupac", {"Godina", "Ukupna prodaja"}, {"NoviStupac.Godina", "NoviStupac.Ukupna prodaja"}) |
|
RenamedColumns |
(#"Expand NoviStupac",{{"NoviStupac.Godina", "Godina"}, {"NoviStupac.Ukupna prodaja", "Ukupna prodaja"}}) |
|
|
SortedRows |
(RenamedColumns,{{"Ukupna prodaja", Order.Descending}}) |
Treći korak: učitavanje upita Ukupna prodaja po proizvodu u podatkovni model programa Excel
U ovom ćete koraku onemogućiti mogućnost Učitaj na radni list i učitati upit u podatkovni model programa Excel da biste sastavili izvješće povezano s rezultatima upita. Osim učitavanja rezultata upita u radni list programa Excel, Power Query omogućuje i učitavanje rezultata upita u podatkovni model programa Excel. Kad učitate podatke u podatkovni model programa Excel, možete koristiti Power Pivot i Power View da biste dodatno analizirali podatke.
Učitavanje upita Ukupna prodaja po proizvodu u podatkovni model programa Excel
-
U oknu Postavke upita poništite mogućnost Prenesi na radni list i potvrdite mogućnost Učitaj u podatkovni model.
-
Da biste upit učitali u podatkovni model programa Excel, kliknite Primijeni i zatvori.
Konačni upit Ukupna prodaja po proizvodu
Kad dovršite sve korake, imat ćete upit Ukupna prodaja po proizvodu u kojem su kombinirani podaci iz datoteke Proizvodi i narudžbe.xlsx i OData sažetka sadržaja tvrtke Northwind. Taj se upit može primijeniti na model dodatka Power Pivot. Promjene upita u dodatku Power Query mijenjaju i osvježuju konačnu tablicu u modelu dodatka Power Pivot.
Napomena: Uređivač upita prikazuje se samo kadu čitate, uredite ili stvorite novi upit pomoću značajke Power Query. U sljedećem se videozapisu prikazuje prozor uređivača upita koji se pojavljuje nakon uređivanja upita iz radne knjige programa Excel. Da biste prikazali uređivača upita bez učitavanja ili uređivanja postojećeg upita za radnu knjigu, na kartici dohvaćanje vanjskih podataka na traci električni upit odaberite iz drugih izvora > prazan upit. U sljedećem se videozapisu prikazuje jedan način prikazivanja uređivača upita.
Napomena: Ova je stranica strojno prevedena te može sadržavati gramatičke pogreške ili netočnosti. Naša je namjera da vam ovaj sadržaj bude koristan. Jesu li vam te informacije bile korisne? Kao referencu možete pogledati i članak na engleskom jeziku.
