Naučte se kombinovat více zdrojů dat (Power Query)

V tomto kurzu můžete pomocí Editoru dotazů Power Query importovat data z místního excelového souboru, který obsahuje informace o produktu, a z datového kanálu OData, který obsahuje informace o objednávce produktu. Provedete kroky transformace a agregace a zkombinujte data z obou zdrojů a vyrobíte sestavu "Celkové prodeje za produkt a rok".   

K provedení tohoto kurzu potřebujete sešit Produkty. V dialogovém okně Uložit jako soubor pojmenujte Výrobky a objednávky.xlsx.

V tomto úkolu importujete produkty ze souboru Products and Orders.xlsx (stažený a přejmenovaný výše) do excelového sešitu, povýšíte řádky na záhlaví sloupců, odeberete některé sloupce a načtete dotaz do listu.

Krok 1: Připojení k excelovému sešitu

  1. Vytvořte excelový sešit.

  2. Vyberte Data > Data > ze souboru >ze sešitu.

  3. V dialogovém okně Importovat data vyhledejte a vyhledejte stažený Products.xlsx a pak vyberte Otevřít.

  4. V podokně Navigátor poklikejte na tabulku Produkty. Zobrazí se Editor Power Query.

Krok 2: Kontrola kroků dotazu

Ve výchozím nastavení Power Query automaticky přidá několik kroků, aby vám to bylo co nejlepší. Další informace najdete v části Použité kroky v podokně Nastavení dotazu.

  1. Klikněte pravým tlačítkem myši na krok Zdroj a vyberte Upravit nastavení. Tento krok byl vytvořen při importu sešitu.

  2. Klikněte pravým tlačítkem myši na krok Navigace a vyberte Upravit nastavení. Tento krok byl vytvořen, když jste v dialogovém okně Navigace vybrali tabulku.

  3. Klikněte pravým tlačítkem myši na krok Změněný typ a vyberte Upravit nastavení. Tento krok vytvořil Power Query, který odvodil datové typy jednotlivých sloupců. Úplný vzorec zobrazíte tak, že vyberete šipku dolů napravo od řádku vzorců.

Krok 3: Odebrání dalších sloupců tak, aby se zobrazovaly jen potřebné sloupce

V tomto kroku odeberete všechny sloupce kromě IDProduktu, NázevProduktu, IDKategorieMnožstvíNaJednotku.

  1. V náhledu datvyberte sloupce IdVýrobku , NázevVýrobku , IdKategoriea MnožstvíSoučátka (použijte Ctrl+Kliknutí nebo Shift+Kliknutí).

  2. Vyberte Odebrat sloupce > Odebrat další sloupce.

    Skrytí ostatních sloupců

Krok 4: Načtení dotazu na produkty

V tomto kroku načtete dotaz Produkty do excelového listu.

  • Vyberte Domů > Zavřít & Načíst. Dotaz se zobrazí v novém excelovém listu.

Shrnutí: Kroky Power Query vytvořené v úkolu 1

Při provádění aktivit dotazů v Power Query se kroky dotazu vytvoří a zobrazí v podokně Nastavení dotazu v seznamu Použité kroky. Každý krok dotazu má odpovídající vzorec Power Query známý taky jako jazyk „M“. Další informace o vzorcích Power Query najdete v tématu Vytvoření vzorců Power Query v Excelu.

Úkol

Krok dotazu

Vzorec

Import excelového sešitu

Zdrojová měna

= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)

Vyberte tabulku Produkty.

Navigace:

= Zdroj{[Položka="Produkty";Druh="Tabulka"]}[Data]

Power Query automaticky rozpozná datové typy sloupců.

Změněný 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}})

Odebrání ostatních sloupců tak, aby se zobrazovaly jenom potřebné sloupce

Odebrání dalších sloupců

= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

V tomto úkolu importujete data do excelového sešitu z ukázkového datového kanálu Northwind OData v http://services.odata.org/Northwind/Northwind.svc,rozbalíte tabulku Order_Details, odeberete sloupce, vypočítáte součet řádků, transformujete datumobjednávky, seskupíte řádky podle id Výrobku a roku, přejmenujete dotaz a zakážete stahování dotazu do excelového sešitu.

