Združevanje podatkov iz več virov podatkov (Power Query)

Pomembno : Besedilo članka je prevedeno strojno. Glejte zavrnitev odgovornosti. Angleško različico tega članka najdete tukaj .

Opomba : Power Query se v programu Excel 2016 v imenuje Pridobivanje in pretvorba. Informacije v tem dokumentu veljajo za oba dodatka. Če želite več informacij, si oglejte Pridobivanje in pretvorba v programu Excel 2016.

V tej vadnici, ki jih boste uporabljali urejevalnik poizvedbe Power Query za uvoz podatkov iz lokalnega Excelove datoteke, ki vsebuje informacije o izdelku, in iz vira podatkov OData ki vsebuje informacije o izdelku vrstnem redu. Izvedite korake za pretvorbo in združevanja in združevanje podatkov iz obeh virov za izdelavo Total Sales per Product in leto poročila.

Če želite uporabiti to vadnico, potrebujete delovni zvezek Izdelki in naročila. V pogovornem oknu Shrani kot poimenujte datoteko kot Izdelki in naročila.xlsx.

V tej vadnici

1. opravilo: uvoz izdelkov v Excelov delovni zvezek

1. korak: povezovanje z Excelovim delovnim zvezkom

2. korak: povišanje prve vrstice tabele v glave stolpcev tabele

3. korak: odstranjevanje ostalih stolpcev za prikaz pomembnih stolpcev

Ustvarjeni koraki dodatka Power Query

4. korak: uvoz poizvedbe izdelkov

2. opravilo: uvoz podatkov naročila iz vira OData

1. korak: povezovanje z virom OData

2. korak: razširitev tabele »Podrobnosti_naročila«

Razširitev povezave tabele »Podrobnosti_naročila«

3. korak: odstranjevanje ostalih stolpcev za prikaz pomembnih stolpcev

Odstranjevanje izbranih stolpcev

4. korak: izračun vsote vrstice za vsako vrstico tabele »Podrobnosti_naročila«

Izračun vsote vrstice za vsako vrstico »Podrobnosti_naročila«

5. korak: pretvorba stolpca z letom »DatumNaročila«

6. korak: vrstice skupine po vrednostih »IDIzdelka« in »Lto«

7. korak: preimenovanje poizvedbe

Rezultati končne poizvedbe

Ustvarjeni koraki dodatka Power Query

8. korak: onemogočanje prenosa poizvedbe v Excelov delovni zvezek

Onemogočanje prenosa poizvedbe

3. opravilo: združevanje poizvedb »Izdelki« in »Skupna prodaja«

1. korak: spajanje poizvedbe »IDIzdelka« s poizvedbo »Skupna prodaja«

2. korak: razširitev združenega stolpca

Razširitev povezave tabele »NovStolpec«

Ustvarjeni koraki dodatka Power Query

3. korak: nalaganje poizvedbe »Skupna prodaja po izdelku« v Excelov podatkovni model

Nalaganje poizvedbe »Skupna prodaja po izdelku« v Excelov podatkovni model

Končna poizvedba »Skupna prodaja po izdelku«

1. opravilo: uvoz izdelkov v Excelov delovni zvezek

V tem opravilu boste uvozili izdelke iz datoteke Izdelki in naročila.xlsx v Excelov delovni zvezek.

1. korak: povezovanje z Excelovim delovnim zvezkom

  1. Ustvarite Excelov delovni zvezek.

  2. Na zavihku traku POWER QUERY kliknite Iz datoteke > Iz Excela.

  3. V Excelovem pogovornem oknu za brskanje poiščite ali vnesite pot dokumenta Products and Orders.xlsx za uvoz v datoteko ali povezovanje z njo.

  4. V podoknu Krmar dvokliknite delovni list Izdelki ali kliknite Izdelki in nato Uredi poizvedbo. Ko uredite poizvedbo ali ustvarite povezavo do novega vira podatkov, se odpre okno Urejevalnik poizvedb.

    Opomba : Če si želite ogledati kratek videoposnetek o tem, kako prikazati Urejevalnik poizvedbe, se pomaknite na konec tega članka.

2. korak: povišanje prve vrstice tabele v glave stolpcev tabele

V mreži Predogled poizvedbe v prvi vrstici tabele ni imen stolpcev tabele. Če želite prvo vrstico povišati v glavo stolpca tabele:

  1. Kliknite ikono tabele ( Ikona tabele ) v zgornjem levem stolpcu predogleda podatkov.

  2. Kliknite Prvo vrstico uporabi kot glave.

