Naučite se združevati več virov podatkov (Power Query)

Velja za
Excel za Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

V tej vadnici boste lahko z Urejevalnik poizvedb Power Query uvozili podatke iz lokalne Excelove datoteke, v kateri so podatki o izdelku, in iz vira OData, v katerem so podatki o naročilu izdelka. Izvedli boste razne korake pretvorbe in združevanja ter združili podatke iz obeh virov ter s tem poročilo »Skupna prodaja po izdelku in letu«.   

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

1. opravilo: uvoz izdelkov v Excelov delovni zvezek

V tem opravilu boste uvozili izdelke iz datoteke Products and Orders.xlsx (preneseni in preimenovani zgoraj) v Excelov delovni zvezek, povišali vrstice v glave stolpcev, odstranili nekaj stolpcev in naložili poizvedbo na delovni list.

1. korak: povezovanje z Excelovim delovnim zvezkom

  1. Ustvarite Excelov delovni zvezek.
  2. Izberite možnost »Pridobite>podatke>iz datoteke«>iz delovnega zvezka.
  3. V pogovornem oknu »Uvoz podatkov « poiščite Products.xlsx datoteko, ki ste jo prenesli, in nato izberite »Odpri«.
  4. V podoknu krmarja dvokliknite tabelo »Izdelki« . Prikaže se urejevalnik Power Query.

2. korak: Preglejte korake poizvedbe

Power Query privzeto samodejno doda nekaj korakov, da vam olajša delo. Če želite izvedeti več, preglejte posamezne korake v razdelku »Uporabljeni koraki « v podoknu z nastavitvami poizvedbe .

  1. Z desno tipko miške kliknite korak »Vir « in izberite »Uredi nastavitve«. Ta korak je ustvaril, ko ste uvozili delovni zvezek.
  2. Z desno tipko miške kliknite korak za krmarjenje in izberite »Uredi nastavitve«. Ta korak je nastal, ko ste izbrali tabelo v pogovornem oknu za krmarjenje .
  3. Z desno tipko miške kliknite korak spremenjene vrste in izberite »Uredi nastavitve«. Ta korak je ustvaril dodatek Power Query, ki je določil podatkovne tipe posameznih stolpcev. Izberite puščico dol na desni strani vnosne vrstice, da si ogledate celotno formulo.

3. korak: odstranjevanje ostalih stolpcev za prikaz pomembnih stolpcev

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

  1. V predogledu podatkov izberite stolpce »IDIzdelka«,»ImeIzdelka«, »IDkategorije« in »KoličinaPerEnota « (uporabite tipke Ctrl+klik ali Shift+klik).
  2. Izberite »Odstrani stolpce>« Odstrani druge stolpce.
    Skrivanje drugih stolpcev

4. korak: nalaganje poizvedbe izdelkov

V tem koraku naložite poizvedbo » Izdelki « v Excelov delovni list.

  • Izberite »Osnovno>« Zapri & naloži. Poizvedba se prikaže na novem Excelovem delovnem listu.

Povzetek: Koraki Power Query, ustvarjeni v 1. opravilu

Ko v dodatku Power Query izvajate opravila s poizvedbo, so koraki poizvedbe ustvarjeni in navedeni v podoknu nastavitev poizvedbe na seznamu »Uporabljeni koraki«. Vsak korak poizvedbe ima pripadajočo formulo dodatka Power Query, imenovano tudi jezik »M«. Če želite več informacij o formulah Power Query, glejte Ustvarjanje formul Power Query v Excelu.

Opravilo Korak poizvedbe Formula
Uvoz Excelovega delovnega zvezka Izvirna vrednost = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Select the Products table Krmarjenje = source{[item="Products",Kind="Table"]}[Data]
Power Query samodejno zazna podatkovne vrste stolpca Spremenjena 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}})
Odstranjevanje drugih stolpcev in prikaz le želenih stolpcev Odstranjeni so drugi stolpci = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

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 http://services.odata.org/Northwind/Northwind.svc, razširili tabelo Order_Details, odstranili stolpce, izračunali vsoto vrstice, pretvorili datum naročila, združili vrstice glede na IDIzdelka in leto, preimenovali poizvedbo in onemogočili prenos poizvedbe v Excelov delovni zvezek.

