Power Query je integrován do Windows verze Office 365, Excel 2016, Excel 2019 a je k dispozici ke stažení zdarma ve Windows verzích Excel 2010 a Excel 2013. Nástroj je navržen k extrakci, transformaci a načtení dat do aplikace Excel z různé zdroje. Nejlepší část: Power Query si pamatuje vaše kroky a bude je přehrávat, když budete chtít aktualizovat data. To znamená, že můžete data vyčistit 1. den v 80% normálního času a data můžete vyčistit 2. až 400. den jednoduše kliknutím na Obnovit.
Říkám to o mnoha nových funkcích aplikace Excel, ale toto je opravdu nejlepší funkce, která zasáhne Excel za 20 let.
Na svých živých seminářích vyprávím příběh o tom, jak byl Power Query vynalezen jako berle pro zákazníky služby SQL Server Analysis Services, kteří byli nuceni používat Excel pro přístup k Power Pivot. Power Query se ale stále zlepšoval a každý člověk, který používá Excel, by si měl najít čas a naučit se Power Query.
Získejte dotaz na výkon
Možná již máte Power Query. Je ve skupině Získat a transformovat na kartě Data.
Pokud se ale nacházíte v aplikaci Excel 2010 nebo Excel 2013, přejděte na internet a vyhledejte Download Power Query. Vaše příkazy Power Query se zobrazí na vyhrazené kartě Power Query na pásu karet.
Vyčistěte data poprvé v Power Query
Chcete-li uvést příklad některé z úžasných funkcí Power Query, řekněte, že si každý den zobrazíte níže zobrazený soubor. Sloupec A není vyplněn. Čtvrtiny jdou napříč místo dolů po stránce.
Začněte tím, že tento sešit uložíte na pevný disk. Umístěte jej na předvídatelné místo se jménem, které pro tento soubor budete každý den používat.
V aplikaci Excel vyberte Získat data, ze souboru, ze sešitu.
Přejděte do sešitu. V podokně náhledu klikněte na List1. Místo kliknutí na Načíst klikněte na Upravit. Nyní vidíte sešit v mírně odlišné mřížce - mřížce Power Query.
Nyní musíte opravit všechny prázdné buňky ve sloupci A. Pokud jste to udělali v uživatelském rozhraní aplikace Excel, nepraktická sekvence příkazů je Domů, Najít a vybrat, Přejít na speciální, Prázdné, Rovné, Šipka nahoru, Ctrl + Enter .
V Power Query vyberte Transformovat, Vyplnit, Dolů.
Všechny hodnoty null jsou nahrazeny hodnotou shora. S Power Query to trvá tři kliknutí místo sedmi.
Další problém: Čtvrtiny jdou napříč místo dolů. V aplikaci Excel to můžete opravit pomocí kontingenční tabulky s více rozsahy konsolidace. To vyžaduje 12 kroků a 23+ kliknutí.
V Power Query vyberte dva sloupce, které nejsou čtvrtinami. Otevřete rozevírací seznam Unpivot Columns na kartě Transformace a zvolte Unpivot Other Columns, jak je uvedeno níže.
Pravým tlačítkem klikněte na nově vytvořený sloupec Atribut a namísto Atributu jej přejmenujte Čtvrtletí. Z dvaceti kliknutí v aplikaci Excel se stane pět kliknutí v Power Query.
Abychom byli spravedliví, ne každý krok čištění je v Power Query kratší než v aplikaci Excel. Odebrání sloupce stále znamená kliknout pravým tlačítkem na sloupec a zvolit Odstranit sloupec. Ale abych byl upřímný, příběh zde není o úspoře času v Den 1.
Ale počkejte: Power Query si pamatuje všechny vaše kroky
Podívejte se na pravou stranu okna Power Query. Existuje seznam s názvem Aplikované kroky. Jedná se o okamžitou auditní stopu všech vašich kroků. Kliknutím na libovolnou ikonu ozubeného kola můžete v tomto kroku změnit své volby a nechat změny kaskádovat v dalších krocích. Kliknutím na libovolný krok zobrazíte, jak vypadala data před tímto krokem.
Po dokončení čištění dat klikněte na Zavřít a načíst, jak je znázorněno níže.
Spropitné
Pokud jsou vaše data více než 1 048 576 řádků, můžete pomocí rozbalovací nabídky Zavřít a načíst načíst data přímo do datového modelu Power Pivot, který pojme 995 milionů řádků, pokud máte v počítači nainstalován dostatek paměti.
Za pár sekund se vaše transformovaná data zobrazí v aplikaci Excel. Skvělý.
Výplata: Čistá data zítra jediným kliknutím
Příběh Power Query ale opět není o úspoře času v Den 1. Když vyberete data vrácená pomocí Power Query, na pravé straně aplikace Excel se zobrazí panel Dotazy a připojení a na něm tlačítko Obnovit. (Potřebujeme zde tlačítko Upravit, ale protože tam žádné není, musíte kliknout pravým tlačítkem myši na původní dotaz, abyste jej mohli zobrazit nebo v něm provést změny).
Je zábavné čistit data 1. den. Rád dělám něco nového. Ale když můj manažer uvidí výslednou zprávu a řekne: „Nádhera. Můžeš to dělat každý den? “ Rychle jsem začal nenávidět nudu každodenního čištění stejné datové sady.
Abych demonstroval 400. den čištění dat, úplně jsem změnil původní soubor. Nové produkty, noví zákazníci, menší počet, více řádků, jak je uvedeno níže. Ukládám tuto novou verzi souboru na stejnou cestu a se stejným názvem souboru jako původní soubor.
Pokud otevřete sešit dotazu a kliknu na Obnovit, za několik sekund Power Query hlásí 92 řádků místo 68 řádků.
Čištění dat v Den 2, Den 3, Den, 4,… Den 400,… Den Infinity nyní vyžaduje dvě kliknutí.
Tento jeden příklad pouze poškrábe povrch Power Query. Pokud s knihou strávíte dvě hodiny, M je pro (Data) Monkey od Ken Puls a Miguela Escobara, dozvíte se o dalších funkcích, jako jsou tyto:
- Kombinace všech souborů Excel nebo CSV ze složky do jedné mřížky aplikace Excel
- Převod buňky pomocí Apple; Banán; Třešeň; Kopr; Lilek na pět řádků v aplikaci Excel
- Provádění SVYHLEDÁVÁNÍ do vyhledávacího sešitu při přenosu dat do Power Query
- Vytvoření jednoho dotazu do funkce, kterou lze použít na každý řádek v aplikaci Excel
Úplný popis Power Query najdete v M Is for (Data) Monkey od Ken Puls a Miguel Escobar. Koncem roku 2019 bude k dispozici retitled druhé vydání, Master Your Data.
Děkujeme Miguelovi Escobarovi, Robovi Garciovi, Mikeu Girvinovi, Rayu Hauserovi a Colinovi Michaelovi za nominaci na Power Query.