Krok 1: Připojení k datovému kanálu OData

  1. Vyberte Data > Data> z jiných zdrojů > z datového kanálu OData.

  2. V dialogu Datový kanál OData zadejte Adresu URL datového kanálu Northwind OData.

  3. Vyberte OK.

  4. V podokně Navigátor poklikejte na tabulku Objednávky.

Krok 2: Rozšíření tabulky Podrobnosti_objednávky

V tomto kroku rozšíříte tabulku Podrobnosti_objednávky související s tabulkou Objednávky a zkombinujete sloupce IDProduktu, JednotkováCenaMnožství z tabulky Podrobnosti_objednávky do tabulky Objednávky. Operace rozšíření kombinuje sloupce ze související tabulky do tabulky předmětu. Při spuštění dotazu se řádky ze související tabulky (Order_Details) zkombinují do řádků s primární tabulkou (Objednávky).

V Power Query má sloupec obsahující související tabulku v buňce hodnotu Záznam nebo Tabulka. Tyto sloupce se nazývají strukturované sloupce. Záznam označuje jeden související záznam a představuje relaci 1:1 s aktuálními daty nebo primární tabulkou. Tabulka označuje související tabulku a představuje relaci 1:N s aktuální nebo primární tabulkou. Strukturovaný sloupec představuje relaci ve zdroji dat, který má relační model. Strukturovaný sloupec například označuje entitu s přidružením cizího klíče v datovém kanálu OData nebo relaci cizího klíče v databázi SQL Serveru.

Po rozšíření tabulky Podrobnosti_objednávky se do tabulky Objednávky přidají tři nové sloupce a další řádky, pokaždé jeden za každý řádek ve vnořené nebo související tabulce.

  1. V náhledudat se vodorovně posuňte k Order_Details sloupce.

  2. Ve sloupci Order_Details vyberte ikonu rozbalení ( Rozbalení ).

  3. V rozevíracím seznamu Rozšířit:

    1. Výběrem možnosti (Vybrat všechny sloupce) vymažete všechny sloupce.

    2. Vyberte Id Výrobku, JednotkováCenaa Množství.

    3. Vyberte OK.

      Odkaz pro rozšíření tabulky Podrobnosti_objednávky

      Poznámka: V Power Query můžete před rozbalením dat v tabulce předmětu rozbalit tabulky propojené ze sloupce a agregovat sloupce propojené tabulky. Další informace o provádění agregačních operací najdete v tématu Agregace dat ze sloupce.

Krok 3: Odebrání dalších sloupců tak, aby se zobrazovaly jen potřebné sloupce

V tomto kroku odeberete všechny sloupce kromě DatumObjednávky, IDProduktu, JednotkováCenaMnožství

  1. V náhledudat vyberte následující sloupce: 

    1. Vyberte první sloupec– Id Objednávky.

    2. Shift+Click the last column, Shipper.

    3. Podržte klávesu Ctrl a klikněte na sloupce Datum_objednávky, Podrobnosti_objednávky.IDProduktu, Podrobnosti_objednávky.JednotkováCenaPodrobnosti_objednávky.Množství.

  2. Klikněte pravým tlačítkem myši na záhlaví vybraného sloupce a vyberte Odebrat další sloupce.

Krok 4: Výpočet součtů pro každý řádek tabulky Podrobnosti_objednávky

V tomto kroku vytvoříte vlastní sloupec k výpočtu součtu řádku pro každý řádek tabulky Podrobnosti_objednávky.

  1. V náhledudat vyberte ikonu tabulky ( Ikona tabulky ) v levém horním rohu náhledu.

  2. Klikněte na Přidat vlastní sloupec.

  3. V dialogovém okně Vlastní sloupec zadejte do pole Vlastní sloupec vzorec[Order_Details.JednotkováCena] * [Order_Details.Množství].

  4. Do pole Nový název sloupce zadejte Součet řádků.

  5. Vyberte OK.

Výpočet součtů pro každý řádek tabulky Podrobnosti_objednávky

Krok 5: Transformace sloupce roku DatumObjednávky

V tomto kroku transformujete sloupec DatumObjednávky tak, aby zobrazoval rok data objednávky.

  1. V náhledu datklikněte pravým tlačítkem na sloupec DatumObjednávky a vyberte Transformovat > rok.

  2. Přejmenujte sloupec DatumObjednávky na Rok:

    1. dvakrát klikněte na sloupec DatumObjednávky a zadejte Rok nebo

    2. Right-Click ve sloupci DatumObjednávky vyberte Přejmenovat azadejte Rok.

