Kombiniranje podataka iz više izvora (Power Query)

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:

Prvi zadatak: uvoz proizvoda u radnu knjigu programa Excel

Prvi korak: povezivanje s radnom knjigom programa Excel

Drugi korak: pretvaranje prvog retka u zaglavlja stupaca tablice

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

Stvoreni koraci za Power Query

Četvrti korak: uvoz upita o proizvodima

Drugi zadatak: uvoz podataka o narudžbi iz OData sažetka sadržaja

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

Drugi korak: proširivanje tablice Detalji_narudžbe

Veza na proširivanje tablice Detalji_narudžbe

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

Uklanjanje odabranih stupaca

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

Računanje ukupnog zbroja u retku za svaki redak tablice Detalji_narudžbe

Peti korak: pretvaranje stupca DatumNarudžbe s godinom

Šesti korak: grupiranje redaka prema kriterijima IDProizvoda i Godina

Sedmi korak: preimenovanje upita

Krajnji rezultati upita

Stvoreni koraci za Power Query

Osmi korak: onemogućivanje preuzimanja upita u radnu knjigu programa Excel

Onemogućivanje preuzimanja upita

Treći zadatak: kombiniranje upita Proizvodi i Ukupna prodaja

Prvi korak: spajanje stupca IDProizvoda i upita Ukupna prodaja

Drugi korak: proširivanje stupca za spajanje

Veza na proširivanje tablice NoviStupac

Stvoreni koraci za Power Query

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

Učitavanje upita Ukupna prodaja po proizvodu u podatkovni model programa Excel

Konačni upit Ukupna prodaja po proizvodu

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

  1. Stvorite radnu knjigu programa Excel.

  2. Na kartici vrpce POWER QUERY kliknite Iz datoteke > Iz programa Excel.

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

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

  1. Kliknite ikonu tablice ( Ikona tablice ) u gornjem lijevom kutu pretpregleda podataka.

  2. Kliknite Koristi prvi redak kao zaglavlja.

Pretvaranje prvog retka u zaglavlja stupaca tablice

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 rešeci Pretpregled upita odaberite stupce IDproizvoda, NazivProizvoda, IDkategorije i JediničnaKoličina (pomoću tipke Ctrl ili Shift i klika mišem).

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

    Sakrivanje drugih stupaca

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

Tablice. Promovtezaglavlja

(Proizvodi)

Uklanjanje drugih stupaca tako da ostanu prikazani samo oni koji vas zanimaju

RemovedOtherColumns

tablice. SelectColumns

(FirstRowAsHeader,{"IDproizvoda", "NazivProizvoda", "IDkategorije", "JediničnaKoličina"})

Četvrti korak: uvoz upita o proizvodima

U ovom koraku u radnu knjigu programa Excel uvozite upit Proizvodi.

  1. Na vrpci uređivača upita kliknite Primijeni i zatvori. Rezultati se prikazuju na novom radnom listu programa Excel.

Vrh stranice

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

  1. Na kartici vrpce POWER QUERY kliknite 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. Kliknite U redu.

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

Zadržite pokazivač miša na izvoru podataka

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.

  1. U oknu Pretpregled upita pomaknite se na stupac Detalji_narudžbe.

  2. U stupcu Order_Details kliknite ikonu za proširivanje ( Proširivanje ).

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

    1. kliknite (Odaberi sve stupce) da biste uklonili sadržaj svih stupaca

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

    3. Kliknite U redu.

      Veza za proširivanje tablice Order_Details

      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

  1. U oknu Pretpregled upita odaberite sve stupce:

    1. Kliknite prvi stupac (IDnarudžbe).

    2. Pritisnite Shift pa kliknite posljednji stupac (Shipper).

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

  1. U oknu Pretpregled upita kliknite ikonu tablice ( Ikona tablice ) u gornjem desnom kutu pretpregleda.

  2. Kliknite Umetni stupac > Prilagođeni.

  3. 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].

  4. U tekstni okvir Novi naziv stupca unesite Ukupni zbroj u retku.

  5. Kliknite U redu.

