Naučte sa skombinovať viaceré zdroje údajov (Power Query)

V tejto úlohe importujete produkty zo súboru Produkty a Orders.xlsx (stiahnuté a premenované vyššie) do excelového zošita, zvýšite úroveň riadkov na hlavičky stĺpcov, odstránite niektoré stĺpce a načítate dotaz do hárka.

Krok 1: Pripojenie k excelovému zošitu

1. Otvorte excelový zošit.

2. Vyberte položku Údaje > Získať údaje > zo súboru > zo zošita.

3. V dialógovom okne Import údajov vyhľadajte a vyhľadajte Products.xlsx súbor, ktorý ste stiahli, a potom vyberte položku Otvoriť.

4. Na table Navigátor dvakrát kliknite na tabuľku Produkty . Zobrazí sa power Editor Power Query.

Krok 2: Preskúmanie krokov dotazu

Podľa predvoleného nastavenia Power Query automaticky pridá niekoľko krokov ako pohodlie pre vás. Ďalšie informácie nájdete v časti Použité kroky na table Nastavenia dotazu .

1. Kliknite pravým tlačidlom myši na krok Zdroj a vyberte položku Upraviť nastavenia. Tento krok bol vytvorený pri importovaní zošita.

2. Kliknite pravým tlačidlom myši na krok Navigácia a vyberte položku Upraviť nastavenia. Tento krok bol vytvorený, keď ste vybrali tabuľku v dialógovom okne Navigácia .

3. Kliknite pravým tlačidlom myši na krok Zmenený typ a vyberte položku Upraviť nastavenia. Tento krok vytvoril Power Query, ktorý odvodil typy údajov každého stĺpca. Výberom šípky nadol napravo od riadka vzorcov zobrazíte úplný vzorec.

Krok 3: Odstránenie ostatných stĺpcov, aby sa zobrazovali iba požadované stĺpce

V tomto kroku odstránite všetky stĺpce okrem stĺpcov ProductID (IDProduktu), ProductName (NázovProduktu), CategoryID (IDKategórie) a QuantityPerUnit (MnožstvoNaJednotku).

1. V ukážke údajov vyberte stĺpce ProductID (ID produktu), ProductName (NázovProduktu), CategoryID (IDKategórie) a QuantityPerUnit (použite kombináciu klávesov Ctrl + kliknutie alebo Shift + kliknutie).

2. Vyberte položku Odstrániť stĺpce > odstrániť ostatné stĺpce.

   

Krok 4: Načítanie dotazu na produkty

V tomto kroku načítate dotaz Produkty do excelového hárka.

• Vyberte položku Domov > Zavrieť & načítať. Dotaz sa zobrazí v novom excelovom hárku.

Súhrn: Power Query kroky vytvorené v úlohe 1

Počas vykonávania aktivít dotazu v Power Query sa vytvoria kroky dotazu a zobrazia sa na table Nastavenia dotazu v zozname Použité kroky. Každý krok dotazu obsahuje zodpovedajúci Power Query vzorec, známy aj ako jazyk M. Ďalšie informácie o Power Query vzorcoch nájdete v téme Vytvorenie Power Query vzorcov v Exceli.

Úloha	Krok dotazu	Vzorec
Import excelového zošita	Zdrojová mena	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Vyberte tabuľku Produkty	Navigácia	= Source{[Item="Products",Kind="Table"]}[Údaje]
Power Query automaticky zisťuje typy údajov stĺpcov	Changed Type (Zmenený typ)	= 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}})
Odstránenie ostatných stĺpcov, aby sa zobrazovali iba požadované stĺpce	Odstránené ostatné stĺpce	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

V tejto úlohe importujete údaje do excelového zošita zo vzorového informačného kanála OData Northwind na http://services.odata.org/Northwind/Northwind.svc,rozbaľte tabuľku Order_Details, odstránite stĺpce, vypočítate súčet riadka, transformujete dátum objednávky, zoskupíte riadky podľa idproduktu a roka, premenujete dotaz a vypnete sťahovanie dotazu do excelového zošita.

Krok 1: Pripojenie k informačnému kanálu OData

1. Vyberte položku Údaje > Získať údaje > z iných zdrojov > z informačného kanála OData.

2. V dialógovom okne Informačný kanál OData zadajte URL informačného kanála Northwind OData.

3. Vyberte tlačidlo OK.

4. Na table Navigátor dvakrát kliknite na tabuľku Objednávky .

Krok 2: Rozbalenie tabuľky Podrobnosti_objednávky

