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

V tomto kurze môžete pomocou editora dotazov doplnku Power Query importovať údaje 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 skombinuje údaje z oboch zdrojov a vygeneruje sa zostava 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, povýšiť riadky na hlavičky stĺpcov, odstrániť niektoré stĺpce a načítať dotaz do hárka.

Krok 1: Pripojenie k excelovému zošitu

  1. Otvorte excelový zošit.

  2. Vyberte položku> Get Data > From File > From Workbook (Získať > údaje zo zošita).

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

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

Krok 2: Kontrola krokov dotazu

Power Query predvolene automaticky pridá niekoľko krokov ako úsudok. Ď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 sa vytvoril po importovaní zošita.

  2. Kliknite pravým tlačidlom myši na krok Navigácia a vyberte položku Upraviť nastavenia. Tento krok sa vytvoril po výbere tabuľky 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 jednotlivých stĺpcov. Výberom šípky nadol napravo od riadku 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 údajovvyberte stĺpce ProductID (ID Produktu),ProductName (NázovProduktu), CategoryID (IDKategórie)a QuantityPerUnit (MnožstvoNaJednotku) (použite kombináciu klávesov Ctrl + kliknutie alebo Shift + kliknutie).

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

    Skrytie ostatných stĺpcov

Krok 4: Načítanie dotazu produktov

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: Kroky doplnku Power Query vytvorené v úlohe 1

Pri vykonávaní činností dotazov v Doplnku Power Query sa vytvoria kroky dotazu, ktoré sú uvedené na table Nastavenia dotazu v zozname Použité kroky. Každý krok dotazu obsahuje príslušný vzorec Power Query, známy aj ako 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 v stĺpcoch

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 Northwind OData na lokalite http://services.odata.org/Northwind/Northwind.svc,rozbaľte tabuľku Order_Details, odstráňte stĺpce, vypočítajte súčet riadkov, transformujte dátumObjednávky, zoskupte riadky podľa stĺpcov ProductID a Year, premenujte dotaz a zakážte sťahovanie dotazov do excelového zošita.

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

  1. Vyberte položku > 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 )kombinovajús riadkami s primárnou tabuľkou (Objednávky).

V doplnku Power Query stĺpec obsahujúci súvisiacu tabuľku obsahuje v bunke hodnotu Záznamalebo Tabuľka. Tieto stĺpce sa nazývajú štruktúrované stĺpce. Record (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 "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 priradenou cudzím kľúčom v vzťahu informačného kanála OData alebo cudzieho kľúča v databáze SQL Servera.

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 zobrazení Ukážkaúdajov sa posuňte vodorovne na Order_Details stĺpca.

  2. V Order_Details vyberte ikonu rozbalenie ( Rozbaliť ).

  3. V rozbaľovacom zozname Rozbaliť:

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

    2. Vyberte položku ProductID (IDProduktu), UnitPrice (JednotkováCena)a Quantity (Množstvo).

    3. Vyberte tlačidlo OK.

      Rozbalenie prepojenia na tabuľku Podrobnosti_objednávky

      Poznámka: V doplnku Power Query môžete rozbaliť prepojené tabuľky zo stĺpca a agregovať stĺpce prepojenej tabuľky ešte pred rozbalím údajov v predmetnej tabuľke. Ď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 tieto stĺpce: 

    1. Vyberte prvý stĺpec, OrderID (ID Objednávky).

    2. Shift + kliknutie na posledný stĺpec, Špeder.

    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 hlavičku vybratého 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 ( Ikona 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 Vzorec vlastného stĺpca text [Order_Details.JednotkováCena] * [Order_Details.Množstvo].

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

  5. Vyberte tlačidlo OK.

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

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 údajovkliknite pravým tlačidlom myši na stĺpec OrderDate (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 DátumObjednávky vyberte položku Premenovaťa zadajte Rok.

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

  1. V ukážke údajovvyberte položku Year(Rok) a Order_Details.ProductID (ID Produktu).

  2. Right-Click z hlavičiek a vyberte položku Zoskupiť 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.

    Dialógové okno Zoskupenie podľa pre operácie agregácie

Krok 7: Premenovanie dotazu

Pred importom údajov o predaji do Excelu premenujte dotaz:

  • Na table Nastavenia dotazu zadajte do poľa Názov položku Celkový predaj.

Výsledky: Záverečný dotaz pre úlohu 2

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

Celkový predaj

Súhrn: Kroky doplnku Power Query vytvorené v úlohe 2 

Pri vykonávaní činností dotazov v Doplnku Power Query sa vytvoria kroky dotazu, ktoré sú uvedené na table Nastavenia dotazu v zozname Použité kroky. Každý krok dotazu obsahuje príslušný vzorec Power Query, známy aj ako 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; [Implementácia="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

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(#"Grouped Rows",{{"Year", Date.Year, 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 skombinujte dotazy Produkty a Celkový predaj pomocou dotazu Zlúčiť a Rozbaliť 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

  1. V excelovom zošite prejdite na dotaz Produkty na karte hárka Produkty.

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

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

  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.

    Dialógové okno Zlúčenie

Výsledok

Operácia Zlúčiť vytvorí dotaz. Výsledok dotazu obsahuje všetky stĺpce z primárnej tabuľky (Produkty)a jeden štruktúrovaný stĺpec tabuľky so súvisiacou tabuľkou ( Celkovýpredaj). Ak chcete do primárnej tabuľky pridať nové stĺpce zo sekundárnej alebo súvisiacej tabuľky, vyberte ikonu Rozbaliť.

Finálne zlúčenie

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

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

  1. V ukážke údajovvyberte položku Rozbaliť ikonu ( Rozbaliť ) vedľa položky NewColumn.

  2. V rozbaľovacom zozname Rozbaliť:

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

    2. Vyberte položky Year (Rok)a Total Sales (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 majú produkty najväčší objem predaja, vyberte položku Zoradiť zostupne podľa celkového predaja.

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

Výsledok

Rozbalenie prepojenia tabuľky

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

V tomto kroku načítate dotaz do excelového dátového modelus cieľom vytvoriť zostavu pripojenú k výsledku dotazu. Po načítaní údajov do excelového dátového modelumôžete pomocou doplnku Power Pivot pokračovať v analýze údajov.

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

  2. V dialógovom okne Import údajov začiarknite políčko 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ázniku (?).

Výsledok

Máte dotaz Celkový predaj za produkt, ktorý kombinuje údaje z údajov z Products.xlsx a informačného kanála Northwind OData. Tento dotaz sa použije na model doplnku Power Pivot. Okrem toho zmeny dotazu upravia a obnovia výslednú tabuľku v dátovom modeli.

Súhrn: Kroky doplnku Power Query vytvorené v úlohe 3

Pri vykonávaní činností dotazov Zlúčiť v Doplnku Power Query sa vytvoria kroky dotazu, ktoré sú uvedené na table Nastavenia dotazu v zozname Použité kroky. Každý krok dotazu obsahuje príslušný vzorec Power Query, známy aj ako 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

Expanded Total Sales

= 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(#"Renamed Columns",{{"Total Sales", Order.Ascending}})

Pozrite tiež

Pomocník doplnku Power Query for Excel

Potrebujete ďalšiu pomoc?

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×