Kombinování dat z různých zdrojů dat (Power Query)

Důležité :  Tento článek je strojově přeložený – přečtěte si toto upozornění. Anglickou verzi tohoto článku pro referenci najdete tady.

Poznámka :  Doplněk Power Query se v Excelu 2016 jmenuje Načíst a transformovat. Informace v tomto článku se týkají obou verzí. Další podrobnosti najdete v tématu Funkce Načíst a transformovat v Excelu 2016.

V tomto kurzu použijete editoru dotazů Power Query pro import dat ze souboru místní Excelu, který obsahuje informace o produktu a od kanálu kanálu OData, který obsahuje informace o produktu pořadí. Provést transformací a agregace postup a kombinování dat z obou zdrojích k vytvoření sestavy Celkové prodeje za produkt a roku.

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

V tomto kurzu

Úkol 1: Import produktů do excelového sešitu

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

Krok 2: Zvýšení úrovně prvního řádku na záhlaví sloupců tabulky

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

Vytvořené kroky v Power Query

Krok 4: Import dotazu na produkty

Úkol 2: Import dat objednávek z datového kanálu OData

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

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

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

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

Odebrání vybraných sloupců

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

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

Krok 5: Transformace sloupce roku DatumObjednávky

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

Krok 7: Přejmenování dotazu

Konečné výsledky dotazu

Vytvořené kroky v Power Query

Krok 8: Zakázání stažení dotazu do excelového sešitu

Zakázání stažení dotazu

Úkol 3: Zkombinování dotazů Produkty a Celkové prodeje

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

Krok 2: Rozšíření sloučeného sloupce

Odkaz pro rozšíření tabulky o NovýSloupec

Vytvořené kroky v Power Query

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

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

Výsledný dotaz Celkové prodeje za produkt

Úkol 1: Import produktů do excelového sešitu

V tomto úkolu naimportujete produkty ze souboru Výrobky a objednávky.xlsx do excelového sešitu.

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

  1. Vytvořte excelový sešit.

  2. Na pásu karet POWER QUERY klikněte na Ze souboru > Ze souboru aplikace Excel.

  3. V dialogu procházení v Excelu vyhledejte nebo zadejte cestu k souboru Produkty a objednávky.xlsx, který chcete importovat nebo propojit se souborem.

  4. V navigačním podokně poklikejte na list Produkty nebo klikněte na Produkty a potom na Upravit dotaz. Při úpravě dotazu nebo při připojení k novému zdroji dat se zobrazí okno Editoru dotazů.

    Poznámka : Krátké video o tom, jak Editor dotazů zobrazit, najdete na konci tohoto článku.

Krok 2: Zvýšení úrovně prvního řádku na záhlaví sloupců tabulky

V mřížce náhledu dotazu neobsahuje první řádek tabulky žádné názvy sloupců tabulky. Postup zvýšení úrovně prvního řádku na záhlaví sloupců tabulky:

  1. Klikněte na ikonu tabulky ( Ikona tabulky ) v levém horním rohu náhledu dat.

  2. Klikněte na Použít první řádek jako záhlaví.

Zvýšení úrovně prvního řádku na záhlaví sloupců tabulky

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 mřížce náhledu dotazu vyberte sloupce IDProduktu, NázevProduktu, IDKategorieMnožstvíNaJednotku (podržením klávesy CTRL nebo SHIFT a kliknutím).

  2. Na pásu karet Editoru dotazů klikněte na Odebrat sloupce > Odebrat ostatní sloupce nebo klikněte pravým tlačítkem na záhlaví sloupce a potom na Odebrat ostatní sloupce.

    Skrytí ostatních sloupců

Vytvořené kroky v Power Query

Při provádění aktivit dotazu v Power Query se kroky dotazu vytvářejí a zobrazují v podokně Nastavení dotazu v seznamu POUŽITÝ POSTUP. Každý krok dotazu má odpovídající vzorec Power Query známý taky jako jazyk „M“. Další informace o jazyce vzorců v Power Query najdete v tématu Další informace o vzorcích Power Query.

Úkol

Krok dotazu

Vzorec

Připojení k excelovému sešitu

Zdroj

Source{[Name="Produkty"]}[Data]