V tomto kroku rozbalíte tabuľku Podrobnosti_objednávky, ktorá súvisí s tabuľkou Objednávky, a skombinujete stĺpce ProductID (IDProduktu), UnitPrice (JednotkováCena) a Quantity (Množstvo) z tabuľky Podrobnosti_objednávky s tabuľkou Objednávky. Operáciou Rozbaliť sa skombinujú stĺpce zo súvisiacej tabuľky s objektovou tabuľkou. Po spustení dotazu sa riadky zo súvisiacej tabuľky (Order_Details) skombinujú do riadkov s primárnou tabuľkou (Objednávky).

V Power Query má stĺpec obsahujúci súvisiacu tabuľku v bunke hodnotu Záznam alebo Tabuľka. Nazývajú sa štruktúrované stĺpce. Záznam označuje jeden súvisiaci záznam a predstavujevzťah "one-to-one" s aktuálnymi údajmi alebo primárnou tabuľkou. Tabuľka označuje súvisiacu tabuľku a predstavuje vzťah "one-to-many" s aktuálnou alebo primárnou tabuľkou. Štruktúrovaný stĺpec predstavuje vzťah v zdroji údajov, ktorý má relačný model. Štruktúrovaný stĺpec napríklad označuje entitu s priradením cudzieho kľúča v informačnom kanáli OData alebo vzťah cudzieho kľúča v databáze SQL Server.

Po rozbalení tabuľky Podrobnosti_objednávky sa do tabuľky Objednávky pridajú tri nové stĺpce a ďalšie riadky, pričom každý sa priradí k riadku vnorenej alebo súvisiacej tabuľky.

1. V ukážke údajov sa posuňte vodorovne na stĺpec Order_Details .

2. V stĺpci Order_Details vyberte ikonu rozbalenia ().

3. V rozbaľovacom zozname Rozbaliť:

   1. Výberom položky (Vybrať všetky stĺpce) vymažete všetky stĺpce.

   2. Vyberte položky IdProduktu, JednotkováCena a Množstvo.

   3. Vyberte tlačidlo OK.

      

      Poznámka: V Power Query môžete rozbaliť tabuľky prepojené zo stĺpca a agregovať stĺpce prepojenej tabuľky pred rozbalením údajov v predmete tabuľky. Ďalšie informácie o vykonaní operácií agregácie nájdete v téme Agregácia údajov zo stĺpca.

Krok 3: Odstránenie ostatných stĺpcov, aby sa zobrazovali iba požadované stĺpce

V tomto kroku môžete odstrániť všetky stĺpce okrem stĺpcov OrderDate (DátumObjednávky), ProductID (IDProduktu), UnitPrice (JednotkováCena) a Quantity (Množstvo). 

1. V ukážkeúdajov vyberte nasledujúce stĺpce: 

   1. Vyberte prvý stĺpec OrderID.

   2. Shift + Kliknutie na posledný stĺpec, Špeditér.

   3. Stlačte kláves Ctrl a kliknite na stĺpce OrderDate (DátumObjednávky), Order_Details.ProductID (Podrobnosti_objednávky.IDProduktu), Order_Details.UnitPrice (Podrobnosti_objednávky.JednotkováCena) a Order_Details.Quantity (Podrobnosti_objednávky.Množstvo).

2. Kliknite pravým tlačidlom myši na vybratú hlavičku stĺpca a vyberte položku Odstrániť ostatné stĺpce.

Krok 4: Výpočet celkovej hodnoty pre riadok za každý riadok tabuľky Podrobnosti_objednávky

V tomto kroku môžete vytvoriť vlastný stĺpec na výpočet celkovej hodnoty pre riadok za každý riadok tabuľky Podrobnosti_objednávky.

1. V ukážke údajov vyberte ikonu tabuľky () v ľavom hornom rohu ukážky.

2. Kliknite na položku Pridať vlastný stĺpec.

3. V dialógovom okne Vlastný stĺpec zadajte do poľa Vzorce vlastného stĺpca[Order_Details.JednotkováCena] * [Order_Details.Quantity].

4. Do poľa Názov nového stĺpca zadajte súčet riadkov.

5. Vyberte tlačidlo OK.

Krok 5: Transformácia stĺpca roka dátumu objednávky

V tomto kroku transformujete stĺpec OrderDate (DátumObjednávky) tak, aby sa v ňom vykreslil rok dátumu objednávky.

1. V ukážke údajov kliknite pravým tlačidlom myši na stĺpec DátumObjednávky a vyberte položku Transformovať > rok.

