Kombinovanje podataka iz više izvora podataka (Power Query)

Važno : Ovaj članak je mašinski preveden, pogledajte odricanje odgovornosti. Pogledajte verziju ovog članka na engleskom jeziku ovde za referencu.

Napomena : Power Query je poznat kao Preuzimanje i transformacija u programu Excel 2016. Informacije koje su pružene ovde odnose se na oba. Da biste saznali više, pročitajte članak Preuzimanje i transformacija u programu Excel 2016.

U ovom uputstvu ćete koristiti Power Query uređivača upita da biste uvezli podatke iz lokalnih Excel datoteke koja sadrži informacije o proizvodu i iz OData feeda koji sadrži informacije o proizvodu redosledu. Izvršavanje transformacija i agregacije korake, i Kombinovanje podataka iz oba izvora da biste dobili izveštaj Ukupna prodaja po proizvodu i godina .

Da biste izvršili ovo uputstvo, potrebno je radna sveska proizvodi i porudžbine . U dijalogu Sačuvaj kao ime datoteke proizvodi i porudžbine.xlsx.

U ovom uputstvu

1. zadatak: Uvoz proizvoda u Excel radnu svesku

1. korak: Povezivanje sa Excel radnom sveskom

2. korak: Podizanje nivoa prvog reda na zaglavlja kolona tabele

3. korak: Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone

Kreirani Power Query koraci

4. korak: Uvoz upita o proizvodima

2. zadatak: Uvoz podataka o porudžbini iz OData feeda

1. korak: Povezivanje sa OData feedom

2. korak: Proširivanje tabele „Detalji_porudžbine“

Proširivanje veze tabele „Detalji_porudžbine“

3. korak: Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone

Uklanjanje izabranih kolona

4. korak: Izračunavanje zbira reda za svaki red „Detalji_porudžbine“

Izračunavanje zbira reda za svaki red „Detalji_porudžbine“

5. korak: Transformacija kolone sa godinama „DatumPorudžbine“

6. korak: Grupisanje redova po ID-u proizvoda i godini

7. korak: Preimenovanje upita

Konačni rezultati upita

Kreirani Power Query koraci

8. korak: Onemogućavanje preuzimanja upita u Excel radnu svesku

Onemogućavanje preuzimanja upita

3. zadatak: Kombinovanje upita „Proizvodi“ i „Ukupna prodaja“

1. korak: Objedinjavanje kolone „IDProizvoda“ u upitu „Ukupna prodaja“

2. korak: Proširivanje kolone objedinjavanja

Proširivanje veze tabele „NovaKolona“

Kreirani Power Query koraci

3. korak: Učitavanje upita „Ukupna prodaja po proizvodu“ u Excel model podataka

Učitavanje upita „Ukupna prodaja po proizvodu“ u Excel model podataka

Upit „Konačna ukupna prodaja po proizvodu“

1. zadatak: Uvoz proizvoda u Excel radnu svesku

U ovom zadatku uvozite proizvode iz datoteke proizvodi i porudžbine.xlsx u Excel radnoj svesci.

1. korak: Povezivanje sa Excel radnom sveskom

  1. Kreirajte Excel radnu svesku.

  2. Na kartici POWER QUERY na traci izaberite stavke Iz datoteke > Iz programa Excel.

  3. U dijalogu za pregledanje Excel pronađite ili otkucajte putanju datoteke „Proizvodi i porudžbine.xlsx“ da biste uvezli datoteku ili se povezali sa njom.

  4. U oknu Navigator kliknite dvaput na radni list Proizvodi ili izaberite stavku Proizvodi i stavku Uredi upit. Kada uredite upit ili se povežete sa novim izvorom podataka, pojavljuje se prozor Uređivač upita.

    Napomena : Za veoma kratak video o tome kako da prikažete uređivač upita pogledajte kraj ovog članka.

2. korak: Podizanje nivoa prvog reda na zaglavlja kolona tabele

U koordinatnoj mreži Pregled upita prvi red tabele ne sadrži imena kolona tabele. Da biste podigli nivo prvog reda na zaglavlja za kolonu tabele:

  1. Kliknite na ikonu tabele ( Ikona tabele ) u gornjem levom uglu pregleda podataka.

  2. Izaberite stavku Koristi prvi red kao zaglavlja.

Podizanje nivoa prvog reda na zaglavlja za kolonu tabele

3. korak: Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone

