Kombiniranje podataka iz više izvora (Power Query)

Važno :  Ovaj je članak strojno preveden. Pogledajteizjavu o odricanju od odgovornosti. Verziju ovog članka na engleskom potražite ovdje.

Napomena : Power Query poznat je pod nazivom Dohvaćanje i pretvaranje u programu Excel 2016. Ovdje navedene informacije odnose se na oba naziva. Da biste saznali više, pročitajte članak Funkcija dohvaćanja i pretvaranja u aplikaciji Excel 2016.

U ovom ćete praktičnom vodiču, koristit ćete uređivača upita dodatka Power Query da biste uvezli podatke iz lokalne datoteke programa Excel koja sadrži podatke o proizvodima i iz OData sažetak sadržaja koji sadrži informacije o narudžbi proizvoda. Izvršite transformaciju i zbrajanja i kombiniranje podataka iz oba izvora, čime se dobiva izvješća Ukupna prodaja po proizvodu i godina .

Za ovu vježbu potrebna vam je radna knjiga 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 ćete zadatku uvesti 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

jednadžba

Povezivanje s radnom knjigom programa Excel

Source

Source{[Name="Proizvodi"]}[Data]

Pretvaranje prvog retka u zaglavlja stupaca tablice

FirstRowAsHeader

Table.PromoteHeaders

(Proizvodi)

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

RemovedOtherColumns

Table.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 s vezom na povezanu tablicu sadrži vezu na unos ili tablicu. Veza na unos vodi do jednog povezanog zapisa i predstavlja odnos jedan prema jedan s predmetnom tablicom. Veza na tablicu vodi do povezane tablice i predstavlja odnos jedan prema više s predmetnom tablicom. Veza predstavlja navigacijska svojstva izvora podataka unutar relacijskog modela. Za OData sažetak sadržaja navigacijska svojstva predstavljaju entitet s povezivanjem pomoću vanjskog ključa. U bazi podataka kao što je SQL Server navigacijska svojstva predstavljaju odnose pomoću vanjskih ključeva 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 Detalji_narudžbe kliknite ikonu za proširivanje ( Proširi ).

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

jednadžba

Povezivanje s OData sažetkom sadržaja

Source

Source{[Name="Narudžbe"]}[Data]

Proširivanje tablice Order_Details

Proširivanje tablice Order_Details

Table.ExpandTableColumn

(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

Table.RemoveColumns

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

Table.AddColumn

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

Pretvaranje stupca OrderDate tako da prikazuje godinu

RenamedColumns

Table.RenameColumns

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

TransformedColumn

Table.TransformColumns

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

RenamedColumns1

Table.RenameColumns

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

Grupiranje redaka prema ProductID i Year

GroupedRows

Table.Group
(RenamedColumns1, {"Godina", "Detalji_narudžbe.IDProizvoda"}, {{"Ukupna prodaja", each List.Sum([Ukupni zbroj u retku]), type number}})

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 naziva NoviStupac radi stvaranja dvaju novih stupaca u upitu Proizvodi: Godina i Ukupna prodaja.

Veza na proširivanje tablice NoviStupac

  1. U rešetci Pretpregled upita kliknite ikonu za proširivanje tablice NoviStupac ( Proširi ).

  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

jednadžba

Spajanje stupca IDproizvoda s upitom Ukupna prodaja

Source (izvor podataka za operaciju Spoji)

Table.NestedJoin

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

Proširivanje stupca za spajanje

ExpandNewColumn

Table.ExpandTableColumn

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

RenamedColumns

Table.RenameColumns

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

SortedRows

Table.Sort

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

Vrh stranice

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 vidjeli uređivač upita bez učitavanja ili uređivanja postojećeg upita radne knjige, iz odjeljka Dohvaćanje vanjskih podataka na kartici vrpce Power Query 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

Napomena : Izjava o odricanju od odgovornosti za strojni prijevod: ovaj je članak preveo računalni sustav bez ljudske intervencije. Microsoft nudi strojne prijevode da bi korisnicima koji ne razumiju engleski omogućio čitanje sadržaja o Microsoftovim proizvodima, uslugama i tehnologijama. Budući da je preveden strojno, članak možda sadrži pogreške u vokabularu, sintaksi ili gramatici.

Proširite svoje vještine
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.

×