1. korak: povezovanje z virom OData

  1. Izberite podatke>Pridobite podatke>iz drugih virov>iz vira OData.
  2. V pogovorno okno Vir OData vnesite spletni naslov za vir Northwind OData.
  3. Izberite V redu.
  4. V podoknu krmarja dvokliknite tabelo »Naročila «.

2. korak: Razširitev Order_Details tabele

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 s primarno tabelo (Naročila).

V dodatku Power Query je v stolpcu s povezano tabelo v celici vrednost Zapis ali Tabela. Ti se imenujejo strukturirani stolpci. Zapis označuje en povezan zapis in predstavlja relacijo »ena proti ena« s trenutnimi podatki ali primarno tabelo. Tabela označuje povezano tabelo in predstavlja relacijo »ena proti mnogo« s trenutno ali primarno tabelo. Strukturiran stolpec predstavlja relacijo v viru podatkov, ki ima relacijski model. Strukturiran stolpec na primer označuje entiteto s povezavo tujega ključa v viru OData ali povezavo tujega ključa v zbirki podatkov strežnika SQL Server.

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

  1. V predogledu podatkov se pomaknite vodoravno do stolpca Order_Details .

  2. V stolpcu Order_Details izberite ikono za razširitev (Razširi).

  3. V spustnem meniju Razširi:

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

    2. Izberite »IDIzdelka«,»CenaEnote« in »Količina«.

    3. Izberite V redu.
      Razširitev povezave tabele »Order_Details«

      Opomba

      V dodatku Power Query lahko razširite tabele, povezane iz stolpca, in združite stolpce 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. 

  1. V predogledu podatkov izberite te stolpce:

    1. Izberite prvi stolpec, »IDNaročila«.
    2. Shift+kliknite zadnji stolpec, pošiljatelj.
    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 izberite »Odstrani druge stolpce«.

4. korak: izračun vsote vrstice za vsako vrstico Order_Details

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

  1. V predogledu podatkov izberite ikono tabele (ikona tabele ) v zgornjem levem kotu predogleda.
  2. Kliknite »Dodaj stolpec po meri«.
  3. V pogovornem oknu »Stolpec po meri « v polje s formulo stolpca po meri vnesite [Order_Details.CenaEnote] * [Order_Details.Količina].
  4. V polje » Novo ime stolpca « vnesite »Line Total«.
  5. Izberite 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 predogledu podatkov z desno tipko miške kliknite stolpec »OrderDate « in izberite »Pretvori>leto«.

  2. Preimenujte stolpec OrderDate v Year:

    1. Dvokliknite stolpec OrderDate in vnesite Year ali
    2. Right-Click stolpcu »OrderDate « izberite »Preimenuj« in vnesite »Leto«.

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

  1. V predogledu podatkov izberite »Leto« in »Order_Details.IDizdelka«.

  2. Right-Click eno od glav in izberite »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. Izberite V redu.
    Pogovorno okno »Združi po« za postopke združevanja

7. korak: preimenovanje poizvedbe

Preden podatke o prodaji uvozite v Excel, preimenujte poizvedbo:

  • V podoknu z nastavitvami poizvedbe v polje » Ime « vnesite »Skupna prodaja«.

Rezultati: Končna poizvedba za opravilo 2

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

Prodaja skupaj

Povzetek: Koraki Power Query, ustvarjeni v 2. opravilu

Ko v dodatku Power Query izvajate opravila s poizvedbo, so koraki poizvedbe ustvarjeni in navedeni v podoknu nastavitev poizvedbe na seznamu »Uporabljeni koraki«. Vsak korak poizvedbe ima pripadajočo formulo dodatka Power Query, imenovano tudi jezik »M«. Če želite več informacij o formulah Power Query, glejte Več informacij o formulah Power Query.