Povišanje prve vrstice tabele v glavo stolpca tabele

3. korak: odstranjevanje ostalih stolpcev za prikaz pomembnih stolpcev

V tem koraku odstranite vse stolpce, razen stolpcev ProductID, ProductName, CategoryID in QuantityPerUnit.

  1. V mreži Predogled poizvedbe izberite stolpce ProductID, ProductName, CategoryID in QuantityPerUnit (uporabite Ctrl+klik ali Shift+klik).

  2. Na traku Urejevalnik poizvedbe kliknite Odstrani stolpce > Odstrani druge stolpce ali z desno tipko miške kliknite glavo stolpca in nato kliknite Odstrani druge stolpce.

    Skrivanje drugih stolpcev

Ustvarjeni koraki dodatka Power Query

Ko v dodatku Power Query izvajate opravila s poizvedbo, so v podoknu Nastavitve poizvedbe ustvarjeni koraki poizvedbe in navedeni na seznamu UPORABLJENI KORAKI. Vsak korak poizvedbe ima pripadajočo formulo dodatka Power Query, imenovano tudi jezik »M«. Če želite več informacij o jeziku formul za dodatek Power Query, preberite temo Več informacij o formulah dodatka Power Query.

Opravilo

Korak poizvedbe

formula

Povezovanje z Excelovim delovnim zvezkom

Vir

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

Povišanje prve vrstice tabele v glavo stolpca tabele

FirstRowAsHeader

Table.PromoteHeaders

(Products)

Odstranjevanje drugih stolpcev in prikaz le želenih stolpcev

RemovedOtherColumns

Table.SelectColumns

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

4. korak: uvoz poizvedbe izdelkov

V tem koraku uvozite poizvedbo Izdelki v Excelov delovni zvezek.

  1. Na traku Urejevalnik poizvedbe kliknite Uporabi in zapri. Rezultati so prikazani v novem Excelovem delovnem listu.

Na vrh strani

2. opravilo: uvoz podatkov naročila iz vira OData

V tem opravilu boste uvozili podatke v Excelov delovni zvezek iz vzorčnega vira Northwind OData na spletnem mestu http://services.odata.org/Northwind/Northwind.svc.

1. korak: povezovanje z virom OData

  1. Na zavihku traku POWER QUERY kliknite Iz drugih virov > Iz vira OData.

  2. V pogovorno okno Vir OData vnesite spletni naslov za vir Northwind OData.

  3. Kliknite V redu.

  4. V podoknu Krmar dvokliknite tabelo Naročila ali kliknite Naročila in nato še Uredi.

Opomba :  Ko kazalec miške premaknete na tabelo, se bo prikazal predogled tabele.

Kazalec miške na viru podatkov

2. korak: razširitev tabele »Podrobnosti_naročil«

V tem koraku razširite tabelo Order_Details, ki je povezana s tabelo Orders, da združite stolpce ProductID, UnitPrice in Quantity iz tabele Order_Details v tabelo Orders. S postopkom Razširi združite stolpce iz sorodne tabele v tabelo zadeve. Ko poizvedbo zaženete, so vrstice iz povezane tabele (Order_Details) združene v vrstice iz tabele zadeve (Orders).

V dodatku Power Query je v stolpcu s povezavo do povezane tabele povezava Vnos ali povezava Tabela. Povezava Vnos vodi do enega povezanega zapisa in s tabelo zadeve predstavlja odnos »ena proti ena«. Povezava Tabela vodi do povezane tabele in s tabelo zadeve predstavlja odnos »ena proti mnogo«. Povezava predstavlja lastnosti krmarjenja v podatkovnem viru znotraj relacijskega modela. Če želite vir OData, lahko lastnosti krmarjenja predstavljajo entiteto s povezavo tujega ključa. V zbirki podatkov, kot je strežnik SQL Server, predstavljajo lastnosti krmarjenja povezave tujega ključa v zbirki podatkov.

Razširjanje povezave tabele »Order_Details«