Krok 6: Seskupení řádků podle IDProduktu a Roku

  1. V náhledu datvyberte Roka Order_Details.ID Produktu.

  2. Right-Click záhlaví a vyberte Seskupit podle.

  3. V dialogu Seskupit podle:

    1. Do textového pole Nový název sloupce zadejte Celkové prodeje.

    2. V rozevíracím seznamu Operace vyberte Součet.

    3. V rozevíracím seznamu Sloupec vyberte Součet za řádek.

  4. Vyberte OK.

    Dialog Seskupit podle pro agregační operace

Krok 7: Přejmenování dotazu

Před importem dat o prodeji do Excelu přejmenujte dotaz:

  • V podokně Nastavení dotazu zadejte do pole Název celkový prodej.

Výsledky: Konečný dotaz pro úkol 2

Po provedení všech kroků budete mít dotaz Celkové prodeje pro datový kanál Northwind OData.

Celkové prodeje

Shrnutí: Kroky Power Query vytvořené v úkolu 2 

Při provádění aktivit dotazů v Power Query se kroky dotazu vytvoří a zobrazí v podokně Nastavení dotazu v seznamu Použité kroky. Každý krok dotazu má odpovídající vzorec Power Query známý taky jako jazyk „M“. Další informace o vzorcích Power Query najdete v tématu Informace o vzorcích Power Query.

Úkol

Krok dotazu

Vzorec

Připojení k datovému kanálu OData

Zdroj

= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])

Výběr tabulky

Navigace

= Zdroj{[Název="Objednávky"]}[Data]

Rozšíření tabulky Podrobnosti_objednávky

Rozšíření tabulky Podrobnosti_objednávky

= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

Odebrání ostatních sloupců tak, aby se zobrazovaly jenom potřebné sloupce

RemovedColumns

= Table.RemoveColumns(#"Expand Order_Details",{"IdObjednavky", "IdZaměstnanec", "IdZaměstnanec", "RequiredDate", "Datum Odeslání", "ShipVia", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})

Výpočet součtů pro každý řádek tabulky Podrobnosti_objednávky

Přidáno 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])

Změna na smysluplnější název, Součet Lne

Přejmenované sloupce

= Table.RenameColumns(InsertedCustom,{{"Custom"; "Line Total"}})

Transformace sloupce DatumObjednávky na zobrazení roku

Extrahovaný rok

