U ovom vodiču pomoću uređivača upita dodatka Power Query možete uvesti podatke iz lokalne datoteke Excel koja sadrži informacije o proizvodu i iz OData sažetka sadržaja koji sadrži informacije o narudžbi proizvoda. Izvodite korake pretvorbe i agregacije te kombinirate podatke iz oba izvora radi proizvodnje izvješća "Ukupna prodaja po proizvodu i godini".   

Da biste mogli izvesti ovaj vodič, potrebna vam je radna knjiga Proizvodi. U dijaloškom okviru Spremi kao datoteci dajte naziv Proizvodi i narudžbe.xlsx.

U ovom zadatku uvozite proizvode iz datoteke Proizvodi i Orders.xlsx (preuzeta i preimenovana iznad) u radnu knjigu programa Excel, promiče retke u zaglavlja stupaca, uklanja neke stupce i učitava upit na radni list.

Prvi korak: povezivanje s radnom knjigom programa Excel

  1. Stvorite radnu knjigu programa Excel.

  2. Odaberite Data > Get Data > From File > From Workbook (> Iz radne knjige).

  3. U dijaloškom okviru Uvoz podataka potražite i pronađite datoteku Products.xlsx koju ste preuzeli, a zatim odaberite Otvori.

  4. U oknu Navigator dvokliknite tablicu Proizvodi. Prikazat će se uređivač dodatkaPower Query.

2. korak: pregledajte korake upita

Power Query po zadanom automatski dodaje nekoliko koraka kao praktičnost. Pregledajte svaki korak u odjeljku Primijenjeni koraci u oknu Postavke upita da biste saznali više.

  1. Desnom tipkom miša kliknite korak Izvor, a zatim odaberite Uređivanje Postavke. Taj je korak stvoren prilikom uvoza radne knjige.

  2. Desnom tipkom miša kliknite korak navigacije pa odaberite Uređivanje Postavke. Taj je korak stvoren kada ste odabrali tablicu iz dijaloškog okvira Navigacija.

  3. Desnom tipkom miša kliknite korak Promijenjena vrsta, a zatim odaberite Uređivanje Postavke. Taj je korak stvorio Power Query koji je odgodila vrste podataka svakog stupca. Odaberite strelicu dolje desno od trake formule da biste vidjeli potpunu 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.

  1. U pretpregledu podatakaodaberite stupce PRODUCTID, ProductName, CategoryIDi QuantityPerUnit (koristite Ctrl + klik ili Shift + Klik).

  2. Odaberite Ukloni stupce > ukloni ostale stupce.

    Sakrivanje drugih stupaca

Četvrti korak: učitavanje upita s proizvodima

U ovom koraku upit Proizvodi možete učitati na radni Excel radni list.

  • Odaberite Polazno > Zatvori & Učitaj. Upit će se prikazati na novom Excel radnom listu.

Sažetak: Koraci dodatka Power Query stvoreni u 1. zadatku

Tijekom izvođenja aktivnosti upita u dodatku Power Query koraci upita stvaraju se i navode u oknu Upit Postavke 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 Excel radne knjige

Izvor

= Excel. Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)

Odaberite tablicu Proizvodi

Navigacija

= Source{[Item="Products",Kind="Table"]}[Data]

Power Query automatski otkriva vrste podataka stupca

Promijenjena vrsta