Opravilo Korak poizvedbe Formula
Povezovanje z virom OData Vir = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
Select a table Krmarjenje = 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« Dodan način »Po meri« = 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])
Spremenite ime v bolj smiselno ime, Lne Total Preimenovani stolpci = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Preoblikovanje stolpca »OrderDate« za upodobitev leta Ekstrahirano leto = Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}})
Spremenite v
bolj pomenljiva imena, datum naročila in leto		 Preimenovani stolpci 1 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}})

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 »Izdelki« in »Skupna prodaja « tako, da uporabite poizvedbo za spajanje in postopek razširitve , nato pa naložite poizvedbo »Skupna prodaja po izdelku« v Excelov podatkovni model.

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

  1. V Excelovem delovnem zvezku se pomaknite do poizvedbe » Izdelki « na zavihku delovnega lista »Izdelki« .

  2. Izberite celico v poizvedbi, nato pa izberite »Spajanje poizvedbe«>.

  3. V pogovornem oknu »Spoji « izberite »Izdelki« kot primarno tabelo in izberite »Total Sales « kot sekundarno ali povezano poizvedbo za spajanje. Total Sales bo postal nov strukturiran stolpec z ikono za razširitev.

  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. Izberite Shrani.

  6. Izberite V redu.

    Opomba

    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 Nastavitev ravni zasebnosti.

    Pogovorno okno spajanja

Rezultat

Postopek spajanja ustvari poizvedbo. V rezultatu poizvedbe so vsi stolpci iz primarne tabele (Izdelki) in en strukturiran stolpec tabele v povezani tabeli (Total Sales). Izberite ikono za razširitev , če želite dodati nove stolpce v primarno tabelo iz sekundarne ali povezane tabele.

Dokončanje postopka spajanja

2. korak: Razširitev spojenega stolpca

V tem koraku razširite spojeni stolpec z imenom »NovStolpec «, da v poizvedbi » Izdelki« ustvarite dva nova stolpca: »Leto« in »Skupna prodaja«.

  1. V predogledu podatkov izberite ikono za razširitev (razširi) poleg možnosti »NewColumn«.

  2. Na spustnem seznamu »Razširi« :

    1. Izberite (Izberi vse stolpce), da počistite vse stolpce.
    2. Izberite »Leto« in »Skupna prodaja«.
    3. Izberite V redu.

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

  4. Če želite izvedeti, kateri izdelki so bili najbolj prodani, in v katerih letih so bili ti izdelki največji od prodaje, izberite »Razvrsti padajoče po skupni prodaji«.

  5. Preimenujte poizvedbo v Total Sales per Product.

Rezultat

Razširitev povezave tabele

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

V tem koraku naložite poizvedbo v Excelov podatkovni model, da ustvarite poročilo, povezano z rezultatom poizvedbe. Ko naložite podatke v Excelov podatkovni model, lahko z dodatkom Power Pivot še podrobneje analizirate podatke.

  1. Izberite »Osnovno>« Zapri & naloži.
  2. V pogovornem oknu »Uvoz podatkov « izberite »Dodaj te podatke v podatkovni model«. Če želite več informacij o uporabi tega pogovornega okna, izberite vprašaj (?).

Rezultat

Imate poizvedbo »Total Sales per Product «, v kateri so združeni podatki iz datoteke Products.xlsx in vira Northwind OData. Ta poizvedba velja za model Power Pivot. S spremembami poizvedbe poleg tega spremenite in osvežite nastalo tabelo v podatkovnem modelu.

Povzetek: Koraki Power Query, ustvarjeni v 3. opravilu

Ko v dodatku Power Query izvajate opravila s poizvedbo za spajanje, so v podoknu z nastavitvami 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 formulah Power Query, glejte Več informacij o formulah 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"}, "Total Sales", JoinKind.LeftOuter)
Razširitev združenega stolpca Razširjena skupna prodaja = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Preimenovanje dveh stolpcev Preimenovani stolpci = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Razvrsti skupno prodajo v naraščajočem vrstnem redu Razvrščene vrstice = Table.Sort(#"Preimenovani stolpci",{{"Total Sales", Order.Ascending}})

Glejte tudi

Pomoč za Power Query za Excel