= Table.TransformColumns(#"Seskupené řádky";{{"Year", Date.Year, Int64.Type}})

Změnit na 

smysluplnější názvy, DatumObjednávky a Rok

Přejmenované sloupce 1

Table.RenameColumns

(TransformedColumn,{{"DatumObjednávky", "Rok"}})

Seskupení řádků podle IDProduktu a Roku

GroupedRows

= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

Power Query vám umožňuje zkombinovat několik dotazů prostřednictvím jejich sloučení nebo připojení. Operace sloučení se provádí u jakéhokoli dotazu Power Query, který má formát tabulky, bez ohledu na zdroj dat, ze kterého data pochází. Další informace o kombinování zdrojů dat najdete v tématu Kombinování víc dotazů.

V tomto úkolu zkombinujte dotazy Produkty a Celkové prodeje pomocí slučovacího dotazu a operace rozbalení a potom načtěte dotaz Celkový prodej za produkt do datového modelu Excelu.

Krok 1: Sloučení IDProduktu a dotazu Celkové prodeje

  1. V excelovém sešitu přejděte na dotaz Produkty na kartě List Produkty.

  2. Vyberte buňku v dotazu a potom vyberte Dotaz a > sloučit.

  3. V dialogovém okně Sloučit vyberte Produkty jako primární tabulku a jako sekundární nebo související dotaz vyberte Celkový prodej, který chcete sloučit. Celkový prodej se stane novým strukturovaným sloupcem s ikonou rozbalení.

  4. Aby Celkové prodeje odpovídaly Produktům podle IDProduktu, vyberte sloupec IDProduktu z tabulky Produkty a sloupec Podrobnosti_objednávky.IDProduktu z tabulky Celkové prodeje.

  5. V dialogu Úrovně ochrany osobních údajů:

    1. Pro oba zdroje dat vyberte jako úroveň izolace osobních údajů hodnotu Organizační.

    2. Vyberte Uložit.

  6. Vyberte OK.

    Poznámka zabezpečení: Úrovně ochrany osobních údajů brání uživateli neúmyslně kombinovat data z víc zdrojů dat, které by mohly být soukromé nebo firemní. V závislosti na dotazu by uživatel mohl neúmyslně odesílat data ze soukromého zdroje dat jinému zdroji dat, který by mohl být škodlivý. Power Query analyzuje každý zdroj dat a klasifikuje ho do definované úrovně ochrany osobních údajů: Veřejné, organizační a soukromé. Další informace o úrovních ochrany osobních údajů najdete v tématu Nastavení úrovní ochrany osobních údajů.

    Dialog Sloučit

Výsledek

Operace sloučení vytvoří dotaz. Výsledek dotazu obsahuje všechny sloupce z primární tabulky (Produkty ) a jeden sloupec s strukturou tabulky až po související tabulku ( Celkovéprodeje). Vyberte ikonu Rozbalit a přidejte nové sloupce do primární tabulky ze sekundární nebo související tabulky.

Výsledné sloučení

Krok 2: Rozbalení sloučeného sloupce

V tomto kroku rozbalíte sloučený sloupec s názvem NovýSloupec a v dotazu Produkty vytvoříte dva nové sloupce:Rok a Celkové prodeje .

  1. V náhledudat vyberte Rozbalit ikonu ( Rozbalení ) vedle NewColumn.

  2. V rozevíracím seznamu Rozbalit:

    1. Výběrem možnosti (Vybrat všechny sloupce) vymažete všechny sloupce.

    2. Vyberte Rok aCelkové prodeje.

    3. Vyberte OK.

  3. Přejmenujte tyto dva sloupce na RokCelkové prodeje.

  4. Pokud chcete zjistit, které produkty a v jakých letech získaly produkty nejvyšší objem prodeje, vyberte Seřadit sestupně podle celkových prodejů.

  5. Přejmenujte dotaz na Celkové prodeje za produkt.

Výsledek

Rozbalení odkazu na tabulku

Krok 3: Načtení dotazu Celkové prodeje za produkt do datového modelu Excelu

V tomto kroku načítáte dotaz do datového modelu Excelua sestavíte sestavu připojenou k výsledku dotazu. Po načtení dat do datového modelu Excelumůžete k další analýze dat použít Power Pivot.

  1. Vyberte Domů > Zavřít & Načíst.

  2. V dialogovém okně Importovat data vyberte Přidat tato data do datového modelu. Další informace o použití tohoto dialogového okna získáte tak, že vyberete otazník (?).

Výsledek

Máte dotaz Celkový prodej za produkt, který kombinuje data ze souboru Products.xlsx a datového kanálu Northwind OData. Tento dotaz se použije u modelu Power Pivotu. Změny dotazu navíc změní a aktualizují výslednou tabulku v datovém modelu.

Shrnutí: Kroky Power Query vytvořené v úkolu 3

Při provádění aktivit slučovacích dotazů v Power Query se kroky dotazu vytvoří a zobrazí v podokně Nastavení dotazu v seznamu Použité kroky. Každý krok dotazu má odpovídající vzorec Power Query známý taky jako jazyk „M“. Další informace o vzorcích Power Query najdete v tématu Informace o vzorcích Power Query.

Úkol

Krok dotazu

Vzorec

Sloučení IDProduktu a dotazu Celkové prodeje

Zdroj (zdroj dat pro operaci sloučení)

= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)

Rozšíření sloučeného sloupce

Rozbalené celkové prodeje

= Table.ExpandTableColumn(Zdroj, "Celkové prodeje", {"Rok", "Celkové prodeje"}, {"Celkové prodeje.rok", "Celkové prodeje.celkové prodeje"})

Přejmenování dvou sloupců

Přejmenované sloupce

= Table.RenameColumns(#"Expanded Total Sales";{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})

Seřazení celkových prodejů vzestupně

Seřazené řádky

= Table.Sort(#"Renamed Columns";{{"Total Sales", Order.Ascending}})

Viz také

Nápověda k Power Query pro Excel

Potřebujete další pomoc?

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor! Pravděpodobně bude užitečné, když vás spojíme s některým z našich agentů podpory Office.

×