Vytvoření, načtení nebo úprava dotazu v Excelu (Power Query)

Vědět, ve kterém prostředí se nacházíte Power Query je dobře integrovaný do uživatelského rozhraní Excelu, zejména když importujete data, pracujete s připojeními a upravujete kontingenční tabulky, excelové tabulky a pojmenované oblasti. Abyste se vyhnuli nejasnostem, je důležité kdykoli vědět, ve kterém prostředí se právě nacházíte, v Excelu nebo Power Query.

Známé excelové listy, pás karet a mřížka	Pás karet Editoru Power Query a náhled dat

Například manipulace s daty v excelovém listu se od Power Query zásadně liší. Připojená data, která vidíte v excelovém listu, navíc můžou nebo nemusí mít Power Query fungující na pozadí, aby data tvarovala. K tomu dochází pouze v případě, že data načtete do listu nebo datového modelu z Power Query.

Přejmenování oušek listů    Ouška listů je vhodné smysluplně přejmenovat, zejména pokud jich máte hodně. Je obzvláště důležité vyjasnit rozdíl mezi listem dat a listem načteným z editoru Power Query. I když máte jenom dva listy, jeden s excelovou tabulkou s názvem List1 a druhý dotaz vytvořený importem této excelové tabulky s názvem Tabulka1, je snadné se zmást. Vždy je vhodné změnit výchozí názvy oušek listů na názvy, které pro vás budou dávat větší smysl. Například přejmenujte List1 na DataTable a Table1 na QueryTable. Teď je jasné, která karta obsahuje data a která karta obsahuje dotaz.

Můžete buď vytvořit dotaz z importovaných dat, nebo vytvořit prázdný dotaz.

Vytvoření dotazu z importovaných dat

Toto je nejběžnější způsob, jak vytvořit dotaz.

1. Importujte nějaká data. Další informace najdete v tématu Import dat z externích zdrojů dat.

2. Vyberte buňku v datech a pak vyberte Dotaz > Upravit.

Vytvoření prázdného dotazu

Možná budete chtít začít úplně od začátku. Tuto akci můžete provést dvěma způsoby:

• Vyberte Data > Získat data > z jiných zdrojů > prázdný dotaz.

• Vyberte Data > Získat data > Spustit editor Power Query.

Pokud jazyk vzorců Power Query M dobře znáte, můžete v tomto okamžiku přidat kroky a vzorce ručně.

Nebo můžete vybrat Domů a pak vybrat příkaz ve skupině Nový dotaz . Proveďte jeden z následujících kroků:

• Vyberte Nový zdroj a přidejte zdroj dat. Tento příkaz je stejný jako příkaz Data > Získat data na pásu karet Excelu.

• Vyberte Poslední zdroje a vyberte ze zdroje dat, se kterým jste pracovali. Tento příkaz je stejný jako příkaz Data > Poslední zdroje na pásu karet Excelu.

• Pokud chcete data zadat ručně, vyberte Zadat data. Tento příkaz můžete zvolit k vyzkoušení Editoru Power Query nezávisle na externím zdroji dat.

Za předpokladu, že je dotaz platný a neobsahuje žádné chyby, můžete ho načíst zpět do listu nebo datového modelu.

Načtení dotazu z editoru Power Query

V Editoru Power Query udělejte jednu z těchto věcí:

• Pokud chcete na list načíst, vyberte Domů > Zavřít & Načíst > Zavřít & Načíst.

• Pokud chcete načíst do datového modelu, vyberte Domů > Zavřít & Načíst > Zavřít & Načíst do.
  
  V dialogovém okně Importovat data vyberte Přidat tato data do datového modelu.

Tip   Někdy je příkaz Načíst do neaktivní nebo zakázaný. K tomu může dojít při prvním vytvoření dotazu v sešitu. Pokud k tomu dojde, vyberte Zavřít & Načíst, v novém listu vyberte Data > Dotazy & Připojení > karta Dotazy , klikněte pravým tlačítkem myši na dotaz a pak vyberte Načíst do. Případně na pásu karet Editoru Power Query vyberte Dotaz > Načíst do.

Načtení dotazu z podokna Dotazy a připojení 

V Excelu můžete chtít načíst dotaz do jiného listu nebo datového modelu.

1. V Excelu vyberte Data > Dotazy & Připojení a pak vyberte kartu Dotazy .

2. V seznamu dotazů vyhledejte dotaz, klikněte pravým tlačítkem myši na dotaz a pak vyberte Načíst do. Zobrazí se dialogové okno Import dat.

3. Rozhodněte se, jak chcete data importovat, a pak vyberte OK. Pokud chcete získat další informace o používání tohoto dialogového okna, vyberte otazník (?).

Existuje několik způsobů, jak upravit dotaz načtený do listu.

Úprava dotazu z dat v excelovém listu

• Pokud chcete upravit dotaz, vyhledejte dříve načtený z Editoru Power Query, vyberte buňku v datech a pak vyberte Dotaz > Upravit.

Úprava dotazu v podokně Dotazy & připojení

Podokno Dotazy & připojení je pro vás možná pohodlnější, když máte v jednom sešitu mnoho dotazů a chcete jeden rychle najít.

1. V Excelu vyberte Data > Dotazy & Připojení a pak vyberte kartu Dotazy .

2. V seznamu dotazů vyhledejte dotaz, klikněte pravým tlačítkem myši na dotaz a pak vyberte Upravit.

Úprava dotazu v dialogovém okně Vlastnosti dotazu