Zvýšení úrovně prvního řádku na záhlaví sloupců tabulky

FirstRowAsHeader

Table.PromoteHeaders

(Produkty)

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

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader,{"IDProduktu", "NázevProduktu", "IDKategorie", "MnožstvíNaJednotku"})

Krok 4: Import dotazu na produkty

V tomto kroku importujete do excelového sešitu dotaz Produkty.

  1. Na pásu karet Editoru dotazů klikněte na Použít a zavřít. Výsledky se zobrazí v novém listu v Excelu.

Začátek stránky

Úkol 2: Import dat objednávek z datového kanálu OData

V tomto úkolu importujete do excelového sešitu data z ukázkového datového kanálu Northwind OData na adrese http://services.odata.org/Northwind/Northwind.svc.

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

  1. Na kartě POWER QUERY na pásu karet klikněte na 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. Klikněte na OK.

  4. V navigačním podokně poklikejte na tabulku Objednávky nebo klikněte na Objednávky a potom na Upravit.

Poznámka :  Když na tabulku najedete myší, zobrazí se informační rámeček jejího náhledu.

Najetí myší na zdroj dat

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. Po spuštění dotazu se řádky ze související tabulky (Podrobnosti_objednávky) zkombinují do řádků z tabulky předmětu (Objednávky).

V Power Query obsahuje sloupec s odkazem na související tabulku odkaz na položku nebo na tabulku. Odkaz na položku odkazuje na jeden související záznam a představuje vzhledem k tabulce předmětu relaci 1:1. Odkaz na tabulku odkazuje na související tabulku a představuje vzhledem k tabulce předmětu relaci 1:N. Odkaz představuje navigační vlastnosti ve zdroji dat v rámci relačního modelu. Pro datový kanál OData představují navigační vlastnosti entitu s přidružením cizího klíče. V databázi, například SQL Serveru, představují navigační vlastnosti vztahy cizího klíče v databázi.

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

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 podokně náhledu dotazu se posuňte na sloupec Podrobnosti_objednávky.

  2. Ve sloupci Podrobnosti_objednávky klikněte na ikonu rozšíření ( Rozbalit ).

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

    1. Kliknutím na Vybrat všechny sloupce vymažte všechny sloupce.

    2. Klikněte na IDProduktu, JednotkováCenaMnožství.

    3. Klikněte na OK.

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

      Poznámka :  V Power Query můžete rozšířit tabulky propojené se sloupcem a provádět agregační operace se sloupci propojené tabulky před rozšířením dat v tabulce předmětu. 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í. V předchozím úkolu jste použili příkaz Odebrat ostatní sloupce. V tomto úkolu odeberete vybrané sloupce.

Odebrání vybraných sloupců

  1. V podokně náhledu dotazu vyberte všechny sloupce:

    1. Klikněte na první sloupec (IDObjednávky).

    2. Podržte klávesu Shift a klikněte na poslední sloupec (Přepravce).

    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. Pravým tlačítkem klikněte na záhlaví vybraného sloupce a pak klikněte na Odebrat 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.

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

  1. V podokně náhledu dotazu klikněte na ikonu tabulky ( Ikona tabulky ) v levém horním rohu náhledu.

  2. Klikněte na Vložit sloupec > Vlastní.

  3. V dialogu Vložit vlastní sloupec zadejte do textového pole Vzorec vlastního sloupce hodnotu [Podrobnosti_objednávky.JednotkováCena] * [Podrobnosti_objednávky.Množství].

  4. Do textového pole Název nového sloupce zadejte Součet za řádek.

  5. Klikněte na 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 mřížce náhledu klikněte pravým tlačítkem myši na sloupec DatumObjednávky a klikněte na Transformovat > Rok.

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

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

    2. Klikněte pravým tlačítkem na sloupec DatumObjednávky, klikněte na Přejmenovat a zadejte Rok.

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

  1. V mřížce náhledu dotazu vyberte RokPodrobnosti_objednávky.IDProduktu.

  2. Pravým tlačítkem myši klikněte na jedno ze záhlaví a pak na 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. Klikněte na OK.

    Dialog Seskupit podle pro agregační operace

Krok 7: Přejmenování dotazu