= Table.TransformColumnTypes(Products_Table,{{"PRODUCTID", Int64.Type}, {"ProductName", type text}, {"SUPPLIERID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})

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

Uklonjeni ostali stupci

= Table.SelectColumns(FirstRowAsHeader,{"PRODUCTID", "ProductName", "CATEGORYID", "QuantityPerUnit"})

U ovom zadatku uvozite podatke u radnu knjigu programa Excel iz oglednog OData sažetka sadržaja northwinda na servisu http://services.odata.org/Northwind/Northwind.svc,proširite tablicu Order_Details, uklonite stupce, izračunajte ukupni zbroj redaka, transformirajte Datum Narudžbe, grupirajte retke prema IDprojeru i godini, preimenujte upit i onemogućite preuzimanje upita u radnu knjigu Excel.

Prvi korak: Povezivanje OData sažetak sadržaja

  1. Odaberite Data > Get Data > from Other Sources > from OData Feed.

  2. U dijaloški okvir OData sažetak sadržaja unesite URL OData sažetka sadržaja tvrtke Northwind.

  3. Odaberite U redu.

  4. U oknu Navigator dvokliknite tablicu Narudžbe.

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. Kada se upit pokrene, reci iz povezane tablice (Order_Details) kombiniraju se u retke s primarnom tablicom (Narudžbe).

U dodatku Power Query stupac koji sadrži povezanu tablicu sadrži vrijednost Zapisili Tablica u ćeliji. Ti se stupci nazivaju 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živanjem vanjskim ključem u OData sažetku sadržaja ili vanjskim ključnim odnosima u SQL Server bazi podataka.

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 pretpregledu podatakapomaknite se vodoravno do Order_Details stupca.

  2. U stupcu Order_Details odaberite ikonu proširenja (Proširi).

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

    1. Odaberite (Odaberi sve stupce) da biste očistili sve stupce.

    2. Odaberite ID proizvoda, JediničnaCijenai Količina.

    3. Odaberite U redu.

      Veza za proširivanje tablice Order_Details

      Napomena: U dodatku Power Query možete proširiti tablice povezane iz stupca i objediniti stupce povezane tablice prije proširivanja podataka u tablici predmeta. 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

  1. U pretpregledu podatakaodaberite sljedeće stupce:

    1. Odaberite prvi stupac, ID Narudžbe.

    2. Shift + Kliknite zadnji stupac, Otpremnica.

    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 odabrano zaglavlje stupca, a zatim odaberite Ukloni ostale 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.

  1. U pretpregledupodataka odaberite ikonu tablice (Ikona tablice) u gornjem lijevom kutu pretpregleda.

  2. Kliknite Dodaj prilagođeni stupac.

  3. U dijaloškom okviru Prilagođeni stupac u okvir Prilagođena formula stupca unesite [Order_Details.JediničnaCijena] * [Order_Details.Količina].

  4. U okvir Novi naziv stupca unesite Ukupni zbroj retka.

  5. Odaberite 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 pretpregledu podatakadesnom tipkom miša kliknite stupac Datum Narudžbe, a zatim odaberite Pretvori > Godina.

  2. Preimenujte stupac DatumNarudžbe u Godina:

    1. dvokliknite stupac DatumNarudžbe pa unesite Godina ili

    2. Right-Click stupcu Datum Narudžbe odaberite Preimenujpa unesite Godina.

Šesti korak: grupiranje redaka prema kriterijima IDProizvoda i Godina

  1. U pretpregledu podatakaodaberite Godinai Order_Details.PRODUCTID.

  2. Right-Click zaglavlja pa odaberite Grupiraj po.

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

    Dijaloški okvir Grupiraj prema za agregacijske operacije

Sedmi korak: preimenovanje upita

Prije uvoza podataka o prodaji u Excel, preimenujte upit:

  • U oknu Postavke u okvir Naziv unesite Ukupna prodaja.

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.

Ukupna prodaja

Sažetak: koraci dodatka Power Query stvoreni u 2. zadatku 

Tijekom izvođenja aktivnosti upita u dodatku Power Query koraci upita stvaraju se i navode u oknu Upit Postavke 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, [Implementacija="2.0"])

Odabir tablice

Navigacija

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

Proširivanje tablice Order_Details

Proširivanje tablice Order_Details

= Table.ExpandTableColumn(Narudžbe, "Order_Details", {"PRODUCTID", "JediničnaCijena", "Količina"}, {"Order_Details.PRODUCTID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

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

RemovedColumns

= Table.RemoveColumns(#"Expand Order_Details",{"ORDERID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})

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

Dodano prilagođeno

= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

= Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Promjena u smisleniji naziv, Lne Total

Preimenovani stupci

= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})

Pretvaranje stupca OrderDate tako da prikazuje godinu

Izdvojena godina

= Table.TransformColumns(#"Grupirani reci";{{"Godina", Datum.godina, Int64.Type}})

Promijeni u 

smisleniji nazivi, Datum Narudžbe i Godina

Preimenovani stupci 1

Table.RenameColumns

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

Grupiranje redaka prema ProductID i Year

GroupedRows

= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

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 zadatku upite Proizvodi i Ukupna prodaja kombinirate pomoću upita Spoji i Proširi, a zatim upit Ukupna prodaja po proizvodu umetnite u podatkovni model Excel proizvoda.

Prvi korak: spajanje stupca IDProizvoda i upita Ukupna prodaja

  1. U radnoj Excel otvorite upit Proizvodi na kartici Radni list Proizvodi.

  2. Odaberite ćeliju u upitu, a zatim Odaberite Upit > Spoji.

  3. U dijaloškom okviru Spajanje odaberite Proizvodi kao primarna tablica, a zatim kao sekundarni ili povezani upit za spajanje odaberite Ukupna prodaja. Ukupna prodaja postat će novi strukturirani stupac s ikonom proširenja.

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

  6. Odaberite 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 informacije o razinama privatnosti potražite u članku Postavljanje razina zaštite privatnosti.

    Dijaloški okvir Spajanje

Rezultat

Operacija spajanja stvara upit. Rezultat upita sadrži sve stupce iz primarne tablice (Proizvodi) i jedan strukturirani stupac tablice u povezanu tablicu (Ukupna prodaja). Odaberite ikonu Proširi da biste dodali nove stupce u primarnu tablicu iz sekundarne ili povezane tablice.

Konačni rezultat spajanja

Drugi korak: proširivanje spojenog stupca

U ovom koraku spojeni stupac proširite nazivom NewColumn da biste stvorili dva nova stupca u upitu Proizvodi: Godina i Ukupna prodaja.

  1. U pretpregledu podatakaodaberite Ikona proširi (Proširi) pokraj mogućnosti NewColumn.

  2. Na padajućem popisu Proširi:

    1. Odaberite (Odaberi sve stupce) da biste očistili sve stupce.

    2. Odaberite Godina i Ukupna prodaja.

    3. Odaberite U redu.

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

  4. Da biste saznali koji su proizvodi i u kojim su godinama proizvodi dobili najveći obujam prodaje, odaberite Sortiraj silazno po ukupnojprodaji.

  5. Promijenite naziv upita u Ukupna prodaja po proizvodu.

Rezultat

Veza za proširivanje tablice

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

U ovom koraku upit učitavate u podatkovni model Excelda biste izgradili izvješće povezano s rezultatom upita. Nakon učitavanja podataka u podatkovni Excel ,pomoću dodatka Power Pivot možete dodatno poboljšati analizu podataka.

  1. Odaberite Polazno > Zatvori & Učitaj.

  2. U dijaloškom okviru Uvoz podataka odaberite Dodaj te podatke u podatkovni model. Da biste saznali više o korištenju tog dijaloškog okvira, odaberite upitnik (?).

Rezultat

Imate upit Ukupna prodaja po proizvodu koji objedinjuje podatke iz datoteke Products.xlsx Northwind OData sažetka sadržaja. Taj se upit primjenjuje na model dodatka Power Pivot. Osim toga, promjene upita mijenjaju i osvježavaju dobivenu tablicu u podatkovnom modelu.

Sažetak: koraci dodatka Power Query stvoreni u 3. zadatku

Tijekom izvođenja aktivnosti spajanja upita u dodatku Power Query koraci upita stvaraju se i navode u oknu Upit Postavke 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(proizvodi, {"PRODUCTID"}, #"Ukupna prodaja", {"Order_Details.ProductID"}, "Total Sales", 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"})

Preimenovanje dvaju stupaca

Preimenovani stupci

= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})

Sortiranje ukupnog iznosa Prodaja uzlaznim redoslijedom

Sortirani reci

= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})

Vidi također

Power Query za Excel pomoć

Potrebna vam je dodatna pomoć?

Proširite svoje vještine
Istražite osposobljavanje
Prvi koristite nove značajke
Pridružite se Microsoft Insidere

Jesu li ove informacije bile korisne?

Koliko ste zadovoljni kvalitetom prijevoda?
Što je utjecalo na vaše iskustvo?

Hvala vam na povratnim informacijama!

×