2. Premenovanie stĺpca OrderDate (DátumObjednávky) na Rok:

   1. Dvakrát kliknite na stĺpec OrderDate (Dátum objednávky) a zadajte Rok alebo

   2. Right-Click v stĺpci OrderDate (DátumObjednávky ), vyberte položku Rename (Premenovať) a zadajte year (Rok).

Krok 6: Zoskupenie riadkov podľa stĺpcov ProductID (IDProduktu) a Year (Rok)

1. V ukážke údajov vyberte položky Year (Rok ) a Order_Details.ProductID (IDProduktu).

2. Right-Click niektorú z hlavičiek a vyberte položku Zoskupovať podľa.

3. V dialógovom okne Zoskupenie podľa:

   1. V textovom poli Názov nového stĺpca zadajte Celkový predaj.

   2. V rozbaľovacom zozname Operácia vyberte možnosť Súčet.

   3. V rozbaľovacom zozname Stĺpec vyberte Celková hodnota pre riadok.

4. Vyberte tlačidlo OK.

   

Krok 7: Premenovanie dotazu

Pred importovaním údajov o predaji do Excelu premenujte dotaz:

• Na table Nastavenia dotazu zadajte do poľa Názovhodnotu Celkový predaj.

Výsledky: Konečný dotaz pre úlohu 2

Po vykonaní všetkých krokov získate dotaz Celkový predaj v informačnom kanáli Northwind OData.

Súhrn: Power Query kroky vytvorené v úlohe 2 

Počas vykonávania aktivít dotazu v Power Query sa vytvoria kroky dotazu a zobrazia sa na table Nastavenia dotazu v zozname Použité kroky. Každý krok dotazu obsahuje zodpovedajúci Power Query vzorec, známy aj ako jazyk M. Ďalšie informácie o Power Query vzorcoch nájdete v téme Informácie o Power Query vzorcoch.

Úloha	Krok dotazu	Vzorec
Pripojenie k informačnému kanálu OData	Zdroj	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc"; null; [Implementácia="2.0"])
Výber tabuľky	Navigácia	= Source{[Name="Orders"]}[Údaje]
Rozbalenie tabuľky Podrobnosti_objednávky	Rozbalenie tabuľky Podrobnosti_objednávky	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
Odstránenie ostatných stĺpcov, aby sa zobrazovali iba požadované stĺpce	RemovedColumns (OdstránenéStĺpce)	= Table.RemoveColumns(#"Rozbaliť Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Výpočet celkovej hodnoty pre riadok za každý riadok tabuľky Podrobnosti_objednávky	Pridané vlastné	= 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])
Zmena na zmysluplnejší názov, Lne Total	Premenované stĺpce	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Transformácia stĺpca OrderDate (DátumObjednávky) tak, aby vykreslil rok	Extrahovaný rok	= Table.TransformColumns(#"Zoskupené riadky";{{"Rok"; Dátum.Rok, Int64.Type}})
Zmeniť na  zmysluplnejšie názvy, DátumObjednávky a Rok	Premenované stĺpce 1	Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}})
Zoskupenie riadkov podľa stĺpcov ProductID (IDProduktu) a Year (Rok)	GroupedRows (ZoskupenéRiadky)	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

Power Query vám umožňuje skombinovať viaceré dotazy zlúčením alebo pripojením. Operácia Zlúčiť sa vykoná na každom dotaze Power Query s tabuľkovým tvarom, a to bez ohľadu na zdroj údajov, z ktorého tieto údaje pochádzajú. Ďalšie informácie o kombinovaní zdrojov údajov nájdete v téme Kombinovanie viacerých dotazov.

V tejto úlohe skombinujete dotazy Produkty a Celkový predaj pomocou dotazu Zlúčiť a rozbaliť a potom načítate dotaz Celkový predaj na produkt do dátového modelu Excelu.

Krok 1: Zlúčenie stĺpca ProductID (IDProduktu) s dotazom Celkový predaj

1. V excelovom zošite prejdite na dotaz Produkty na karte Hárok Produkty .

2. Vyberte bunku v dotaze a potom vyberte položku Dotaz > zlúčiť.

3. V dialógovom okne Zlúčiť vyberte ako primárnu tabuľku položku Produkty a ako sekundárny alebo súvisiaci dotaz na zlúčenie vyberte možnosť Celkový predaj . Celkový predaj sa stane novým štruktúrovaným stĺpcom s ikonou rozbalenia.

