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

V tem opravilu uvozite izdelke iz datoteke »Izdelki« in »Orders.xlsx« (prenesene in preimenovane zgoraj) v Excelov delovni zvezek, povišate vrstice v glave stolpcev, odstranite nekatere stolpce in naložite poizvedbo na delovni list.

1. korak: povezovanje z Excelovim delovnim zvezkom

1. Ustvarite Excelov delovni zvezek.

2. Izberite Podatkovni > Pridobi podatke >iz > iz delovnega zvezka.

3. V pogovornem oknu Uvoz podatkov poiščite in poiščite preneseno Products.xlsx, nato pa izberite Odpri.

4. V podoknu Krmar dvokliknite tabelo Izdelki . Prikaže se Urejevalnik poizvedb Napajanje.

2. korak: Preglejte korake poizvedbe

Privzeto Power Query vam samodejno doda več korakov. Če želite izvedeti več , preglejte vsak korak v razdelku Uporabljeni koraki v podoknu Nastavitve poizvedbe.

1. Z desno tipko miške kliknite korak Vir in izberite Uredi nastavitve. Ta korak je bil ustvarjen, ko ste uvozili delovni zvezek.

2. Z desno tipko miške kliknite korak krmarjenja in izberite Uredi nastavitve. Ta korak je bil ustvarjen, ko ste izbrali tabelo v pogovornem oknu Krmarjenje.

3. Z desno tipko miške kliknite korak Spremenjena vrsta in izberite Uredi nastavitve. Ta korak je ustvaril Power Query ki je predpostavljal podatkovne tipe vsakega stolpca. Izberite puščico dol desno od 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 ProductID, ProductName, CategoryID in QuantityPerUnit (uporabite Ctrl+klik ali Shift+klik).

2. Izberite Odstrani stolpce > Odstrani druge stolpce.

   

4. korak: Nalaganje poizvedbe za izdelke

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: Power Query, ustvarjene v opravilu 1

Med izvajanjem dejavnosti poizvedbe v Power Query se ustvarijo koraki poizvedbe in so 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 Power Query formulah, glejte Power Query formul v Excelu.

Opravilo	Korak poizvedbe	Formula
Uvoz Excelovega delovnega zvezka	Izvirna vrednost	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Izberite tabelo »Izdelki«	Krmarjenje	= Source{[Item="Products",Kind="Table"]}[Data]
Power Query samodejno zazna podatkovne tipe stolpcev	Spremenjena vrsta	= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", vnesite 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 drugi stolpci	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

V tem opravilu uvozite podatke v Excelov delovni zvezek iz vzorčnega vira Northwind OData v storitvi http://services.odata.org/Northwind/Northwind.svc,razširite tabelo Order_Details, odstranite stolpce, izračunate skupno vrstico, pretvorite datumNaročila, združite vrstice po argumentih »IDIzdelka« in »Leto«, preimenujte poizvedbo in onemogočite prenos poizvedbe v Excelov delovni zvezek.

1. korak: Vzpostavljanje povezave z virom OData

1. Izberite Podatkovni > Pridobi podatke> iz drugih virov in >iz vira OData.

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

3. Izberite V redu.

4. V podoknu Krmar dvokliknite tabelo Naročila .

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 zaženete poizvedbo, so vrstice iz povezane tabele (Order_Details) združene v vrstice s primarno tabelo (Naročila).

V Power Query stolpec, ki vsebuje povezano tabelo, v celici vsebuje vrednost Zapis ali Tabela. Ti stolpci 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 s tujim ključem v viru OData ali odnosu s tujim ključem v SQL Server podatkov.

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 predogledu podatkov se pomaknite vodoravno do Order_Details podatkov.

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

3. V spustnem meniju Razširi:

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

   2. Izberite PRODUCTID, UnitPrice in Quantity.

   3. Izberite V redu.

      

      Opomba: V Power Query lahko razširite tabele, povezane iz stolpca, in združite stolpce povezane tabele, preden razširite podatke v tabeli zadeve. Č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 tabele »Podrobnosti_naročpila«

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 Formula stolpca po meri vnesite [Order_Details.CenaEnote] * [Order_Details.Količina].

4. V polje Novo ime stolpca vnesite Vsota vrstic.

5. Izberite V redu.

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 DatumNaročila in izberite Pretvori > Leto.

2. Preimenujte stolpec OrderDate v Year:

   1. Dvokliknite stolpec OrderDate in vnesite Year ali

   2. Right-Click stolpcu DatumNaročila izberite Preimenuj in vnesite Leto.

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

1. V predogledu podatkov izberite Yearin 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 Total Sales.

Rezultati: Končna poizvedba za opravilo 2

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

Povzetek: Power Query, ustvarjene v opravilu 2 

Med izvajanjem dejavnosti poizvedbe v Power Query se ustvarijo koraki poizvedbe in so 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 Power Query formulah, glejte Več informacij Power Query formulah.

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«	Dodano 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])
Spremeni se v bolj pomenljivo ime, Lne Total	Preimenovani stolpci	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Preoblikovanje stolpca »OrderDate« za upodobitev leta	Izvlečeno leto	= Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}})
Spremenite v  bolj smiselna imena, »DatumNaroč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}})

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 za spajanje in operacijo Razširi, nato pa naložite poizvedbo Total Sales per Product v Excelov podatkovni model.

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

1. V Excelovem delovnem zvezku poiščite poizvedbo »Izdelki « na zavihku delovnega lista »Izdelki«.

2. Izberite celico v poizvedbi in nato izberite Poizvedba v> spoji.

3. V pogovornem oknu Spajanje izberite Products kot primarno tabelo in nato 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.

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

   

Rezultat

Z operacijo spajanja ustvarite poizvedbo. V rezultatu poizvedbe so vsi stolpci iz primarne tabele (Izdelki) in en strukturiran stolpec tabele v povezano tabelo (Total Sales). Izberite ikono Razširi , da dodate 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«: »Leto « in »Skupna prodaja«.

1. V predogledu podatkov izberite Ikono za razširitev () ob možnosti NewColumn.

2. Na spustnem seznamu Razširi:

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

   2. Izberite Leto inSkupna 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 izdelki dobili največji obseg prodaje, izberite Razvrsti padajoče po skupni prodaji.

5. Preimenujte poizvedbo v Total Sales per Product.

Rezultat

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 dodatno analizirate podatke.

1. Izberite Osnovno > Zapri & naloži.

2. V pogovornem oknu Uvoz podatkov se prepričajte, da ste izbrali 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 , ki združuje podatke iz datoteke Products.xlsx in vira Northwind OData. Ta poizvedba je uporabljena za model Power Pivot. Poleg tega spremembe poizvedbe spremenijo in osvežijo nastalo tabelo v podatkovnem modelu.

Povzetek: Power Query koraki, ustvarjeni v opravilu 3

Med izvajanjem dejavnosti spajanja poizvedbe v Power Query se ustvarijo koraki poizvedbe in so 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 Power Query formulah, glejte Več informacij Power Query formulah.

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	Expanded Total Sales	= 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"}})
Razvrščanje skupne prodaje v naraščajočem vrstnem redu	Razvrščene vrstice	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})