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

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.
   

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

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.
      

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

5. korak: Preoblikovanje stolpca »OrderDate«

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 Leto pretvorbe>.

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: združevanje vrstic po ID-ju izdelka in letu

1. V predogledu podatkov izberite Year in Order_Details.ProductID.

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.
   

7. korak: Preimenovanje poizvedbe

Preden uvozite podatke o prodaji v Excel, preimenujte poizvedbo:

• V podoknu Nastavitve 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.

Povzetek: Koraki dodatka Power Query, ustvarjeni v opravilu 2

Med izvajanjem dejavnosti poizvedbe v dodatku Power Query so ustvarjeni koraki poizvedbe in navedeni v podoknu Nastavitve 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	= Vir{[Ime="Naročila"]}[Podatki]
Razširjanje tabele Order_Details	Razširjanje tabele »Order_Details«	= Table.ExpandTableColumn(Naročila, »Order_Details«, {»IDizdelka«, »CenaEnote«, »Količina«}, {"Order_Details.IDizdelka", »Order_Details.CenaEnote«, »Order_Details.Količina«})
Odstranjevanje drugih stolpcev in prikaz le želenih stolpcev	RemovedColumns	= Table.RemoveColumns(#"Razširi Order_Details",{"ID naročila", "ID stranke", "ID zaposlenega", "ZahtevaniDatum", "Datum pošiljanja", "ShipVia", "Tovor", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Izračun vsote vrstice za vsako vrstico »Order_Details«	Dodano po meri	= Table.AddColumn(RemovedColumns, »Po meri«, vsak [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Razširjeno Order_Details", "Vsota vrstice", vsak [Order_Details.CenaEnote] * [Order_Details.Količina])
Sprememba v bolj smiselno ime, Lne Total	Preimenovani stolpci	= Tabela.PreimenujStolpci(VstavljenoCustom,{{"Po meri", "Vsota vrstice"}})
Preoblikovanje stolpca »OrderDate« za upodobitev leta	Izvlečeno leto	= Tabela.TransformColumns(#"Združene vrstice",{{"Leto", Datum.Leto, Int64.Vrsta}})
Spremenite v bolj smiselna imena, OrderDate in Year	Preimenovani stolpci 1	Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}})
Vrstice skupine po vrednostih »ProductID« in »Year«	GroupedRows	= Table.Group(PreimenovaniStolpci1, {"Leto", "Order_Details.IDizdelka"}, {{"Skupna prodaja", vsak List.Sum([Vsota vrstice]), vnesite število}})

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 « s poizvedbo »Spajanje « in »Razširi« , nato pa naložite poizvedbo »Skupna prodaja na izdelek« v Excelov podatkovni model.

1. korak: Spajanje ID-izdelka v poizvedbo »Skupna prodaja«

1. V Excelovem delovnem zvezku se pomaknite do poizvedbe Izdelki na zavihku Izdelki .

2. Izberite celico v poizvedbi in nato izberite Spajanje poizvedbe>.

3. V pogovornem oknu Spajanje izberite Izdelki kot primarno tabelo in izberite Skupna prodaja kot sekundarno ali povezano poizvedbo, ki jo želite spojiti. Skupna prodaja bo postala nov strukturirani stolpec z ikono razširitve.

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.

   

Rezultat

Operacija spajanja ustvari poizvedbo. Rezultat poizvedbe vsebuje vse stolpce iz primarne tabele (Izdelki) in en strukturirani stolpec tabele do povezane tabele (Skupna prodaja). Izberite ikono Razširi, če želite dodati nove stolpce v primarno tabelo iz sekundarne ali povezane tabele.

2. korak: razširitev spojenega stolpca

V tem koraku razširite spojeni stolpec z imenom NewColumn , da ustvarite dva nova stolpca v poizvedbi »Izdelki «: Year in Total Sales.

1. V predogledu podatkov izberite ikono 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 in v katerih letih so imeli največji obseg prodaje, izberite Razvrsti padajoče glede na skupno prodajo.

5. Preimenujte poizvedbo v Total Sales per Product.

Rezultat

3. korak: Nalaganje poizvedbe »Skupna prodaja na izdelek« 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 uporabite Power Pivot za nadaljnjo analizo podatkov.

1. Izberite Domov>Zapri & Nalaganje.
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