U ovom koraku treba da uklonite sve kolone osim kolona IDProizvoda, ImeProizvoda, IDKategorije i KoličinaPoJedinici.

  1. U koordinatnoj mreži Pregled upita izaberite kolone IDProizvoda, ImeProizvoda, IDKategorije i KoličinaPoJedinici (koristite Ctrl+klik ili Shift+klik).

  2. Na traci Uređivača upita izaberite stavke Ukloni kolone > Ukloni druge kolone ili kliknite desnim tasterom miša na zaglavlje kolone, a zatim izaberite stavku Ukloni druge kolone.

    Sakrivanje drugih kolona

Kreirani Power Query koraci

Dok u programskom dodatku Power Query obavljate aktivnosti vezane za upite, u oknu Postavke upita, na listi PRIMENJENI KORACI kreiraju se i navode koraci upita. Za svaki korak postoji odgovarajuća Power Query formula, poznata i pod nazivom „M“ jezik. Više informacija o jeziku Power Query formula potražite u članku Saznajte više o Power Query formulama.

Zadatak

Korak upita

Formula

Povezivanje sa Excel radnom sveskom

Izvor

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

Podizanje nivoa prvog reda na zaglavlja za kolonu tabele

FirstRowAsHeader

Table.PromoteHeaders

(Products)

Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

4. korak: Uvoz upita o proizvodima

U ovom koraku uvozite upit Proizvodi u Excel radnu svesku.

  1. Na traci Uređivača upita kliknite na dugme Primeni i zatvori. Rezultati se prikazuju na novom Excel radnom listu.

Vrh stranice

2. zadatak: Uvoz podataka o porudžbini iz OData feeda

U ovom zadatku uvozite podatke u Excel radnu svesku iz uzorka Northwind OData feeda na lokaciji http://services.odata.org/Northwind/Northwind.svc.

1. korak: Povezivanje sa OData feedom

  1. Na kartici POWER QUERY na traci izaberite stavke Iz drugih izvora > Iz OData feeda.

  2. U dijalogu OData feed unesite URL za Northwind OData feed.

  3. Kliknite na dugme U redu.

  4. U oknu Navigator kliknite dvaput na tabelu Porudžbine ili izaberite stavku Porudžbine, a zatim kliknite na dugme Uredi.

Napomena :  Kada zadržite pokazivač miša iznad tabele, prikazuje se padajući pregled tabele.

Zadržavanje pokazivača miša iznad izvora podataka

2. korak: Proširivanje tabele „Detalji_porudžbine“

U ovom koraku treba da proširite tabelu Detalji_porudžbine koja je povezana sa tabelom Porudžbine da biste kombinovali kolone IDProizvoda, CenaPoJedinici i Količina iz tabele Detalji_porudžbine u tabeli Porudžbine. Operacija Proširivanje kombinuje kolone iz srodne tabele u tabeli teme. Kad se upit pokrene, redovi iz srodne tabele (Detalji_porudžbine) kombinuju se u redovima iz tabele teme (Porudžbine).

U programskom dodatku Power Query kolona koja sadrži vezu ka srodnoj tabeli ima vezu Stavka ili Tabela. Veza Stavka vodi do jednog srodnog zapisa i predstavlja relaciju jedan-naspram-jedan sa tabelom teme. Veza Tabela vodi do srodne tabele i predstavlja relaciju jedan-naspram-više sa tabelom teme. Veza predstavlja svojstva navigacije u izvoru podataka unutar relacionog modela. Za OData feed svojstva navigacije predstavljaju entitet sa povezivanjem sporednog ključa. U bazi podataka, kao što je to SQL Server, svojstva navigacije predstavljaju relacije sporednih ključeva u bazi podataka.

Proširivanje veze tabele „Detalji_porudžbine“

Kad proširite tabelu Detalji_porudžbine, tri nove kolone i dodatni redovi dodaju se u tabelu Porudžbine, po jedan za svaki red u ugnežđenoj ili srodnoj tabeli.

  1. U oknu Pregled upita pomerite se do kolone Detalji_porudžbine.

  2. U koloni Detalji_porudžbine kliknite na ikonu za proširivanje ( Proširivanje ).

  3. Na padajućem meniju Proširivanje:

    1. Izaberite stavku (Izaberi sve kolone) da biste obrisali sve kolone.

    2. Izaberite kolone IDProizvoda, CenaPoJedinici i Količina.

    3. Kliknite na dugme U redu.

      Proširivanje veze tabele „Detalji_porudžbine“

      Napomena :  U programskom dodatku Power Query možete da proširite tabele povezane sa kolonom kao i da izvršite operacije agregacije za kolone srodne tabele pre proširivanja podataka u tabeli teme. Više informacija o tome kako da izvršite agregatne operacije potražite u članku Agregacija podataka iz kolone.

