V dnešním videu si ukážeme, jak vyřešit jeden z nejčastějších problémů v datech. A to je jak vyhledávat v Excelu, i když se názvy, dle kterých vyhledáváte vůbec neshodují. Máte jednoho zákazníka, ale v tabulce se vám objevuje pod deseti různými názvy. Jednou máte v exportu ze systému zákazníka uvedeného pod správným označením, jednou s koncovkou s.r.o. nebo a.s., jednou bez koncovky, jednou ho zde máte uvedeného pod zkratkou atd. Ve skutečnosti se jedná o jednoho zákazníka, ale pod několika různými označeními. Jak byste taková data sladili, abyste s nimi mohli dále počítat?
Excelový soubor ke stažení:
Naším úkolem je zjistit, jaké byly celkové tržby pro jednotlivá odvětví v jednotlivých letech. Tržby máme uvedené v tabulce tržba. V této tabulce máme uvedené společnosti a jejich tržby. Ale nemáme zde uvedená odvětví, pod které společnosti patří. Ty musíme dohledat z tabulky Kategorie.
Informaci o zařazení společnosti do kategorie a odvětví máme uvedenou v tabulce Zákazník kategorie. Problém, který zde nastává je ten, že zatímco v tabulce kategorie zákazník máme názvy společností uvedené správně, ale v exportu ze systému, kde máme tržby máme několik variant jmen společností. Jak tyto tabulky vyčistit tak, abychom mohli dohledat tržby k odvětvím? Abychom dokázali spočítat celkové tržby pro jednotlivá odvětví, tak musíme do tabulky s tržbami dohledat odvětví. Zde ale narážíme na problém, že nemáme přesnou shodu u názvů společností, abychom dohledali odvětví do tabulky s tržbami. A ručně se nám čistit data opravdu nechtějí.
Vyřešíme to přes Power Query a mapovací tabulku.
Než tabulku nahrajeme do Power Query, tak vytvoříme mapovací tabulku. Mapovací tabulka není nic jiného než spojovací můstek mezi oběma tabulkami, které se snažíme propojit. Pomocí mapovací tabulky sjednotíme názvy v tabulkách a díky nim proběhne propojení. Mapovací tabulka má vždy dva sloupce, první sloupec, který se jmenuje From a druhý sloupec, který bude vždy nazvaný jako To. Toto je klíčové, jelikož aby Power Query rozpoznalo mapovací tabulku, tak musí mít sloupce tato označení.
Ve sloupci From uvedeme všechny názvy společností, které ve zdrojové tabulce existují. Tedy všechny varianty, pod kterou se společnost vyskytuje. A ve sloupci To, uvedeme u každé společnosti správný název. Tato tabulka následně zajistí, že se názvy společností sjednotí a my tak budeme moci propojit tabulky a spojit informace dohromady. Takže ve sloupci From máme u společnosti ČEZ všechny varianty, které máme ve zdrojové tabulce a vedle toho máme správný formát společnosti, tak jak ho potřebujeme. A toto jsme udělali pro všechny společnosti. Toto je mapovací tabulka, díky které budeme schopni propojit tabulky.
Teď když máme mapovací tabulku hotovou, tak všechny tři zdrojové tabulky nahrajeme do Power Query. Všechny tři zdrojové tabulky máme ve formě excelové tabulky, zdrojová tabulka s tržbami je nazvaná jako CRM_tržba, mapovací tabulka je pojmenovaná jako MapovacíTbl a poslední tabulka je pojmenovaná jako Kategorie. Postupně klikneme do tabulek a nahrajeme je do editoru Power Query. A jelikož nechceme duplikovat tabulky v sešitu, tak na tyto tabulky vytvoříme pouze připojení.
Teď když máme všechny zdrojové tabulky nahrané v editoru, tak se můžeme pustit do propojení tabulek. Klíčovou tabulkou je tabulka s tržbami, takže ji označíme a na kartě Domů vybereme Sloučit dotazy a Sloučit dotazy jako nový.
Potřebujeme propojit tabulku CRM_tržba s tabulkou Kategorie. A společným sloupcem je sloupec, kde máme uvedené názvy zákazníka, tedy sloupec Společnost a Zákazník, takže tyto dva sloupce označíme. Typ spojení necháme levý vnější, ale musíme zaškrtnout Sloučit s použitím přibližné shody a v úplně posledním rozbalovacím seznamu (Tabulka transformace) vybrat mapovací tabulku.
Do tabulky se přidá nový sloupec, ze kterého si pomocí dvojité šipky v záhlaví můžeme vybrat, které sloupce z tabulky Kategorie chceme rozbalit. Řekněme, že chceme rozbalit všechny sloupce.
Do tabulky se tím dohledají korespondující řádky z tabulky Kategorie.
Nakonec seskupíme tabulku. Chceme seskupovat hodnoty podle odvětví a pro každé odvětví chceme spočítat celkovou tržbu.
Nakonec finální dotaz přejmenujeme a pošleme tabulku zpátky do Excelu přes tlačítko Zavřít a Načíst.
Nakonec si můžeme ověřit, že transformace bude reagovat i na nově přidaná data do zdroje. Nesmíme zapomenout novou variantu společnosti přidat i do mapovací tabulky.