4. Ak chcete porovnať stĺpec Total Sales (Celkový predaj) so stĺpcom Products (Produkty) podľa stĺpca ProductID (IDProduktu), vyberte stĺpec ProductID (IDProduktu) z tabuľky Products (Produkty) a stĺpec Order_Details.ProductID (Podrobnosti_objednávky.IDProduktu) z tabuľky Celkový predaj.

5. V dialógovom okne Úrovne ochrany osobných údajov:

   1. Pre oba zdroje údajov vyberte ako úroveň ochrany osobných údajov možnosť Organizačné.

   2. Vyberte položku Uložiť.

6. Vyberte tlačidlo OK.

   Poznámka k zabezpečeniu:  Úrovne ochrany osobných údajov chránia používateľa pred neúmyselným kombinovaním údajov z viacerých zdrojov, ktoré môžu byť súkromné alebo organizačné. V závislosti od dotazu môže používateľ neúmyselne odoslať údaje zo súkromného zdroja do iného zdroja, ktorý môže byť škodlivý. Power Query analyzuje každý zdroj údajov a klasifikuje ho podľa definovanej úrovne ochrany osobných údajov: Verejné, Organizačné a Súkromné. Ďalšie informácie o úrovniach ochrany osobných údajov nájdete v téme Nastavenie úrovní ochrany osobných údajov.

   

Výsledok

Operácia zlúčenia vytvorí dotaz. Výsledok dotazu obsahuje všetky stĺpce z primárnej tabuľky (Produkty) a jeden štruktúrovaný stĺpec tabuľky do súvisiacej tabuľky (Celkový predaj). Výberom ikony Rozbaliť pridáte nové stĺpce do primárnej tabuľky zo sekundárnej alebo súvisiacej tabuľky.

Krok 2: Rozbalenie zlúčeného stĺpca

V tomto kroku rozbalíte zlúčený stĺpec s názvom NovýStĺpec a vytvoríte dva nové stĺpce v dotaze Produkty : Rok a Celkový predaj.

1. V ukážke údajov vyberte ikonu Rozbaliť () vedľa položky NovýStĺpc.

2. V rozbaľovacom zozname Rozbaliť :

   1. Výberom položky (Vybrať všetky stĺpce) vymažete všetky stĺpce.

   2. Vyberte položky Rok a Celkový predaj.

   3. Vyberte tlačidlo OK.

3. Premenujte tieto dva stĺpce na Rok a Celkový predaj.

4. Ak chcete zistiť, ktoré produkty a v ktorých rokoch získali produkty najvyšší objem predaja, vyberte položku Zoradiť zostupne podľa celkového predaja.

5. Premenujte dotaz na Celkový predaj podľa produktu.

Výsledok

Krok 3: Načítanie dotazu Celkový predaj za produkt do dátového modelu Excelu

V tomto kroku načítate dotaz do dátového modelu Excelu s cieľom vytvoriť zostavu pripojenú k výsledku dotazu. Po načítaní údajov do dátového modelu Excelu môžete použiť power pivot na ďalšiu analýzu údajov.

1. Vyberte položku Domov > Zavrieť & načítať.

2. V dialógovom okne Import údajov vyberte položku Pridať tieto údaje do dátového modelu. Ďalšie informácie o používaní tohto dialógového okna získate výberom otáznika (?).

Výsledok

Máte dotaz Celkový predaj na produkt , ktorý kombinuje údaje zo súboru Products.xlsx a informačného kanála OData spoločnosti Northwind. Tento dotaz sa použije na model doplnku Power Pivot. Okrem toho zmeny v dotaze upravujú a obnovujú výslednú tabuľku v dátovom modeli.

Súhrn: Power Query kroky vytvorené v úlohe 3

Pri vykonávaní činností zlúčenia dotazov v Power Query sa vytvoria kroky dotazu a zobrazia sa na table Nastavenia dotazu v zozname Použité kroky. Každý krok dotazu obsahuje zodpovedajúci Power Query vzorec, známy aj ako jazyk M. Ďalšie informácie o Power Query vzorcoch nájdete v téme Informácie o Power Query vzorcoch.

Úloha	Krok dotazu	Vzorec
Zlúčenie stĺpca ProductID (IDProduktu) s dotazom Celkový predaj	Zdroj (zdroj údajov pre operáciu Zlúčiť)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Rozbalenie zlúčeného stĺpca	Rozbalený celkový predaj	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Premenovanie dvoch stĺpcov	Premenované stĺpce	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Zoradiť celkový predaj vo vzostupnom poradí	Zoradené riadky	= Table.Sort(#"Premenované stĺpce",{{"Celkový predaj"; Order.Ascending}})