3. korak: Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone

U ovom koraku uklanjate sve kolone osim kolona DatumPorudžbine, IDProizvoda, CenaPoJedinici i Količina. U prethodnom zadatku koristili ste opciju Ukloni druge kolone. U ovom zadatku uklanjate izabrane kolone.

Uklanjanje izabranih kolona

  1. U oknu Pregled upita izaberite sve kolone:

    1. Kliknite na prvu kolonu (IDPorudžbine).

    2. Shift+klik na poslednju kolonu (Otpremnik).

    3. Ctrl+klik na kolone DatumPorudžbine, Detalji_porudžbine.IDProizvoda, Detalji_porudžbine.CenaPoJedinici i Detalji_porudžbine.Količina.

  2. Kliknite desnim tasterom miša na izabrano zaglavlje kolone i izaberite stavku Ukloni kolone.

4. korak: Izračunavanje zbira reda za svaki red „Detalji_porudžbine“

U ovom koraku kreirate prilagođenu kolonu radi izračunavanja zbira reda za svaki red Detalji_porudžbine.

Izračunavanje zbira reda za svaki red „Detalji_porudžbine“

  1. U oknu Pregled upita kliknite na ikonu tabele ( Ikona tabele ) u gornjem levom uglu pregleda.

  2. Izaberite stavke Umetni kolonu > Prilagođeno.

  3. U dijalogu Umetanje prilagođene kolone, u okviru za tekst Formula prilagođene kolone unesite [Detalji_porudžbine.CenaPoJedinici] * [Detalji_porudžbine.Količina].

  4. U okvir za tekst Ime nove kolone unesite Zbir reda.

  5. Kliknite na dugme U redu.

Izračunavanje zbira reda za svaki red tabele Detalji_Porudžbine

5. korak: Transformacija kolone sa godinama „DatumPorudžbine“

U ovom koraku treba da transformišete kolonu DatumPorudžbine kako bi se prikazala godina datuma porudžbine.

  1. U koordinatnoj mreži Pregled kliknite desnim tasterom miša na kolonu DatumPorudžbine i izaberite stavke Transformacija > Godina.

  2. Preimenujte kolonu DatumPorudžbine u Godina:

    1. Kliknite dvaput na kolonu DatumPorudžbine i unesite reč Godina ili

    2. Kliknite desnim tasterom miša na kolonu DatumPorudžbine, izaberite stavku Preimenuj i unesite reč Godina.

6. korak: Grupisanje redova po ID-u proizvoda i godini

  1. U koordinatnoj mreži Pregled upita izaberite kolone Godina i Detalji_Porudžbine.IDProizvoda.

  2. Kliknite desnim tasterom miša na neko od zaglavlja i izaberite stavku Grupiši po.

  3. U dijalogu Grupisanje po:

    1. U okviru za tekst Ime nove kolone unesite ime Ukupna prodaja.

    2. Na padajućem meniju Operacija izaberite stavku Zbir.

    3. Na padajućem meniju Kolona izaberite stavku Zbir reda.

  4. Kliknite na dugme U redu.

    Dijalog „Grupisanje po“ za agregatne operacije

7. korak: Preimenovanje upita

Pre nego što uvezete podatke o prodaji u Excel, dajte upitu ime Ukupna prodaja:

  1. U oknu Postavke upita, u okvir za tekst Ime unesite Ukupna prodaja.

Konačni rezultati upita

Kad završite svaki korak, imaćete upit „Ukupna prodaja“ preko Northwind OData feeda.

Ukupna prodaja

Kreirani Power Query koraci

Dok u programskom dodatku Power Query obavljate aktivnosti vezane za upite, u oknu Postavke upita, na listi PRIMENJENI KORACI kreiraju se i navode koraci upita. Za svaki korak postoji odgovarajuća Power Query formula, poznata i pod nazivom „M“ jezik. Više informacija o jeziku Power Query formula potražite u članku Saznajte više o Power Query formulama.

Zadatak

Korak upita

Formula

Povezivanje sa OData feedom

Izvor

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

Proširivanje tabele Detalji_Porudžbine

Proširivanje tabele „Detalji_Porudžbine“