• V Excelu vyberte Data > Data & Připojení > karta Dotazy, klikněte pravým tlačítkem myši na dotaz a vyberte Vlastnosti, v dialogovém okně Vlastnosti vyberte kartu Definice a pak vyberte Upravit dotaz.

Spropitné    Pokud jste na listu s dotazem, vyberte Data > Vlastnosti, v dialogovém okně Vlastnosti vyberte kartu Definice a pak vyberte Upravit dotaz. 

Datový model obvykle obsahuje několik tabulek uspořádaných v relaci. Dotaz do datového modelu načtete tak, že pomocí příkazu Načíst dozobrazíte dialogové okno Importovat data a zaškrtnete políčko Přidat tato data do režimu datl. Další informace o datových modelech najdete v tématech Zjištění, které zdroje dat se používají v datovém modelu sešitu, Vytvoření datového modelu v Excelu a Vytvoření kontingenční tabulky pomocí více tabulek.

1. Datový model otevřete tak, že vyberete Power Pivot > Spravovat.

2. V dolní části okna Power Pivotu vyberte kartu listu požadované tabulky.
   
   Ověřte, že se zobrazuje správná tabulka. Datový model může obsahovat mnoho tabulek.

3. Poznamenejte si název tabulky.

4. Okno Power Pivotu zavřete tak, že vyberete Soubor > Zavřít. Uvolnění paměti může trvat několik sekund.

5. Vyberte Data > Připojení & Vlastnosti > kartě Dotazy , klikněte pravým tlačítkem myši na dotaz a pak vyberte Upravit.

6. Po dokončení provádění změn v Editoru Power Query vyberte Soubor > Zavřít & Načíst.

Výsledek

Dotaz v listu a tabulka v datovém modelu se aktualizují.

Pokud zjistíte, že načtení dotazu do datového modelu trvá mnohem déle než načtení do listu, zkontrolujte kroky Power Query a zjistěte, jestli nefiltrujete textový sloupec nebo strukturovaný sloupec seznamu pomocí operátoru Obsahuje . Tato akce způsobí, že Excel znovu zobrazí výčet celé datové sady pro každý řádek. Excel navíc nemůže efektivně používat vícevláknové spouštění. Jako alternativní řešení zkuste použít jiný operátor, například Rovná se nebo Začíná na.

Společnost Microsoft o tomto problému ví a je vyšetřovaná.

Power Query můžete načíst:

• Na list. V Editoru Power Query vyberte Domů > Zavřít & Načíst > Zavřít & Načíst.

• Do datového modelu. V Editoru Power Query vyberte Domů > Zavřít & Načíst > Zavřít & Načíst do.
  
  Power Query ve výchozím nastavení při načítání jednoho dotazu načte dotazy do nového listu a do datového modelu načte více dotazů najednou.  Můžete změnit výchozí chování všech sešitů nebo jenom aktuálního sešitu. Při nastavování těchto možností Power Query nezmění výsledky dotazů v listu ani v datech a poznámkách datového modelu.
  
  Výchozí nastavení dotazu můžete také dynamicky přepsat pomocí dialogového okna Importovat , které se zobrazí po výběru možnosti Zavřít & Načíst do.

Globální nastavení, která platí pro všechny vaše sešity

1. V Editoru Power Query vyberte Soubor > Možnosti a nastavení > Možnosti dotazu.

2. V dialogovém okně Možnosti dotazu na levé straně v části GLOBAL vyberte Načtení dat.

3. V části Výchozí nastavení načítání dotazů postupujte takto:

   • Vyberte Použít standardní nastavení načítání. 

   • Vyberte Zadat vlastní výchozí nastavení  načítání a pak vyberte nebo zrušte zaškrtnutí políčka Načíst do listu nebo Načíst do datového modelu.

Spropitné    V dolní části dialogového okna můžete vybrat Obnovit výchozí nastavení a pohodlně se vrátit k výchozímu nastavení.

Nastavení sešitu, která platí jenom pro aktuální sešit

1. V dialogovém okně Možnosti dotazu na levé straně v části AKTUÁLNÍ SEŠIT vyberte Načtení dat.

2. Udělejte jednu nebo víc z těchto věcí:

   • V části Detekce typů vyberte nebo zrušte zaškrtnutí políčka Rozpoznat typy sloupců a záhlaví pro nestrukturované zdroje.
     
     Výchozím chováním je jejich zjištění. Pokud chcete data tvarovat sami, zrušte zaškrtnutí této možnosti.

   • V části Relace vyberte nebo zrušte zaškrtnutí políčka Vytvářet relace mezi tabulkami při prvním přidání do datového modelu.

     Před načtením do datového modelu je výchozím chováním vyhledat existující relace mezi tabulkami, jako jsou cizí klíče, v relační databázi a importovat je s daty. Zrušte zaškrtnutí této možnosti, pokud to chcete udělat sami.

   • V části Relace vyberte nebo zrušte zaškrtnutí políčka Aktualizovat relace při aktualizaci dotazů načtených do datového modelu.
     
     Výchozí chování je neaktualizovat relace. Při aktualizaci dotazů, které jsou již načteny do datového modelu, Power Query vyhledá existující relace mezi tabulkami, jako jsou cizí klíče, v relační databázi a aktualizuje je. To může odebrat relace vytvořené ručně po importu dat nebo zavést nové relace. Pokud to ale chcete udělat, vyberte tuto možnost.

   • V části Data na pozadí vyberte nebo zrušte zaškrtnutí políčka Povolit stahování náhledů dat na pozadí.
     
     Výchozí chování je stahovat náhledy dat na pozadí. Pokud chcete zobrazit všechna data hned, zrušte zaškrtnutí této možnosti.