V dnešním videu si ukážeme, jak se pomocí Power Query v Excelu napojit na složku, kde máte uložené zdrojové excelové soubory. Díky Power Query totiž můžeme spojovat i tabulky, které jsou v různých excelových sešitech a celý proces u toho dokonce zautomatizovat. Takže až příště do složky přidáte nový soubor, tak se po aktualizaci nová data automaticky zahrnou do vašich výpočtů a to na jedno kliknutí myši.
V předchozích videích jsme si již ukázali jak provádět úpravy čistit text pomocí Power Query, jak sloučit tabulky do sloučené tabulky nebo jak sloučit tabulky pomocí Power Query.
Excelový soubor ke stažení
Jak sloučit excelové soubory pomocí Power Query
Představme si, že je naším úkolem analyzovat data, která jsou ovšem uložená v několika různých excelových souborech. Prodejní data jsou ukládána každý měsíc do separátního souboru a jsou v jedné složce, která je pojmenovaná jako Zdrojová data. Ve složce máme zatím prodejní data za první tři měsíce. Když se pomocí náhledu podíváme na to, jak data v jednotlivých souborech vypadají, tak vidíme, že se jedná o stejně strukturované tabulky. Proto abychom s daty mohli jakkoliv počítat, tak musíme data z různých Excelů dostat do jedné tabulky.
Jedním způsobem, jak data spojit, je samozřejmě zkopírovat tabulky z jednotlivých souborů do jedné společné tabulky. Nevýhodou tohoto způsobu je to, že se jedná o dost manuální práce, zvláště pokud bychom ve složce měli desítky souborů. Podstatnější nevýhodou je ovšem to, že pokud se po našem zkopírování data v souborech změní, tak budeme muset celý proces opakovat.
Pojďme si teď ukázat, jak celý proces zautomatizovat.
Začneme tím, že si otevřeme nový excelový sešit. Na kartě Data najdeme sekci Načíst a Transformovat data. Zde rozklikneme první ikonu Načíst data. Hned první možnost v nabídce je Ze souboru a v této nabídce máme i možnost Ze složky. To je přesně to, co potřebujeme.
Otevře se okno navigátoru, kde musíme najít zdrojovou složku. Když najdeme zdrojovou složku, tak na ní klikneme a vybereme Otevřít.
V navigačním okně se zobrazí všechny soubory, které jsou ve složce uložené. Potřebujeme tyto soubory sloučit, takže rozklikneme ikonu Kombinovat a vybereme hned první možnost Sloučit a Transformovat data.
V dalším okně se zobrazí seznam listů, které máme v ukázkovém souboru. To, jak sloučení funguje je, že Power Query si vybereme jeden ze zdrojových excelových sešitů jako ukázku, provede transformační kroky na tomto ukázkovém souboru a následně tyto kroky uplatní na ostatní soubory ve složce. To jaký ukázkový soubor si Power Query vybrali vidíme zde, kde je napsáno, že se jedná o první soubor ve složce. Když klikneme na list, tak vidíme náhled na data v ukázkovém souboru, což je březen. Chceme sloučit tyto listy ve všech souborech, takže vybereme OK.
Po chvilce se otevře klasické okno Power Query. V levém panelu se zobrazil ukázkový soubor, přes který proběhly transformační kroky a dole se zobrazila sloučená zdrojová data. V pravém panelu se rovněž načetly kroky, které Power Query udělalo proto, aby došlo ke sloučení souborů. V podstatě se Power Query nejprve napojilo na složku, následně pomocí funkce spojila soubory dohromady a výsledkem je sloučená tabulka.
Když rozklikneme filtr v tabulce, tak můžeme ověřit, že ve sloučené tabulce opravdu máme data za všechny tři měsíce. Nicméně naše práce není u konce. Sloučená tabulka potřebuje ještě několik úprav, abychom s ní mohli pracovat. První věc, kterou upravíme je, že do tabulky přibyl nový sloupec, ve kterém je název souboru. V závislosti na tom, jak máte soubor pojmenovaný se vám tento sloupec může a nemusí hodit. Rovněž podle neúplné zelené čáry v záhlaví tabulky vidíme, že máme ve sloupcích prázdné buňky.
Začneme prázdnými buňkami. Těch se jednoduše zbavíme pomocí filtru v záhlaví tabulky. Vybereme si jeden sloupec, třeba sloupec s produkty a ve filtru odškrtneme NULL, což je prázdná buňka. Potvrdíme a teď podle zelené čáry vidíme, že jsou všechny sloupce úplné, tedy že v žádném sloupci nemáme prázdné buňky.
Ve zdrojových datech máme uvedené datumy, takže teoreticky první sloupec nepotřebujeme. Pokud byste ho ale chtěli kvůli filtraci zachovat, tak můžeme provést pár úprav. Začneme tím, že odstraníme příponu .xlsx. Označíme sloupec a na kartě Transformace najdeme Nahradit hodnoty. Chceme nahradit celou příponu včetně tečky, a nahradit ji chceme ničím, takže v poli Nahradit hodnotou nevyplníme nic. Potvrdíme a přípona se odstranila.
Ještě odstraníme podtržítko stejným způsobem. Vybereme nahradit hodnoty a jako nahrazovaný znak napíšeme podtržítko a tentokrát ho chceme nahradit mezerou, jelikož chceme mít mezi měsícem a rokem mezeru. Potvrdíme a máme sloupec vyčištěný.
Než sloučenou tabulku nahrajeme do Excelu, tak přejmenujeme první sloupec na MěsícRok a zkontrolujeme, že máme u každého sloupce správný datový typ. Když je tabulka vyčištěná, tak ji můžeme nahrát do Excelu. Na kartě Domů najdeme Zavřít a Načíst.
Po chvilce se sloučená tabulka nahraje na list Zdrojová data. Teď máte aktivní napojení na složku s vašimi zdrojovými soubory.
Na závěr ověříme, že spojení opravdu funguje. Nejprve ve zdrojovém březnovém souboru změníme nějakou hodnotu tržby na velmi vysoké číslo. Zavřeme soubor a do složky nahrajeme ještě nový měsíc. Vrátíme se ke sloučené tabulce a obnovíme spojení. Na kartě Data najdeme v sekci Dotazy a připojení Aktualizovat vše. Připojení se začne aktualizovat, což poznáme podle točícího se kolečka u tabulky. Obnovení se aktualizovalo a hned vidíme, že se nová březnová hodnota nahrála do tabulky. Ověříme, že máme v tabulce i duben a máme ho tam. Ještě můžeme pomocí filtrů v záhlaví tabulky zkontrolovat, že obsah sloupců vypadá v pořádku, a jak se zdá, tak vše vypadá dobře.
Hotovo. Teď máte aktivní napojení na složku.
Jedna odpověď
😊👍