Računanje ukupnog zbroja u retku za svaki redak tablice Detalji_narudžbe

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 rešetci pretpregleda desnom tipkom miša kliknite stupac DatumNarudžbe pa kliknite Transformiraj > Godina.

  2. Preimenujte stupac DatumNarudžbe u Godina:

    1. dvokliknite stupac DatumNarudžbe pa unesite Godina ili

    2. desnom tipkom miša kliknite stupac DatumNarudžbe pa kliknite Preimenuj i unesite Godina.

Šesti korak: grupiranje redaka prema kriterijima IDProizvoda i Godina

  1. U rešetci Pretpregled upita odaberite Godina i Detalji_narudžbe.IDProizvoda.

  2. Desnom tipkom miša kliknite jedno od zaglavlja pa kliknite Grupiraj prema.

  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. Kliknite U redu.

    Dijaloški okvir Grupiraj prema za agregacijske operacije

Sedmi korak: preimenovanje upita

Prije no što u Excel uvezete podatke o prodaji, upitu dodijelite naziv UkupnaProdaja:

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

Ukupna prodaja

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

tablicu. Expandustupac

(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

Tablica. Removistupci

(#"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

tablice. AddColumn

(RemovedColumns, "Prilagođeno", each [Detalji_narudžbe.JediničnaCijena] * [Detalji_narudžbe.Količina])

Pretvaranje stupca OrderDate tako da prikazuje godinu

RenamedColumns

Tablica. RenameColumns

(InsertedCustom,{{"Prilagođeno", "Ukupni zbroj u retku"}})

TransformedColumn

tablici. transformacije

(RenamedColumns,{{"DatumNarudžbe", Datum.Godina}})

RenamedColumns1

Tablica. RenameColumns

(TransformedColumn,{{"DatumNarudžbe", "Godina"}})

Grupiranje redaka prema ProductID i Year

GroupedRows

Tablica. Group
(RenamedColumns1, {"godina", "Order_Details. IDProizvoda"}, {{"Ukupna prodaja", svaki popis. SUM ([ukupni zbroj]), upišite broj}})

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

  1. U oknu Postavke upita poništite okvir Učitaj na radni list.

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

    Onemogućivanje preuzimanja upita

Vrh stranice

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

  1. U radnoj knjizi programa Excel prijeđite na upit Proizvodi na listu 2.

  2. Na kartici UPIT na vrpci kliknite Spoji.

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

  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. Kliknite Spremi.

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

    Dijaloški okvir Spajanje

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.

Konačni rezultat spajanja

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

  1. U rešetki pretpregleda upita kliknite ikonu proširenja newcolumn ( Proširivanje ).

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

    1. kliknite (Odaberi sve stupce) da biste uklonili sadržaj svih stupaca

    2. Kliknite Godina i Ukupna prodaja.

    3. Kliknite U redu.

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

  4. Sortirajte silazno prema stupcu Ukupna prodaja da biste saznali koji su proizvodi i kojih godina prodani u najvećoj količini.

  5. Promijenite naziv upita u Ukupna prodaja po proizvodu.

Veza za proširivanje tablice

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)

Tablica. nested join

(Proizvodi,{"IDproizvoda"},#"Ukupna prodaja",{"Detalji_narudžbe.IDproizvoda"},"NoviStupac")

Proširivanje stupca za spajanje

ExpandNewColumn

tablicu. Expandustupac

(Izvor, "NoviStupac", {"Godina", "Ukupna prodaja"}, {"NoviStupac.Godina", "NoviStupac.Ukupna prodaja"})

RenamedColumns

Tablica. RenameColumns

(#"Expand NoviStupac",{{"NoviStupac.Godina", "Godina"}, {"NoviStupac.Ukupna prodaja", "Ukupna prodaja"}})

SortedRows

Tablica. sortiranje

(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

  1. U oknu Postavke upita poništite mogućnost Prenesi na radni list i potvrdite mogućnost Učitaj u podatkovni model.

  2. Da biste upit učitali u podatkovni model programa Excel, kliknite Primijeni i zatvori.

Učitavanje podatkovnog modela programa Excel

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.

Kako vidjeti uređivač upita u programu Excel

Vrh stranice

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

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×