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

V tem opravilu uvozite izdelke iz datoteke »Izdelki« in »Orders.xlsx« (preneseni in preimenovani zgoraj) v delovni zvezek programa Excel, povišate vrstice v glave stolpcev, odstranite nekaj stolpcev in naložite poizvedbo na delovni list.

1. korak: povezovanje z Excelovim delovnim zvezkom

1. Ustvarite Excelov delovni zvezek.

2. Izberite Možnost>Get Data > From File > From Workbook (> 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 dodatka Power Query.

2. korak: Preglejte korake poizvedbe

Power Query privzeto samodejno doda nekaj korakov, ki so priročni za vas. Preglejte vsak korak v razdelku Uporabljeni koraki v podoknu Nastavitve, če želite izvedeti več.

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 krmarjenje 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 sklepal vrste podatkov za vsak stolpec. 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 predogledupodatkov izberite stolpce ProductID, ProductName, CategoryIDin QuantityPerUnit (uporabite 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 delovni Excel list.

• Izberite Osnovno > Zapri & nalaganje. Poizvedba se prikaže v novem delovnem Excel list.

Povzetek: koraki dodatka Power Query, ustvarjeni v opravilu 1

Ko v dodatku Power Query izvajate opravila s poizvedbo, so v podoknu Poizvedbe ustvarjeni koraki Nastavitve 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 dodatka Power Query, glejte Ustvarjanje formul dodatka Power Query Excel.

Opravilo	Korak poizvedbe	Formula
Uvoz Excel 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", 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 drugi stolpci	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

V tem opravilu uvozite podatke v delovni zvezek Excel iz vzorčnega vira Northwind OData v brskalniku http://services.odata.org/Northwind/Northwind.svc,razširite tabelo Order_Details, odstranite stolpce, izračunate vsoto vrstice, pretvorite datum Naročila, združite vrstice po vrednostih ProductID in Year, preimenujte poizvedbo in onemogočite prenos poizvedbe v delovni zvezek Excel.

1. korak: Povezovalnik vira OData

1. Izberite možnost > Dobi 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 se poizvedba zažene, so vrstice iz povezane tabele (Order_Details) združene v vrstice s primarno tabelo (Naročila).

V dodatku Power Query ima stolpec, ki vsebuje povezano tabelo, v celici vrednost Zapis ali Tabela. To se imenuje 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 podatkovse pomaknite vodoravno do Order_Details podatkov.

2. V stolpcu 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, UnitPricein Quantity.

   3. Izberite V redu.

      

      Opomba: V dodatku Power Query lahko razširite tabele, ki so povezane iz stolpca, in združite stolpce povezane tabele, preden razširite podatke v tej 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 predogledupodatkov izberite te stolpce: 

   1. Izberite prvi stolpec, »ID naroč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 predogledupodatkov 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.UnitPrice] * [Order_Details.Quantity].

4. V polje Novo ime stolpca vnesite Line Total.

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 podatkovz 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 Preimenujin vnesite Year.

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

1. V predogledu podatkovizberite Year inOrder_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 polje Ime vnesite Total Sales (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 

Ko v dodatku Power Query izvajate opravila s poizvedbo, so v podoknu Poizvedbe ustvarjeni koraki Nastavitve 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 dodatka Power Query, glejte Več informacij o formulah dodatka 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«	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])
Spremenite se v bolj pomenljivo ime, Nja 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  more meaningful names, OrderDate and Year	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 Products in Total Sales s poizvedbo Spoji in Razširi, nato pa naložite poizvedbo Total Sales per Product v Excel model.

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

1. V delovnem Excel izdelkov poiščite poizvedbo Izdelki na zavihkudelovnega lista Izdelki.

2. Izberite celico v poizvedbi in nato izberite Poizvedba za spajanje> .

3. V pogovornem oknu Spajanje za primarno tabelo izberite Products 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 stolpec, ki ima v povezani tabeli strukturirano tabelo (Total Sales). Izberite ikono za razširitev, č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 Products:Year in Total Sales.

1. V predogledu podatkovizberite Ikona za razširitev () zraven možnosti NewColumn.

2. Na spustnem seznamu Razširi:

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

   2. Izberite Year in Total Sales ( Leto) in Total Sales (Skupna prodaja).

   3. Izberite 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, 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 podatkovni Excel model, da ustvarite poročilo, povezano z rezultatom poizvedbe. Ko naložite podatke v Excel model,lahko za nadaljnjo analizo podatkov uporabite Power Pivot.

1. Izberite Osnovno > Zapri & nalaganje.

2. V pogovornem oknu Uvoz podatkov preverite, ali 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 Products.xlsx in vira Northwind OData. Ta poizvedba je uporabljena za model Power Pivot. Poleg tega spremenite poizvedbo in osvežite nastalo tabelo v podatkovnem modelu.

Povzetek: koraki dodatka Power Query, ustvarjeni v opravilu 3

Ko v dodatku Power Query izvajate opravila s poizvedbo Spoji, so v podoknu Poizvedbe ustvarjeni koraki Nastavitve 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 dodatka Power Query, glejte 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"}, "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}})