Ko razširite tabelo Order_Details, so v tabelo Orders dodani trije novi stolpci in dodatne vrstice, ena za vsako vrstico v ugnezdeni ali povezani tabeli.

  1. V podoknu Predogled poizvedbe se premaknite do stolpca Order_Details.

  2. V stolpcu Podrobnosti_naročila kliknite ikono za razširitev ( Razširi ).

  3. V spustnem meniju Razširi:

    1. Kliknite (Izberi vse stolpce), da počistite vse stolpce.

    2. Kliknite IDIzdelka, CenaEnote in Količina.

    3. Kliknite V redu.

      Razširitev povezave tabele »Order_Details«

      Opomba :  V dodatku Power Query lahko razširite tabele, povezane s stolpcem, lahko pa izvedete tudi postopke združevanja v stolpcih povezane tabele, preden razširite podatke v zadevni tabeli. Če želite več informacij o tem, kako izvedete postopke združevanja, glejte Združevanje podatkov iz stolpca.

3. korak: odstranjevanje ostalih stolpcev za prikaz pomembnih stolpcev

V tem koraku odstranite vse stolpce, razen stolpcev OrderDate, ProductID, UnitPrice in Quantity. V prejšnjem opravilu ste uporabili ukaz Odstrani druge stolpce. Pri tem opravilu odstranite izbrane stolpce.

Odstranjevanje izbranih stolpcev

  1. V podoknu Predogled poizvedbe izberite vse stolpce:

    1. Kliknite prvi stolpec (OrderID).

    2. Shift+kliknite zadnji stolpec (Shipper).

    3. S kombinacijo Ctrl+klik izberite stolpce OrderDate, Order_Details.ProductID, Order_Details.UnitPrice in Order_Details.Quantity.

  2. Z desno tipko miške kliknite izbrano glavo stolpca in nato kliknite Odstrani stolpce.

4. korak: izračun vsote vrstice za vsako vrstico tabele »Podrobnosti_naročpila«

V tem koraku ustvarite Stolpec po meri, s katerim izračunate vsoto vrstice za vsako vrstico Order_Details.

Izračun vsote za vsako vrstico tabele »Order_Details«

  1. V podoknu Predogled poizvedbe kliknite ikono tabele ( Ikona tabele ) v zgornjem levem kotu predogleda.

  2. Kliknite Vstavi stolpec > Po meri.

  3. V pogovornem oknu Vstavi stolpec po meri v polju z besedilom Stolpec s formulo po meri vnesite [Order_Details.UnitPrice] * [Order_Details.Quantity].

  4. V polje z besedilom Novo ime stolpca vnesite Line Total.

  5. Kliknite V redu .

Izračun vsote vrstice za vsako vrstico »Order_Details«

5. korak: pretvorba stolpca z letom »DatumNaročila«

V tem koraku stolpec OrderDate pretvorite tako, da upodobi leto datuma naročila.

  1. V podoknu Predogled z desno tipko miške kliknite stolpec OrderDate in nato Pretvori > Year.

  2. Preimenujte stolpec OrderDate v Year:

    1. Dvokliknite stolpec OrderDate in vnesite Year ali

    2. Z desno tipko miške kliknite stolpec OrderDate, kliknite Preimenuj in vnesite Year.

6. korak: vrstice skupine po vrednostih »IDIzdelka« in »Leto«

  1. V podoknu Predogled poizvedbe izberite Year in Order_Details.ProductID.

  2. Z desno tipko miške kliknite eno od glav in nato kliknite Združi po.

  3. V pogovornem oknu Združi po:

    1. V polje z besedilom Novo ime stolpca vnesite Total Sales.

    2. V spustnem polju Postopek izberite Sum.

    3. V spustnem meniju Stolpec izberite Line Total.

  4. Kliknite V redu .

    Pogovorno okno »Združi po« za postopke združevanja

7. korak: preimenovanje poizvedbe

Preden podatke o prodaji uvozite v Excel, poimenujte poizvedbo Total Sales:

  1. V pogovornem oknu Nastavitve poizvedbe v polju z besedilom Ime vnesite Total Sales.

Rezultati končne poizvedbe

Ko izvedete posamezen korak, boste dobili poizvedbo »Total Sales« za vir podatkov Northwind OData.

Total Sales

Ustvarjeni koraki dodatka Power Query

Ko v dodatku Power Query izvajate opravila s poizvedbo, so v podoknu Nastavitve poizvedbe ustvarjeni koraki poizvedbe in navedeni na seznamu UPORABLJENI KORAKI. Vsak korak poizvedbe ima pripadajočo formulo dodatka Power Query, imenovano tudi jezik »M«. Če želite več informacij o jeziku formul za dodatek Power Query, preberite temo Več informacij o formulah dodatka Power Query.