Table.ExpandTableColumn

(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

Uklanjanje drugih kolona kako bi se prikazale samo bitne kolone

RemovedColumns

Table.RemoveColumns

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

Izračunavanje zbira reda za svaki red tabele Detalji_Porudžbine

InsertedColumns

Table.AddColumn

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

Transformacija kolone „DatumPorudžbine“ za prikazivanje godine

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

RenamedColumns1

Table.RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}})

Grupisanje redova po ID-u proizvoda i godini

GroupedRows

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

8. korak: Onemogućavanje preuzimanja upita u Excel radnu svesku

Pošto upit Ukupna prodaja ne predstavlja konačni izveštaj Ukupna prodaja po proizvodu i godini, onemogućite preuzimanje upita u Excel radnu svesku. Kad je opcija Učitaj u radnu svesku postavljena na vrednost Isključeno u oknu Postavke upita, rezultat podataka ovog upita se ne preuzima, ali se upit i dalje može kombinovati sa drugim upitima kako bi se dobio željeni rezultat. U sledećem zadatku ćete saznati kako se ovaj upit kombinuje sa upitom „Proizvodi“.

Onemogućavanje preuzimanja upita

  1. U oknu Postavke upita opozovite izbor u polju za potvrdu Učitaj u radni list.

  2. Na traci Uređivača upita kliknite na dugme Primeni i zatvori. U oknu Upiti radne sveske upit Ukupna prodaja prikazuje vrednost Učitavanje je onemogućeno.

    Onemogućavanje preuzimanja upita

Vrh stranice

3. zadatak: Kombinovanje upita „Proizvodi“ i „Ukupna prodaja“

Power Query omogućava vam da kombinujete više upita, njihovim objedinjavanjem ili dodavanjem. Operacija Objedinjavanje izvršava se na bilo kom Power Query upitu sa tabelarnim oblikom, bez obzira na izvor podataka iz kojeg podaci potiču. Više informacija o kombinovanju izvora podataka potražite u članku Kombinovanje više upita.

U ovom zadatku treba da kombinujete upite Proizvodi i Ukupna prodaja pomoću koraka upita Objedinjavanje i Proširivanje.

1. korak: Objedinjavanje kolone „IDProizvoda“ u upitu „Ukupna prodaja“

  1. U Excel radnoj svesci idite do upita Proizvodi na listu List2.

  2. Na kartici UPIT na traci kliknite na dugme Objedini.

  3. U dijalogu Objedinjavanje izaberite tabelu Proizvodi kao primarnu tabelu i Ukupna prodaja kao drugu ili srodni upit za objedinjavanje. Ukupna prodaja postaje nova proširiva kolona.

  4. Da bi se tabela Ukupna prodaja podudarala sa tabelom Proizvodi po koloni IDProizvoda, izaberite kolonu IDProizvoda iz tabele Proizvodi i kolonu Detalji_porudžbine.IDProizvoda iz tabele Ukupna prodaja.

  5. U dijalogu Nivoi privatnosti:

    1. Izaberite stavku Organizacioni za nivo izolacije privatnosti za oba izvora podataka.

    2. Kliknite na dugme Sačuvaj.

  6. Kliknite na dugme U redu.

    Napomena o bezbednosti : Nivoi privatnosti sprečavaju korisnike da slučajno kombinuju podatke iz više izvora podataka, koji su možda privatni ili za organizaciju. U zavisnosti od upita korisnik može slučajno da pošalje podatke iz privatnog izvora podataka drugom izvoru podataka koji je možda zlonameran. Power Query analizira svaki izvor podataka i postavlja mu definisani nivo privatnosti: javni, organizacijski i privatni. Više informacija o nivoima privatnosti potražite u članku Nivoi privatnosti.

    Dijalog „Objedinjavanje“

Pošto kliknete na dugme U redu, operacija Objedinjavanje kreira upit. Rezultat upita sadrži sve kolone iz primarne tabele (Proizvodi) i jednu kolonu koja sadrži vezu navigacije ka srodnoj tabeli (Ukupna prodaja). Operacija Proširivanje dodaje nove kolone u primarnu ili tabelu teme iz srodne tabele.

Konačno objedinjavanje

2. korak: Proširivanje kolone objedinjavanja

U ovom koraku proširujete kolonu objedinjavanja sa imenom NovaKolona kako biste kreirali dve nove kolone u upitu Proizvodi: Godina i Ukupna prodaja.

