V dnešním videu si ukážeme 12 triků, které vám pomohou při práci v Power Query. Všechny tipy a triky si ukážeme na jednom praktickém příkladu z praxe. Naším úkolem je z prodejní databáze, kterou jsme dostali ve formátu textového souboru vytvořit pro našeho nadřízeného finální report, který bude obsahovat dvě tabulky. V jedné tabulce uvidíme celkové tržby v letech pro jednotlivá oddělení a ve druhé tabulce uvidíme celkové tržby pro vedoucí pracovníky v jednotlivých letech. Jak už to tak bývá, tak nejen, že jsou zdrojová data v textovém souboru, ale ještě jsou dosti špinavá. Obsahují spojená data, spoustu nechtěných znaků a nesprávné formáty. Než s takovými daty tedy budeme moci pracovat, tak je nejprve musíme z textového souboru dostat do Excelu a ještě je musíme vyčistit a právě v tom nám pomůže Power Query.
Excelové soubory ke stažení
Triky v Power Query
A rovnou se vrhneme na řešení úkolu.
Načtení dat ze zdroje
Když začnete pracovat s Power Query, tak jednou z nejdůležitějších věcí je naučit se, z jakých zdrojů můžete načítat data do Excelu. Kromě excelových souborů, souborů PDF a souborů csv můžete do Excelu načítat data i třeba z textových souborů. V našem příkladu máme export dat ze systému v podobě textového souboru. Abychom s daty mohli pracovat, tak je nejprve musíme dostat do Excelu. Začneme tím, že otevřeme nový excelový soubor. Na kartě Data najdeme Načíst a Transformovat data, kde najdeme ikonu z Textu nebo CSV. Klikneme na ikonu a otevřeme se okno, ve kterém můžeme soubor vyhledat. Najdeme náš soubor, označíme ho a klikneme na Importovat. Otevře se okno, kde vidíme načtená data. Power Query správně vyhodnotilo, že oddělovačem dat byl tabulátor. Teď se nám data rozdělila pode tabulátoru do jednotlivých sloupců. Jelikož s daty potřebujeme dále pracovat, tak klikneme na Transformovat data.
Tím se otevře Power Query s rozdělenými sloupci a můžeme přistoupit k úpravě dat.
Lokální datum
Prvním problémem, který v naší datové sadě vidíme je, že datum máme ve špatném formátu. Jako první máme v datumu uvedený měsíc a na druhé pozici máme den. To, že Power Query nepoznává datum jako datum vidíme i podle datového typu sloupce, Power Query místo datumu přisoudil Text. Špatný formát datumu může být buď chyba v exportu nebo se s takovým případem můžete setkat v praxi, kdybyste obdrželi podklady například ze Spojených států, kde používají datum rovněž obráceně. Aby Excel rozpoznal, že se jedná o datum a mohli jsme s daty dále pracovat, tak potřebujeme změnit formát datumu. To můžeme udělat přes datové typy. Pokud bychom v záhlaví klikli na datový typ a vybrali datum, tak se datumy, kde bude měsíc po číslici 12, změní na chybu. Toto je špatně, takže krok v pravém panelu smažeme. Trik v převodu datumu spočívá v tom, že musíme kliknout na datový typ a vybrat možnost Použití národního prostředí. Zde vybereme datový typ, tedy datum a zde dole musíme vybrat regionální nastavení, odkud jsme soubor dostali. Nevybíráme tedy naše nastavení, ale původní nastavení. Řekněme, že toto nastavení používají ve Spojených státech, takže najdeme Angličtinu a Spojené státy. Potvrdíme a datumy se změnily na správný formát, kde máme nejprve uvedený den, pak měsíc a následně rok.
Transformace textu
Power Query je skvělým pomocníkem při transformaci textu. Toho využijeme hned v několika následujících příkladech. První textovou úpravu potřebujeme udělat u vedoucího pracovníka, kde máme jméno pracovníka spojené s městem, ve kterém působí. Jméno pracovníka není přitom spojené s městem vždy stejným znakem. Jednou je spojené s obráceným lomítkem a jednou se svislou čárou. Máme několik možností, jak se s úkolem vypořádat, ale vyjdeme z toho, co je naším cílem. Cílem je vytvořit tabulku, kde budeme mít uvedené tržby pro vedoucí pracovníky. To znamená, že v tomto případě ve finálním reportu město nepotřebujeme. Můžeme tedy použít textovou úpravu ve formě Extrakce. Označíme sloupec a na kartě Transformace najdeme Extrahovat, kde vybereme Text před oddělovačem. Prvním oddělovačem je obrácené lomítko. Napíšeme obrácené lomítko a potvrdíme. Z buněk nám teď zmizely města včetně obráceného lomítka. To samé uděláme pro svislou čáru. Označíme sloupec a znovu na kartě Transformace vybereme Extrahovat a Text před oddělovačem, kde jako oddělovač tentokrát použijeme svislou čáru. Potvrdíme a máme sloupec vyčištěný. Kontrolu můžeme provést přes filtr ve sloupci, kde zkontrolujeme, že nám ve sloupci zbyly opravdu jen jména bez nechtěných měst.
Další textovou úpravu musíme použít u sloupce s produktem. Export dat nám bohužel za kódy produktů vyexportoval i nechtěné znaky jako hvězdičky, lomítka a svislé čáry. Opět mámě několik způsobů, jak se s úpravou vypořádat. Můžeme postupně nahradit všechny nechtěné znaky pomocí transformace Nahradit hodnoty. Pokud je znaků ale hodně tak můžeme použít další textovou úpravu přes Rozdělení sloupce, kterou je rozdělení sloupce podle číslice nebo nečíslice. Produktové kódy mají na konci označení číslici a následují znaky, které spadají pod nečíslice. Označíme tedy sloupec a na kartě Transformace najdeme Rozdělit sloupec a zde podle číslice a nečíslice. Potvrdíme a sloupec se rozdělil.
Nicméně zde máme malý problém. Nevšimli jsme si, že se u jednoho produktu znaky propsali do kód produktu, takže se nám někde se znaky oddělila i číslice. Můžeme tedy udělat ještě druhou úpravu a to je, že rozdělíme sloupec znovu a tentokrát jako nečíslice versus číslice. Označíme sloupec a uplatníme rozdělení sloupce a transformaci. Tím se rozdělený sloupec ještě jednou rozdělí.
Na řádcích, kde nebylo číslo je hodnota null, což znamená prázdno a tam kde číslo bylo, je číslo. V prostředním sloupci zůstaly nechtěné znaky. Tento sloupec můžeme odstranit. Klikneme na něj a pravé tlačítko myši a vybereme Odebrat. Teď musíme tyto dva sloupce zase spojit dohromady. Označíme nejprve první sloupec se začátkem kódu, držíme klávesu CTRL a označíme druhý sloupec, na kartě Transformace najdeme Sloučit sloupce. Oddělovač žádný nechceme, a název nového sloupce má být Kód produktu. Potvrdíme a máme vytvořený nový sloupec s očištěným kódem produktu.
Přejmenování kroků
Dalším tipem pro práci v Power Query je, že můžeme přejmenovávat jednotlivé kroky úprav, které jste vytvořili. Všeobecně je každý krok vždy automaticky pojmenovaný dle úpravy, kterou jste provedli. Pokud máte ale kroků několik, tak se po nějaké době můžete ztratit v tom, co jste jednotlivými kroky chtěli udělat. Na každý krok ale můžete kliknout pravým tlačítkem myši a vybrat Přejmenovat. Klikneme třeba na krok s rozdělením sloupců, vybereme přejmenovat a pojmenujeme si krok tak, abychom na první pohled věděli, co jsme tímto krokem dělali. První rozdělení bylo číslice vs nečíslice. Můžeme tedy krok pojmenovat jako očištění kódu. Potvrdíme klávesou ENTER a máme krok přejmenovaný. Takto můžeme přejmenovat všechny kroky u kterých potřebujeme mít větší detail o tom, co jsme krokem provedli za úpravu.
Podmíněný sloupec
U úprav ještě zůstaneme. Řekněme, že máme informaci, že se na začátku roku 2023 vdala vedoucí pracovnice Tereza Bláhová a nadále se již nejmenuje Bláhová, ale Prokopová. Máme tak v databázi dvě různá jména, která vlastně přísluší jedné a té samé osobě. To můžeme opět ověřit přes filtr v záhlaví sloupce. Klikneme do záhlaví a ve filtru vidíme, že zde opravdu máme dvě jména, která patří jedné osobě. Potřebujeme tedy nějakým způsobem jména v databázi sjednotit. Ideálně tedy nahradit všechny předchozí výskyty Terezy Bláhové a nahradit je novým jménem Tereza Prokopová. Opět existuje několik způsob jak to vyřešit. Mohli bychom to vyřešit přes Transformaci, Nahradit hodnoty a zde bychom jako hledanou hodnotu napsali Tereza Bláhová a jako hodnotu pro nahrazení Tereza Prokopová. Můžeme to ale vyřešit i přes podmíněný sloupec. Na kartě Přidání sloupce najdeme Podmíněný sloupec. Nejprve zvolíme jméno nového sloupce, třeba Pracovník. A teď specifikujeme podmínku. Pokud je tedy ve sloupci s vedoucím pracovníkem rovna hodnota Tereza Bláhová, tak chceme hodnotu nahradit v novém sloupci jménem Tereza Prokopová. A pokud se jméno rovnat nebude, tak chceme původní hodnotu ze sloupce s vedoucím pracovníkem. Podmínka je hotová a můžeme potvrdit. Do dat se přidal nový sloupec, kde můžeme přes filtr v záhlaví ověřit, zda máme hodnoty v novém sloupci správně. A vidíme, že Tereza Bláhová se v novém sloupci již nevyskytuje. Ještě změníme u sloupce datový typ na text a jelikož teď máme nový sloupcem se jmény pracovníků, tak ten původní můžeme klidně Odebrat. Pravé tlačítko a Odebrat.
Profilace sloupce
Když do Power Query nahrajete data, která mají více než 1 000 řádků, tak si musíte být vědomi toho, že náhled Power Query, se kterým pracujete je v základu založený pouze na prvních 1 000 horních řádcích. To máme uvedené zde dole na liště. V databázi máme 20 000 řádků, ale do Power Query se teď nahrálo pouze prvních 1 000 řádků, s těmi provádíme úpravy a když jsme hotovi, tak se samozřejmě úpravy přenesou na zbylé řádky. To, že nemáme v Power Query nahranou celou databázi vidíme i ve filtru sloupce, najedeme na sloupec Pracovník, rozklikneme filtr a vidíme, že dole máme vykřičník, který nás upozorňuje, že seznam nemusí být úplný. Pokud byste chtěli zkontrolovat, že vaše úpravy se správně propíší na celá vaše data. Tak klikněte na spodní liště na Profilaci a změňte nastavení na Profilace na základě celé sady. Teď když klikneme do sloupce Pracovník, tak u výstrahy můžeme kliknout na Načíst další a tím se zpracuje celá databáze a teď vidíme filtr jmen, na základě celé datové sady. Při složitějších úpravách je čas od času dobré úpravy zkontrolovat pro celou sad dat, abyste věděli, že jste nikde nezapomněli na nějakou dodatečnou úpravu.
Profil a kvalita sloupce
Na kartě Zobrazení existuje možnost, které se říká Profil sloupce. Toto nastavení vám může pomoci, když si potřebujete udělat přehled o tom, jaká data ve sloupci vlastně máte. Označíme třeba sloupec s pracovníky a na kartě Zobrazení zaklikneme Profil sloupce. Dole v okně se otevře pomocné okno Profilu a máme rychlý náhled dat. Víme, že ve sloupci máme 19 999 hodnot, přičemž 12 hodnot je odlišných, tedy máme 12 pracovníků. Vedle v okně vidíme i distribuci hodnot, takže víme, že se v databázi nejčastěji objevuje jméno Adriana Konopná. To samé můžeme udělat i pro sloupec tržeb. Stačí se pouze překliknout na sloupec tržeb a profil sloupce se změní. Teď víme, že nejnižší tržba je 1 000 Kč, zatímco nejvyšší je 5 500 Kč. Nejčastější tržba v databázi je ve výši 4 631 Kč atd. Když chceme profil sloupce vypnout, tak ho stačí odškrtnout v horní liště.
Druhým pomocným nástrojem je Kvalita sloupce. Na horní liště v Zobrazení zaškrtneme Kvalita sloupce a tím se pod záhlavím sloupců objeví rychlý přehled o datech ve sloupci. Jelikož máme čistou databázi, kde nebyly žádné chyby a prázdné buňky, tak zde vidíme u každého sloupce, že máme 0% chybových hodnot a 0% prázdných buněk. Kvalita sloupce vám tak ideálně pomůže hned na začátku po nahrání dat do Power Query, kde pomocí Kvality sloupce zjistíme, zda máte v databázi chyby a prázdné hodnoty, které bude potřeba buď odstranit a nebo provést jiné úpravy. Když jste s kontrolou hotovi, tak Kvalitu sloupce opět vypneme tak, že pole v liště odškrtnete.
Odstranit do konce
Dalším trikem pro urychlení práce je odstranění nepotřebných kroků. Na ukázku tohoto triku jsem v databázi provedla několik kroků navíc. To vidíme v panelu vpravo. U úprav, zejména u těch složitějších, je časté, že vytvoříte spoustu kroků a po několika si uvědomíte, že jste nedosáhli kýžené úpravy a kroky tedy budete muset smazat. Nepotřebný krok můžete smazat pomocí ikony odstranit u jednotlivých kroků. Nicméně když víte, že potřebujete odstranit několik kroků najednou, tak nejrychlejší je, kliknout na první krok, který chcete odstranit, zmáčknout pravé tlačítko myši a vybrat Odstranit až do konce. Tím se odstraní všechny kroky až do konce včetně toho kroku, který jste měli označený.
Seskupit podle
Ve finálním reportu jsme chtěli zobrazit dvě tabulky. První tabulka měla ukazovat celkové tržby v letech pro jednotlivá oddělení. Zadání říkalo, že bychom neměli použít pro report kontingenční tabulku, jelikož náš nadřízený nerad pracuje s kontingenčními tabulkami. Souhrnný report tedy vytvoříme rovnou v Power Query a to pomocí seskupení dat. Nejdříve si musíme uvědomit, co je naším cílem. V první tabulce potřebujeme seskupit data podle roku a podle oddělení. Abychom data mohli seskupit podle roku, tak nejdříve musíme rok vyextrahovat ze sloupce datum. Klikneme na sloupec Datum a na liště Přidání sloupce vybereme Datum, a zde vybereme Rok. Tím se do tabulky přidal sloupec s příslušným rokem.
Teď máme vše co k seskupení potřebujeme. Na liště Transformace vybereme Seskupit podle. Potřebujeme seskupit data podle dvou sloupců, takže vybereme Upřesnění. To nám dovolí vybrat více seskupení. Prvním sloupcem pro seskupení je nový sloupec Rok. Druhým sloupcem je Oddělení. Přidáme tedy seskupení a vybereme Oddělení. Zvolíme název nového sloupce, třeba Tržba celkem. Vybereme operaci, kterou chceme provést na agregovaných datech, chceme celkové tržby tedy součet a jaký sloupec chceme sečíst? Sloupec s tržbami. Seskupení je hotové a potvrdíme. Data se seskupila přesně tak jak potřebujeme.
Abychom se v datech lépe vyznali, tak je ještě dobré seřadit tabulku podle sloupce s rokem a to vzestupně.
Aby byla tabulka ještě přehlednější, tak můžeme ještě seřadit oddělení tak, abychom vždy měli jako první oddělení A, pak B a C. Klikneme do sloupce oddělení a ještě seřadíme i tento sloupec. Teď máme tabulku seřazenou dle let a oddělení a seskupenou podle celkových tržeb.
Duplikovat
To ovšem není vše. V reportu máme mít i druhou tabulku, která by ukazovala tržby seskupené podle vedoucích pracovníků v letech. Abychom toho docílili, tak potřebujeme duplikát databáze, ve které bychom provedli jiné seskupení. Nemusíme data do Excelu nahrávat podruhé a provádět ty samé úpravy. Můžeme jednoduše kliknout na dotaz v levém panelu, zmáčknout pravé tlačítko myši a vybrat Duplikovat. Tím se dotaz duplikuje i s provedenými kroky. Abychom se v tom vyznali, tak můžeme dotazy přejmenovat. Klikneme na první a přejmenujeme ho na Oddělení, jelikož je to tabulka seskupená dle oddělení. Přejmenujeme rovnou i druhou databázi na pracovníci. V této druhé databázi jen musíme smazat poslední dva kroky, kde jsme hodnoty řadili a seskupovali. Smažeme je v pravém panelu a provedeme nové seskupení. Takže karta Transformace, Seskupit podle a upřesnění. Opět chceme seskupovat podle roku, takže vybereme rok a přidáme seskupení a zde vybereme Pracovník. Opět můžeme nový sloupec nazvat Tržba celkem a chceme provést Součet a chceme sčítat sloupec Tržeb.
Potvrdíme a můžeme rovnou i seřadit data, tak jako v minulé tabulce.
Takže nejprve seřadíme vzestupně sloupec rok a následně sloupec s pracovníky.
Zavřít a Načíst
Se změnami jsme hotoví a můžeme upravená data nahrát do Excelu. Pokud potřebujeme obě tabulky v Excelu vidět, tak na liště Domů vybereme Zavřít a Načíst. Kdybychom data chtěli rovnou nahrát do kontingenční tabulky, vytvořit pouze připojení na data nebo data načíst do datového modelu, tak vybereme Zavřít a Načíst do, kde bychom příslušnou variantu vybrali. Vybereme tedy Zavřít a Načíst a tabulky se začnou nahrávat na separátní listy do Excelu.
Aktualizace dat
Data se nahrála do Excelu a finální report je hotový. Ještě ověříme, že bude fungovat aktualizace dat. Do textového souboru jsem přidala nová data, ve kterých se objevuje o nové oddělení F a nový vedoucí pracovník Otakar Nový. Zkusíme zda funguje aktualizace a zda se nová data objeví v tabulkách. Obnovíme spojení mezi textovým souborem a Excelem, na kartě Data vybereme Aktualizovat vše a počkáme než se spojení obnoví. Po chvilce se spojení aktualizovalo, pojďme zkontrolovat data. V tabulce oddělení přibylo v roce 2023 nové oddělení F, a stejně tak v tabulce s pracovníky přibylo v roce 2023 nové jméno Otakar Nový.
2 komentáře
😊
Děkuji za super tipy 🙂