Před importem dat prodeje do Excelu pojmenujte dotaz Celkové prodeje:

  1. V podokně Nastavení dotazů zadejte do textového pole Název text Celkové prodeje.

Konečné výsledky dotazu

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

Celkové prodeje

Vytvořené kroky v Power Query

Při provádění aktivit dotazu v Power Query se kroky dotazu vytvářejí a zobrazují v podokně Nastavení dotazu v seznamu POUŽITÝ POSTUP. Každý krok dotazu má odpovídající vzorec Power Query známý taky jako jazyk „M“. Další informace o jazyce vzorců v Power Query najdete v tématu Další informace o vzorcích Power Query.

Úkol

Krok dotazu

Vzorec

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

Zdroj

Source{[Name="Objednávky"]}[Data]

Rozšíření tabulky Podrobnosti_objednávky

Rozšíření tabulky Podrobnosti_objednávky

Table.ExpandTableColumn

(Orders, "Podrobnosti_objednávky", {"IDProduktu", "JednotkováCena", "Množství"}, {"Podrobnosti_objednávky.IDProduktu", "Podrobnosti_objednávky.JednotkováCena", "Podrobnosti_objednávky.Množství"})

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

RemovedColumns

Table.RemoveColumns

(#"Rozšíření Podrobnosti_objednávky",{"IDObjednávky", "IDZákazníka", "IDZaměstnance", "DodatDne", "DatumOdeslání", "Dopravce", "Dopravné", "NázevZásilky", "AdresaDodání", "MěstoDodání", "OblastDodání", "PSČDodání", "ZeměDodání", "Zákazník", "Zaměstnanec", "Přepravce"})

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

InsertedColumns

Table.AddColumn

(RemovedColumns, "Vlastní", each [Podrobnosti_objednávky.JednotkováCena] * [Podrobnosti_objednávky.Množství])

Transformace sloupce DatumObjednávky na zobrazení roku

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Vlastní", "Součet za řádek"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"DatumObjednávky", Datum.Rok}})

RenamedColumns1

Table.RenameColumns

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

Seskupení řádků podle IDProduktu a Roku

GroupedRows

Table.Group
(RenamedColumns1, {"Rok", "Podrobnosti_objednávky.IDProduktu"}, {{"Celkové prodeje", each List.Sum([Součet za řádek]), type number}})

Krok 8: Zakázání stažení dotazu do excelového sešitu

Vzhledem k tomu, že dotaz Celkové prodeje nepředstavuje konečnou sestavu Celkové prodeje za produkt a rok, zakažte stažení dotazu do excelového sešitu. Pokud je Načíst do listu v podokně Nastavení dotazů nastavené na Vypnuto, nebudou se výsledná data tohoto dotazu stahovat. Abyste ale dosáhli požadovaného výsledku, můžete tento dotaz kombinovat s jinými dotazy. V dalším úkolu se naučíte kombinovat tento dotaz s dotazem Produkty.

Zakázání stahování dotazu

  1. V podokně Nastavení dotazů zrušte zaškrtnutí políčka Načíst do listu.

  2. Na pásu karet Editoru dotazů klikněte na Použít a zavřít. V podokně Dotazy sešitu se u dotazu Celkové prodeje zobrazuje Načítání bylo zakázáno.

    Zakázání stažení dotazu

Začátek stránky

Úkol 3: Zkombinování dotazů Produkty a Celkové prodeje

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 zkombinujete dotazy ProduktyCelkové prodeje pomocí kroků dotazu SloučitRozšířit.

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

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

  2. Na kartě QUERY na pásu karet klikněte na Sloučit.

  3. V dialogu Sloučit vyberte Produkty jako primární tabulku a Celkové prodeje jako druhý nebo související dotaz ke sloučení. Celkové prodeje se stanou novým rozšiřitelným sloupcem.

  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. Klikněte na Uložit.

  6. Klikněte na 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 Úrovně ochrany osobních údajů.

    Dialog Sloučit

Po kliknutí na OK vytvoří operace sloučení dotaz. Výsledek tohoto dotazu obsahuje všechny sloupce z primární tabulky (Produkty) a jediný sloupec obsahující navigační odkaz na související tabulku (Celkové prodeje). Operace rozšíření přidá do primární tabulky nebo tabulky předmětu nové sloupce ze související tabulky.

Výsledné sloučení

Krok 2: Rozšíření sloučeného sloupce

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

Odkaz pro rozšíření tabulky o NovýSloupec

  1. V podokně náhledu dotazu klikněte na ikonu rozšíření NovýSloupec ( Rozbalit ).

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

    1. Kliknutím na Vybrat všechny sloupce vymažte všechny sloupce.

    2. Klikněte na RokCelkové prodeje.

    3. Klikněte na OK.

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

  4. Seřaďte je sestupně podle Celkových prodejů, abyste zjistili, které produkty ve kterém roce dosáhly nejvyšších objemů prodeje.

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

Rozbalení odkazu na tabulku

Vytvořené kroky v Power Query

Při činnostech sloučení dotazu v Power Query se vytvoří kroky dotazu a zobrazí se v podokně Nastavení dotazů v seznamu POUŽITÝ POSTUP. Každý krok dotazu má odpovídající vzorec Power Query známý taky jako jazyk „M“. Další informace o jazyce vzorců v Power Query najdete v tématu Další 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,{"IDProduktu"},#"Celkové prodeje",{"Podrobnosti_objednávkyIDProduktu"},"NovýSloupec")

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

ExpandNewColumn

Table.ExpandTableColumn

(Source, "NovýSloupec", {"Rok", "Celkové prodeje"}, {"NovýSloupec.Rok", "NovýSloupec.Celkové prodeje"})

RenamedColumns

Table.RenameColumns

(#"Rozšíření NovýSloupec",{{"NovýSloupec.Rok", "Rok"}, {"NovýSloupec.Celkové prodeje", "Celkové prodeje"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Celkové prodeje", Order.Descending}})

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

V tomto kroku zakážete možnost Načíst do listu a načtením dotazu do datového modelu Excelu vytvoříte sestavu propojenou s výsledkem dotazu. Kromě načítání výsledků dotazu do excelového listu vám Power Query umožňuje načíst výsledek dotazu do datového modelu Excelu. Po načtení dat do datového modelu Excelu můžete použít Power Pivot a Power View k další analýze dat.

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

  1. V podokně Nastavení dotazu zrušte zaškrtnutí políčka Načíst do listu a zaškrtněte políčko Načíst do datového modelu.

  2. Pokud chcete dotaz načíst do datového modelu Excelu, klikněte na Použít a zavřít.

Načtení datového modelu Excelu

Výsledný dotaz Celkové prodeje za produkt

Po provedení všech kroků budete mít dotaz Celkové prodeje za produkt kombinující data ze souboru Produkty a objednávky.xlsx a datového kanálu Northwind OData. Tento dotaz můžete použít na model Power Pivot. Změny dotazu v Power Query navíc změní a aktualizují výslednou tabulku v modelu Power Pivot.

Začátek stránky

Poznámka : Editor dotazů se zobrazí jenom v případě, že v Power Query načtete nebo budete upravovat dotaz, případně když budete vytvářet nový dotaz. Následující video ukazuje okno Editor dotazů, které se zobrazí, když budete z excelového sešitu upravovat dotaz. Jestli chcete Editor dotazů zobrazit bez načtení nebo úpravy existujícího dotazu v sešitu, vyberte ve skupině Načíst externí data na záložce Power Query pásu karet možnost Z jiných zdrojů > Prázdný dotaz. Jeden ze způsobů, jak se dá Editor dotazů zobrazit, ukazuje následující video.

Jak zobrazit Editor dotazů v Excelu

Poznámka : Upozornění ke strojovému překladu: Tento článek přeložil počítačový systém bez zásahu člověka. Společnost Microsoft nabízí tyto strojové překlady proto, aby umožnila uživatelům, kteří nemluví anglicky, získat informace o produktech, službách a technologiích této společnosti. Protože je tento článek strojově přeložený, může obsahovat slovní, syntaktické nebo gramatické chyby.

Sdílení Facebook Facebook Twitter Twitter E-mail E-mail

Byly tyto informace užitečné?

Výborně! Je ještě něco dalšího, co byste nám chtěli dát vědět?

Jak bychom ho mohli vylepšit?

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

×