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, ktorý obsahuje informácie o objednávke produktu. Vykonáte kroky transformácie a agregácie a skombinujete údaje z oboch zdrojov a 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.
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
-
Otvorte excelový zošit.
-
Vyberte položku Údaje > 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 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 .
-
Kliknite pravým tlačidlom myši na krok Zdroj a vyberte položku Upraviť nastavenia. Tento krok bol vytvorený pri importovaní zošita.
-
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 .
-
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).
-
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).
-
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
-
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 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.
-
V ukážke údajov sa posuňte vodorovne 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 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).
-
V ukážkeúdajov vyberte nasledujúce stĺpce:
-
Vyberte prvý stĺpec OrderID.
-
Shift + Kliknutie 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 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.
-
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 Vzorce vlastného stĺpca[Order_Details.JednotkováCena] * [Order_Details.Quantity].
-
Do poľa Názov nového stĺpca zadajte súčet riadkov.
-
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 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 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)
-
V ukážke údajov vyberte položky Year (Rok ) a Order_Details.ProductID (IDProduktu).
-
Right-Click niektorú z hlavičiek a vyberte položku Zoskupovať 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.
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 |
(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
-
V excelovom zošite prejdite na dotaz Produkty na karte Hárok Produkty .
-
Vyberte bunku v dotaze a potom vyberte položku Dotaz > zlúčiť.
-
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.
-
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 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.
-
V ukážke údajov vyberte ikonu Rozbaliť () vedľa položky NovýStĺpc.
-
V rozbaľovacom zozname Rozbaliť :
-
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 získali produkty najvyšší 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 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.
-
Vyberte položku 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 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}}) |