Dnes se ve videu vrátíme k Power Query – nástroji, který vám dokáže během pár minut ušetřit hodiny ruční práce. Pokud s daty pracujete často, tak Power Query by měl být váš nejlepší přítel v Excelu. V tomto videu se podíváme na několik užitečných triků v Power Query, díky kterým bude vaše práce v Power Query rychlejší a efektivnější.
Excelový soubor ke stažení:
Ve zdrojovém excelovém souboru, který najdete ke stažení na webu Akademie Excelu, máme pět příkladů. Zdrojové tabulky máme nahrané v Power Query, takže otevřeme editor a ukážeme si jednotlivé triky. Rozklikneme Příklad číslo jedna a po chvilce se otevře editor Power Query.
Trik pro psaní funkcí
Když začnete být trochu pokročilí v PQ a jazyku M, tak začnete psát vlastní funkce a tím si upravovat transformace nad rámec uživatelského prostředí. Stejně jako v Excelu se vám i v PQ nabízí nápověda, když vyhledáváte funkce. U toho ale můžete narazit na několik otravných věcí. Řekněme, že chceme použít funkci Number.From, tak ji do příkazovéhp řádku začneme psát.
Když funkci najdeme a potvrdíme, tak se funkce nevloží správně. Máme zde dvakrát slovo Number. Stalo se vám to někdy? Když si toho nevšimnete a potvrdíte, tak se vrátí chyba nebo to musíte ručně přepsat.
Důvodem je tečka ve funkci. Když do funkce napíšete tečku a pak ji najdete v nápovědě a potvrdíte, tak se slovo vloží první slovo před tečkou znovu, řešením a trikem je tečku do funkce nepsat. Napíšeme funkci a vynecháme tečku a teď najdeme funkci a potvrdíme. Tento trik mi ušetřil hodiny a hodiny chyb a ručního opravování napsaných funkcí.
Změnit datový typ u více sloupců najednou
U sloupců v Power Query je klíčové stanovit správně datové typy. Klasicky se mění datový typ ze záhlaví sloupce, kde máte na výběr jednotlivé datové typy. Co když ale chcete změnit datový typ u více sloupců najednou? Když označíte sloupce a zkusíte jeden z datových typů v záhlaví, tak to nebude fungovat. Trik spočívá v tom, že označíme sloupce, a klikneme do záhlaví pravým tlačítkem myši a zde máme možnost změnit datový typ a bude to fungovat na více sloupců. Datový typ chceme z datumu a času změnit pouze na datum, takže vybereme datum a díky triku s pravým tlačítkem máme změněný datový typ u více sloupců najednou.
Jak seřadit sloupce v záhlaví
V tabulce máme sloupce, které potřebujeme seřadit. V záhlaví máme z toho důvodu i tato čísla, která značí pořadí sloupců. Jak ale seřadíme sloupce, když zde není žádné tlačítko na řazení podle názvů ve sloupci? Většina lidí to řeší tak, že ručně sloupce přehází. Nicméně u hodně sloupců by nás to asi nebavilo. Existuje trik. Na kartě Domů vybereme Vybrat sloupce, kde máme možnost vybrat sloupce, které chceme zanechat v tabulce. Máme zde ale i ikonu pro seřazení, kde máme možnost seřadit položky v záhlaví podle názvu.
Přidat datový typ u Table.AddColumns
Jedno ze základních pravidel u práce v Power Query je snažit se úpravy udělat s co nejmenším počtem kroků, aby aktualizace trvaly co nejkratší čas. Nejčastěji se vám vkládá navíc krok Změněný datový typ. Věděli jste ale, že si tento krok můžete ušetřit, když přidáváte nový sloupec? Řekněme, že vytvoříme nový vlastní sloupec, kde vynásobíme cenu číslem. A přidá se funkce Table.AddColumn, a tato funkce má i jeden nepovinný parametr, kde můžeme změnit datový typ. Tím si ušetříme krok navíc.
Když mezera není mezera
Stalo se vám někdy, že jste se snažili rozdělit hodnoty ve sloupci podle mezery, ale sloupec se ne a ne rozdělit? Možná to není mezera. V příkladu máme sloupec, kde máme hodnoty a chceme hodnoty rozdělit podle mezery, ale nic se nestane. Jak zjistíme, co máme v buňce? Jedná se o pevnou mezeru. Což je mezera, která se od obyčejné mezery liší v tom, že zabraňuje zalomení textu na konci řádku.
Když nevíme, co máme v buňce za oddělovač, tak se pokusíme sloupec cvičně oddělit oddělovačem a speciální znak se ukáže jako oddělovač. Zde máme znak pro pevnou mezeru.
Nahradíme tedy všechny speciální znaky mezerou.
Nicméně musíme se vrátit do příkazového řádku a opravit znak, při nahrazování se vložily dva křížky. Křížek v závorkách musíme smazat.
A po nahrazení speciálního znaku mezerou již půjde sloupec rozdělit.