Opravilo

Korak poizvedbe

formula

Povezovanje z virom OData

Vir

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

Razširjanje tabele Order_Details

Razširjanje tabele »Order_Details«

Table.ExpandTableColumn

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

Odstranjevanje drugih stolpcev in prikaz le želenih stolpcev

RemovedColumns

Table.RemoveColumns

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

Izračun vsote vrstice za vsako vrstico Order_Details

InsertedColumns

Table.AddColumn

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

Preoblikovanje stolpca »OrderDate« za upodobitev leta

RenamedColumns

Table.RenameColumns

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

TransformedColumn

Table.TransformColumns

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

RenamedColumns1

Table.RenameColumns

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

Vrstice skupine po vrednostih »ProductID« in »Year«

GroupedRows

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

8. korak: onemogočanje prenosa poizvedbe v Excelov delovni zvezek

Ker poizvedba Total Sales ne predstavlja končnega poročila skupne prodaje po izdelku in letu Total Sales per Product and Year, onemogočite prenos poizvedbe v Excelov delovni zvezek. Ko je možnost Nalaganje v delovni list v podoknu Nastavitve poizvedbeizklopljena, podatkovni rezultati te poizvedbe niso preneseni, poizvedbo pa lahko še vedno združite z drugimi poizvedbami in tako dosežete želen rezultat. Naučite se združiti to poizvedbo s poizvedbo »Products« v naslednjem opravilu.

Onemogočanje prenosa poizvedbe

  1. V podoknu Nastavitve poizvedbe odstranite potrditveno oznako z možnosti Nalaganje v delovni list.

  2. Na traku Urejevalnik poizvedbe kliknite Uporabi in zapri. V podoknu Poizvedbe delovnega zvezka poizvedba Total Sales prikaže, da je nalaganje onemogočeno.

    Onemogočanje prenosa poizvedbe

Na vrh strani

3. opravilo: združevanje poizvedb »Izdelki« in »Skupna prodaja«

Z dodatkom Power Query lahko združite več poizvedb tako, da jih spojite ali priložite. Postopek spajanja je izveden v poljubni poizvedbi dodatka Power Query z obliko tabele, ki ni odvisna od svojega vira podatkov. Če želite več informacij o združevanju virov podatkov, glejte Združevanje več poizvedb.

V tem opravilu združite poizvedbe Products in Total Sales tako, da uporabite korak poizvedbe Spoji in Razširi.

1. korak: spajanje poizvedbe »IDIzdelka« s poizvedbo »Skupna prodaja«

  1. V Excelovem delovnem zvezku poiščite poizvedbo Products na listu List2.

  2. Na zavihku POIZVEDBA na traku kliknite Spoji.

  3. V pogovornem oknu Spoji izberite tabelo Products kot primarno tabelo, nato pa izberite tabelo Total Sales kot sekundarno ali povezano poizvedbo za spajanje. Total Sales bo postal nov razširljiv stolpec.

  4. Če želite tabelo Total Sales primerjati s tabelo Products po vrednosti ProductID, izberite stolpec ProductID v tabeli Products in stolpec Order_Details.ProductID v tabeli Total Sales.

  5. V pogovornem oknu Ravni zasebnosti:

    1. Za osamitev ravni zasebnosti za oba vira podatkov izberite Organizacijsko.

    2. Kliknite Shrani.

  6. Kliknite V redu .

    Varnostno opozorilo : Ravni zasebnosti uporabniku onemogočajo, da nehote združi podatke iz več podatkovnih virov, ki so lahko zasebni ali v lasti organizacije. Od poizvedbe je odvisno, ali lahko uporabnik nato nehote pošlje podatke iz zasebnega podatkovnega vira v drug podatkovni vir, ki je lahko zlonameren. Power Query analizira vsak podatkovni vir in ga razvrsti v določeno raven zasebnosti: »javno«, »organizacijsko« in »zasebno«. Če želite več informacij o ravneh zasebnosti, glejte Ravni zasebnosti.

    Pogovorno okno spajanja

Ko kliknete V redu, postopek spajanja ustvari poizvedbo. V rezultatu poizvedbe so vsi stolpci iz primarne tabele (Products) in en stolpec s povezavo za krmarjenje do povezane tabele (Total Sales). S postopkom razširjanja dodate nove stolpce iz povezane tabele v primarno ali zadevno tabelo.