Proširivanje veze tabele „NovaKolona“

  1. U koordinatnoj mreži Pregled upita kliknite na ikonu za proširivanje NovaKolona ( Proširivanje ).

  2. Na padajućem meniju Proširivanje:

    1. Izaberite stavku (Izaberi sve kolone) da biste obrisali sve kolone.

    2. Kliknite na stavke Godina i Ukupna prodaja.

    3. Kliknite na dugme U redu.

  3. Preimenujte te dve kolone u Godina i Ukupna prodaja.

  4. Sortirajte po opadajućem redosledu po koloni Ukupna prodaja da biste saznali koji su proizvodi i koje godine imali najveću količinu prodaje.

  5. Preimenujte upit u Ukupna prodaja po proizvodu.

Veza za proširivanje tabele

Kreirani Power Query koraci

Dok izvršavate aktivnosti Objedinjavanje upita u programskom dodatku Power Query, koraci upita se kreiraju i navode u oknu Postavke upita, na listi PRIMENJENI KORACI. Za svaki korak postoji odgovarajuća Power Query formula, poznata i pod nazivom „M“ jezik. Više informacija o jeziku Power Query formula potražite u članku Saznajte više o Power Query formulama.

Zadatak

Korak upita

Formula

Objedinjavanje kolone „IDProizvoda“ u upitu „Ukupna prodaja“

Izvor (izvor podataka za operaciju Objedinjavanje)

Table.NestedJoin

(Products,{"ProductID"},#"Total Sales",{"Order_Details.ProductID"},"NewColumn")

Proširivanje kolone objedinjavanja

ExpandNewColumn

Table.ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

Table.RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Total Sales", Order.Descending}})

3. korak: Učitavanje upita „Ukupna prodaja po proizvodu“ u Excel model podataka

U ovom koraku treba da onemogućite opciju Učitaj u radni list i učitate upit u Excel model podataka kako biste izgradili izveštaj povezan sa rezultatom upita. Pored učitavanja rezultata upita u Excel radni list, Power Query vam omogućava da učitate rezultat upita u Excel model podataka. Kad učitate podatke u Excel model podataka, možete da koristite Power Pivot i Power View za dalju analizu podataka.

Učitavanje upita „Ukupna prodaja po proizvodu“ u Excel model podataka

  1. U oknu Postavke upita opozovite izbor u polju za potvrdu Učitaj u radni list i potvrdite izbor u polju za potvrdu Učitaj u model podataka.

  2. Da biste učitali upit u Excel model podataka, kliknite na dugme Primeni i zatvori.

Učitavanje Excel modela podataka

Upit „Konačna ukupna prodaja po proizvodu“

Kad izvršite svaki korak, imaćete upit Ukupna prodaja po proizvodu koji kombinuje podatke iz datoteke „Proizvodi i porudžbine.xlsx“ i Northwind OData feeda. Ovaj upit može da se primeni na Power Pivot model. Pored toga, promene upita u programskom dodatku Power Query menjaju i osvežavaju dobijenu tabelu u Power Pivot modelu.

Vrh stranice

Napomena : Uređivač upita se pojavljuje samo kada učitavate, uređujete ili kreirate novi upit pomoću programskog dodatka Power Query. Sledeći video prikazuje prozor uređivača upita koji se pojavljuje nakon uređivanja upita iz Excel radne sveske. Da biste prikazali uređivač upita bez učitavanja ili uređivanja postojećeg upita radne sveske, iz odeljka Preuzimanje spoljnih podataka na kartici Power Query na traci izaberite stavke Iz drugih izvora > Prazan upit. Sledeći video prikazuje jedan način za prikazivanje uređivača upita.

Kako prikazati uređivač upita u programu Excel

Napomena : Odricanje odgovornosti za mašinski prevod: Ovaj članak je preveo računarski sistem bez ljudske intervencije. Microsoft nudi ove mašinske prevode da bi pomogao korisnicima koji ne govore engleski da uživaju u sadržaju o Microsoft proizvodima, uslugama i tehnologijama. Budući da je članak mašinski preveden, može da sadrži greške u rečniku, sintaksi ili gramatici.

Unapredite veštine
Istražite obuku
Prvi nabavite nove funkcije
Pridružite se Office Insider korisnicima

Da li su vam ove informacije koristile?

Hvala vam na povratnim informacijama!

Hvala za povratne informacije! Zvuči da će biti od pomoći ako vas povežemo sa našim agentima Office podrške.

×