V tomto kurze môžete použiť Editor Power Query Power Query na import údajov z lokálneho excelového súboru, ktorý obsahuje informácie o produkte, a z informačného kanála OData obsahujúceho informácie o objednávke produktu. Vykonáte kroky transformácie a agregácie a skombinujete údaje z oboch zdrojov, čím vytvoríte zostavu Celkový predaj na produkt a rok.
Na vykonanie tohto kurzu potrebujete zošit Produkty . V dialógovom okne Uložiť ako pomenujte súbor Produkty a objednávky.xlsx.
Úloha 1: Importovanie produktov do excelového zošita
V tejto úlohe importujete produkty zo súboru Produkty a Orders.xlsx (stiahnutého a premenovaného 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
- Otvorte excelový zošit.
- Vyberte položku Získať>údaje>zo súboru>zo zošita.
- V dialógovom okne Import údajov vyhľadajte a vyhľadajte Products.xlsx súbor, ktorý ste stiahli, a potom vyberte položku Otvoriť.
- Na table Navigátor dvakrát kliknite na tabuľku Produkty . Zobrazí sa Editor Power Query.
Krok 2: Preskúmanie krokov dotazu
Power Query pre vaše pohodlie predvolene automaticky pridá niekoľko krokov. Ďalšie informácie získate v časti Použité kroky na table Nastavenia dotazu v časti Použité kroky.
- Kliknite pravým tlačidlom myši na krok Zdroj a vyberte položku Upraviť nastavenia. Tento krok sa vytvoril pri importovaní zošita.
- Kliknite pravým tlačidlom myši na krok navigácie a vyberte položku Upraviť nastavenia. Tento krok sa vytvoril po výbere tabuľky z dialógového okna Navigácia .
- 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. Vyberte šípku nadol napravo od riadka vzorcov a zobrazte celý 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).
- V zobrazení Ukážka údajov vyberte stĺpce IDProduktu, NázovProduktu, IDkategórie a MnožstvoNaJednotku (použite kombináciu klávesov Ctrl + kliknutie alebo Shift + kliknutie).
- Vyberte položku Odstrániť stĺpce>Odstrániť ostatné stĺpce.
Krok 4: Načítanie dotazu produktov
V tomto kroku načítate dotaz Produkty do excelového hárka.
- Vyberte položky Domov,>Zavrieť & Načítať. Dotaz sa zobrazí v novom excelovom hárku.
Súhrn: Kroky Power Query vytvorené v Úlohe 1
Počas vykonávania činností dotazov v Power Query sa vytvoria kroky dotazov a začlenia sa do tably Nastavenia dotazu v zozname Použité kroky. Každému kroku dotazu zodpovedá vzorec Power Query, nazývaný tiež jazyk M. Ďalšie informácie o vzorcoch Power Query nájdete v téme Vytváranie vzorcov Power Query v Exceli.
| Úloha | Krok dotazu | Vzorec |
|---|---|---|
| Importovanie 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"]}[Data] |
| Power Query automaticky zisťuje typy údajov stĺpca | 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}, {"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"}) |
Úloha 2: Importovanie údajov objednávky z informačného kanála OData
V tejto úlohe importujete údaje do excelového zošita zo vzorového informačného kanála OData Northwind na lokalite http://services.odata.org/Northwind/Northwind.svc, rozbalíte Order_Details tabuľku, odstránite stĺpce, vypočítate celkovú hodnotu v riadku, transformujete dátumObjednávky, zoskupíte riadky podľa stĺpcov ProductID (IDProduktu) a Year (Rok), premenujete dotaz a zakážete stiahnutie dotazu do excelového zošita.
Krok 1: Pripojenie k informačnému kanálu OData
- Vyberte položku Údaje>Získať údaje>z iných zdrojov>z informačného kanála OData.
- V dialógovom okne Informačný kanál OData zadajte URL informačného kanála Northwind OData.
- Vyberte tlačidlo OK.
- Na table Navigátor dvakrát kliknite na tabuľku Objednávky .
Krok 2: Rozbalenie Order_Details tabuľky
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 predstavuje vzť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 typu "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 Order_Details sa do tabuľky Objednávky pridajú tri nové stĺpce a ďalšie riadky, každý pre každý riadok vnorenej alebo súvisiacej tabuľky.
V zobrazení Ukážka údajov sa posuňte vodorovným smerom na stĺpec Order_Details .
V stĺpci Order_Details vyberte ikonu rozbalenia (
).V rozbaľovacom zozname Rozbaliť:
Výberom položky (Vybrať všetky stĺpce) vymažete všetky stĺpce.
Vyberte položky IDProduktu, JednotkováCena a Množstvo.
Vyberte tlačidlo OK.
Poznámka
V doplnku Power Query môžete rozbaliť tabuľky prepojené zo stĺpca a pred rozbalením údajov v objektovej tabuľke agregovať stĺpce prepojenej 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).
V ukážke údajov vyberte nasledujúce stĺpce:
- Vyberte prvý stĺpec OrderID.
- Shift + kliknúť na posledný stĺpec Špeditér.
- 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).
Kliknite pravým tlačidlom myši na hlavičku vybratého stĺpca a vyberte položku Odstrániť ostatné stĺpce.
Krok 4: Výpočet celkovej hodnoty pre riadok pre každý riadok Order_Details
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.
- V ukážke údajov vyberte ikonu tabuľky (
) v ľavom hornom rohu ukážky. - Kliknite na položku Pridať vlastný stĺpec.
- V dialógovom okne Vlastný stĺpec zadajte do poľa vzorca Vlastný stĺpechodnotu [Order_Details.JednotkováCena] * [Order_Details.Množstvo].
- Do poľa Názov nového stĺpca zadajte Celkovú hodnotu riadka.
- 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.
V ukážke údajov kliknite pravým tlačidlom myši na stĺpec OrderDate (DátumObjednávky ) a vyberte položku Transformovať>rok.
Premenovanie stĺpca OrderDate (DátumObjednávky) na Rok:
- Dvakrát kliknite na stĺpec OrderDate (Dátum objednávky) a zadajte Rok alebo
- Right-Click v stĺpci DátumObjednávky vyberte položku Premenovať a zadajte rok.
Krok 6: Zoskupenie riadkov podľa stĺpcov ProductID (IDProduktu) a Year (Rok)
V ukážke údajov vyberte položky Year (Rok) a Order_Details.ProductID.
Right-Click jednu z hlavičiek a vyberte položku Zoskupiť podľa.
V dialógovom okne Zoskupenie podľa:
- V textovom poli Názov nového stĺpca zadajte Celkový predaj.
- V rozbaľovacom zozname Operácia vyberte možnosť Súčet.
- V rozbaľovacom zozname Stĺpec vyberte Celková hodnota pre riadok.
Vyberte tlačidlo OK.
Pred importovaním údajov o predaji do Excelu premenujte dotaz:
- Na table Nastavenia dotazu zadajte do poľa Názov reťazec Celkový predaj.
Výsledky: Finálny dotaz pre úlohu 2
Po vykonaní všetkých krokov získate dotaz Celkový predaj v informačnom kanáli Northwind OData.
Súhrn: Kroky Power Query vytvorené v úlohe 2
Počas vykonávania činností dotazov v Power Query sa vytvoria kroky dotazov a začlenia sa do tably Nastavenia dotazu v zozname Použité kroky. Každému kroku dotazu zodpovedá vzorec Power Query, nazývaný tiež jazyk M. Ďalšie informácie o vzorcoch Power Query nájdete v téme Informácie o vzorcoch Power Query.
| Úloha | Krok dotazu | Vzorec |
|---|---|---|
| Pripojenie k informačnému kanálu OData | Zdroj | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"]) |
| Výber tabuľky | Navigácia | = Source{[Name="Orders"]}[Data] |
| 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(#"Expand 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 | Pridanie vlastných |
= 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 výstižnejší 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",{{"Year", Date.Year, Int64.Type}}) |
| Zmeniť na výstižnejšie názvy, OrderDate (DátumObjednávky) a Year (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}}) |
Úloha 3: Kombinovanie dotazov Produkty a Celkový predaj
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 operácie zlúčenia a rozbalenia a potom načítate dotaz Celkový predaj za produkt do dátového modelu Excelu.
Krok 1: Zlúčenie stĺpca ProductID (IDProduktu) s dotazom Celkový predaj
V excelovom zošite prejdite na dotaz Produkty na karte hárka Produkty .
Vyberte bunku v dotaze a potom vyberte položkuZlúčiťdotaz.>
V dialógovom okne Zlúčenie vyberte ako primárnu tabuľku položku Produkty a ako sekundárny alebo súvisiaci dotaz na zlúčenie položku Celkový predaj . Z celkového predaja sa stane nový štruktúrovaný stĺpec s ikonou rozbalenia.
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.
V dialógovom okne Úrovne ochrany osobných údajov:
- Pre oba zdroje údajov vyberte ako úroveň ochrany osobných údajov možnosť Organizačné.
- Vyberte položku Uložiť.
Vyberte tlačidlo OK.
Poznámka
Ú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). Ak chcete do primárnej tabuľky pridať nové stĺpce zo sekundárnej alebo súvisiacej tabuľky, vyberte ikonu Rozbaliť .
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.
V ukážke údajov vyberte ikonu rozbalenia (
) vedľa položky NovýStĺpec.V rozbaľovacom zozname:
- Výberom položky (Vybrať všetky stĺpce) vymažete všetky stĺpce.
- Vyberte položky Rok a Celkový predaj.
- Vyberte tlačidlo OK.
Premenujte tieto dva stĺpce na Rok a Celkový predaj.
Ak chcete zistiť, ktoré produkty a v ktorých rokoch vykázali najväčší objem predaja, vyberte položku Zoradiť zostupne podľa celkového predaja.
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 prepojenú s výsledkom dotazu. Po načítaní údajov do excelového dátového modelu môžete použiť Power Pivot na ďalšiu analýzu údajov.
- Vyberte položky Domov,>Zavrieť & Načítať.
- 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 za produkt , v ktorom sú kombinované údaje zo súboru Products.xlsx a informačného kanála Northwind OData. Tento dotaz sa použije na model doplnku Power Pivot. Okrem toho sa zmenami dotazu upraví a obnoví aj výsledná tabuľka v dátovom modeli.
Súhrn: Kroky Power Query vytvorené v Úlohe 3
Počas vykonávania činností zlúčeného dotazu v Power Query sa vytvoria kroky dotazov a začlenia sa do tably Nastavenia dotazu v zozname Použité kroky. Každému kroku dotazu zodpovedá vzorec Power Query, nazývaný tiež jazyk M. Ďalšie informácie o vzorcoch Power Query nájdete v téme Informácie o vzorcoch Power Query.
| Ú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 | Rozšírený 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"}}) |
| Zoradenie celkového predaja vo vzostupnom poradí | Zoradené riadky | = Table.Sort(#"Premenované stĺpce",{{"Celkový predaj", Order.Ascending}}) |