Dokončanje postopka spajanja

2. korak: razširitev združenega stolpca

V tem koraku razširite spojen stolpec z imenom NewColumn in tako ustvarite dva nova stolpca v poizvedbi Products: Year in Total Sales.

Razširitev povezave tabele »NewColumn«

  1. V podoknu Predogled poizvedbe kliknite ikono za razširitev NovStolpec ( Razširi ).

  2. V spustnem meniju Razširi:

    1. Kliknite (Izberi vse stolpce), da počistite vse stolpce.

    2. KlikniteLeto in Skupna prodaja.

    3. Kliknite V redu.

  3. Preimenujte ta dva stolpca v Year in Total Sales.

  4. Če želite izvedeti, kateri izdelki so bili najbolje prodajani in v katerih letih, uporabite možnost Razvrsti padajoče po vrednosti Total Sales.

  5. Preimenujte poizvedbo v Total Sales per Product.

Razširitev povezave tabele

Ustvarjeni koraki dodatka Power Query

Ko v dodatku Power Query izvajate opravila s poizvedbo Spoji, so v podoknu Nastavitve poizvedbe ustvarjeni koraki poizvedbe in navedeni na seznamu UPORABLJENI KORAKI. Vsak korak poizvedbe ima pripadajočo formulo dodatka Power Query, imenovano tudi jezik »M«. Če želite več informacij o jeziku formul za dodatek Power Query, preberite temo Več informacij o formulah dodatka Power Query.

Opravilo

Korak poizvedbe

formula

Spajanje poizvedbe »ProductID« s poizvedbo »Total Sales«

Vir (vir podatkov za postopek spajanja)

Table.NestedJoin

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

Razširitev združenega stolpca

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: nalaganje poizvedbe »Skupna prodaja po izdelku« v Excelov podatkovni model

V tem koraku onemogočite možnost Naloži v delovni list in naložite poizvedbo v Excelov podatkovni model, da ustvarite poročilo, povezano z rezultatom poizvedbe. Poleg tega, da lahko s poizvedbo Power Query naložite rezultate poizvedbe v Excelov delovni list, jih lahko naložite tudi v Excelov podatkovni model. Ko naložite podatke v Excelov podatkovni model, lahko z dodatkoma Power Pivot in Power View podatke še podrobneje analizirate.

Nalaganje poizvedbe »Total Sales per Product« v Excelov podatkovni model

  1. V podoknu Nastavitve poizvedbe odstranite potrditveno oznako za možnost Naloži v delovni list in potrdite možnost Naloži v podatkovni model.

  2. Če želite poizvedbo naložiti v Excelov podatkovni model, kliknite Uporabi in zapri.

Nalaganje Excelovega podatkovnega modela

Končna poizvedba »Skupna prodaja po izdelku«

Ko izvedete posamezen korak, dobite poizvedbo Total Sales per Product, v kateri so združeni podatki iz datoteke Products and Orders.xlsx in vira Northwind OData. Poizvedbo lahko uporabite za model Power Pivot. S spremembami poizvedbe v programu Power Query poleg tega spremenite in osvežite nastalo tabelo v modelu Power Pivot.

Na vrh strani

Opomba : Urejevalnik poizvedbe se prikaže le pri nalaganju, urejanju ali ustvarjanju nove poizvedbe z dodatkom Power Query. V tem videoposnetku si lahko ogledate, kako se prikaže okno Urejevalnik poizvedbe med urejanjem poizvedbe Excelovega delovnega zvezka. Če želite prikazati urejevalnik poizvedbe brez nalaganja ali urejanja obstoječe poizvedbe delovnega lista, v razdelku Pridobivanje zunanjih podatkov na zavihu Power Query na traku izberite Iz drugih virov > Prazna poizvedba. V tem videoposnetku si lahko ogledate način, kako prikazati urejevalnik poizvedbe.

Kako prikazati urejevalnik za poizvedbe v Excelu

Opomba : Strojni prevod – zavrnitev odgovornosti: Ta članek je bil preveden z računalniškim programom brez človeškega posredovanja. Microsoft skuša s strojno prevedenimi članki vsebino o Microsoftovih izdelkih, storitvah in tehnologijah približati osebam, ki ne razumejo angleščine. Ker je bil članek strojno preveden, so lahko v njem jezikovne, slovnične in pravopisne napake.

Razširite svoja znanja
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×