Rychlé sloučení souborů v Power Query
- Vojtěch Šíma
- Dec 22, 2024
- 7 min read
Updated: Feb 17
tl;dr Power Query nabízí nativní možnost jak sloučit soubory, nicméně tato funkce často vytváří zbytečné množství dodatečných komponentů a může působit těžkopádně. V tomto článku si ukážeme dvě metody, které dosáhnou stejného výsledku, ale jsou přímočařejší a snáze upravitelné. Navíc k těmto metodám budete potřebovat jen jeden (přibližně) řádek kódu, přičemž zbytek lze jednoduše "vyklikat," což je hlavním záměrem tohoto článku.
Nativní "slučovač" souborů
Pravděpodobně jste se setkali s funkcí v Power Query, která umožňuje kombinovat soubory s využitím konkrétního vzorového souboru jako referenčního bodu. Tato možnost se obvykle objeví například při použití konektoru SharePoint Folder.
Zde stačí zvolit možnost Combine & Transform Data a poskytnout vzorový soubor nebo první soubor, který určí strukturu, kterou by měly ostatní soubory při kombinování dodržet. Tento proces vytvoří několik dotazů s parametry a funkcemi, což může na první pohled působit chaoticky a matoucím dojmem, zejména pro nové uživatele. Nicméně je ve skutečnosti nemusíte tolik řešit, pokud provádíte jen jednoduché transformace nebo plánujete data upravit až po jejich sloučení.
S několika dodatečnými úpravami může tento proces vytvořit požadovaný výsledek: více souborů se stejnou strukturou spojených do jednoho dotazu nebo tabulky, kterou následně můžete načíst do svého modelu.
Existuje lepší cesta?
Ano, aspoň tedy osobně si myslím, že existuje pohodlnější způsob. Rozumím, že tato metoda je vyloženě pro uživatele, kteří jenom klikají, přijde mi však, že upravovat a dodatečně cokoliv dodělávat je mnohem složitější, než kdyby si to člověk napsal sám s jedním řádkem kódu a zbytkem klikání.
Vytvoř si vlastní "Slučovač"
Přepsání toho, co Power Query dokáže udělat za vás, je poměrně jednoduché a je později mnohem lépe upravitelné. Místo vytvoření čtyř různých komponent, nám bude stačit pouze jedna: funkce.
Ukážu vám krok za krokem, jak si něco takového vytvořit i "doma v pokojíčku".
Nahrej data a najdi svůj ukázkový soubor
V tomto příkladu použiji konektor SharePoint Folder, protože je to běžná volba pro tento typ připojení. Nicméně stejný postup můžete aplikovat i s jakýmkoliv jiným konektorem podobného charakteru.
Pojďme navigovat Power Query do našeho SharePoint Folderu.
Nyní zadejte Sharepoint stránku, ke které chcete přistupovat, buď ručně, nebo pomocí parametru. Pro větší flexibilitu a snazší manipulaci obvykle doporučuji použít parametr. Mezi metodami můžete přepínat pomocí ikony vlevo.
V dalším okně se vyhněte lákavému, zelenému okénku a klikněte na jednoduché Transform Data.
Tento krok nahraje obsah Sharepoint Folder to Dotazu v Power Query.
Zvol a Připrav Ukázkový Soubor
Dále musíme vybrat vzorový soubor, postupujeme přitom stejným způsobem jako u nativního "kombinátoru". Zde si nasimulujeme transformace, které chceme aplikovat na každý soubor, přičemž vycházíme z předpokladu, že soubory, které chceme kombinovat, mají stejnou povahu a strukturu.
Vybereme si jeden konkrétní.
Tip: Je užitečné pojmenovávat vaše soubory podle stejného vzoru, aby bylo později snazší je identifikovat jako skupinu.
Řekněme, že naším cílovým souborem je "yearly_file_excel_2023.xlsx". Protože se jedná o Excel, musíme provést několik kroků, abychom se dostali k vlastním datům.
Nejdřív klikneme na text "Binary" ve sloupečku "Content". Kliknutím přímo na text se zvolí konkrétní soubor a otevře se jeho obsah.
Jak jsem říkal, pracujeme s Excelem, tudíž použiujeme funkci "Excel.Workbook()", která nás odkáže na seznam listů v našem souboru.
Zde, zopakujeme postup z minulého kroku a kliknete přímo na "Table" ve sloupečku "Data". Přitom dáváme pozor, že jsme zvolili správný list. Tento krok nám pak dovolí vidět naše vlastní data.
Upozornění: V tomto postupu budeme natvrdo nastavovat list, který používáme, tudíž mějte na paměti, že se list musí nacházet ve všech souborech.
Měli bychom dostat taková data.
Zde provedeme všechny potřebné transformace, abychom dosáhli požadovaného výsledku. V našem případě jenom necháme, aby první řádek byl záhlaví, abychom zajistili správné názvy sloupců.
Při přípravě vzorového souboru není nutné nastavovat datové typy, protože je budete muset znovu upravit po sloučení souborů. Přesto stojí za to je zkontrolovat, abyste se ujistili, že vše vypadá správně (i když tento krok později odstraníte).
A to je vše! Nyní máme náš vzorový soubor s požadovanými transformacemi. Dalším krokem je zajistit, aby se tyto transformace aplikovaly na všechny naše soubory.
Ukážu vám dvě metody:
Změň vzorový soubor na funkci #1
Tady přichází ta nejtěžší část—ale nebojte, nemusíte být žádný Hackerman, abyste to zvládli. Potřebujeme přistoupit ke kódu, který byl použit k vytvoření vzorového souboru, a upravit část odkazující se na název souboru tak, aby byl dynamický.
Pokud jste klikali jako já, dostane něco takového:
Upozornění: Přidal jsem komentáře a přejmenoval kroky pro lepší čitelnost. Ve vašem případě uvidíte jiné názvy kroků (a žádné komentáře), ale struktura zůstane stejná.
Jediné, co musíme udělat, je zabalit celý kód do funkce a změnit statický název souboru na dynamický pomocí parametru funkce.
Jak můžete vidět, tak jsme žádný kód neodstranili; jenom jsme všechny existující kroky obalii do funkce s parameterem fileName, který akceptuje text. Dodatečně jsme změnili statický název souboru v kroku selectFile za ten z parametru. Tím jsme zajistili, že funkce dynamicky zpracovává různé názvy souborů podle vstupu, který obdrží.
Nyní, když máme naši funkci, pojďme uzavřít tuto první metodu a zavolat funkci pro naše data.
Sloučení soborů #1
V této fázi stačí získat názvy souborů a pro každý zavolat funkci. Teoreticky to může být i seznam názvu souborů v textové podobě. Nemusíme totiž využívat funkci Sharepoint Folder, abychom tyto názvy získali, protože vše děje uvnitř naší funkce.
Aby byl proces dynamičtější—zvláště pokud neznáme všechny názvy souborů předem—získáme názvy souborů přímo ze SharePointu.
Načtěte složku SharePoint stejným způsobem jako dříve ⚓. Poté filtrujte soubory podle vzoru, který jste přiřadili k názvům souborů.
Zvolil jsem takové soubory, které obsahují "yearly_file_excel_".
Máme naše názvy souborů, takže se zbavme ostatních sloupců, protože je nebudeme potřebovat.
Nyní je vše připraveno. Pojďme zavolat naši funkci.
V záložce Add Column klikněte na Invoke Custom Function.
V novém dialogovém okně vyplňte pole následovně:
Název sloupce: Můžete zadat jakýkoliv název podle vašich preferencí.
Dotaz funkce: Vyberte funkci, kterou jsme vytvořili v předchozích krocích.
Hodnota pro parametr funkce: Zvolte sloupec "Name".
Pokud jste vše provedli správně, získáte tuto novou tabulku.
Posledním krokem je rozbalení sloupce "content" kliknutím na malé šipky v pravém horním rohu záhlaví sloupce. Zde si můžete vybrat, které sloupce chcete rozbalit. V našem případě rozbalíme vše
Nezapomeňte odškrtnout možnost "Use original column name as prefix", jinak dostanete názvy sloupců jako "content.Month" nebo "content.Revenue".
A to je finální výsledek! V této fázi byste měli přiřadit správné datové typy jednotlivým sloupcům. Dále možná budete chtít odstranit sloupec s názvem souboru, aby vám zůstaly pouze sloupce Month a Revenue (nebo jiné, které jsou relevantní pro vaši analýzu).
Upozornění: Tento postup načítá každý soubor přímo ze SharePointu, vyhledá konkrétní soubor, provede transformace a poté jej načte. Nevýhodou je, že pro každý soubor znovu prohledává složku SharePoint, což může při velkém množství souborů výrazně zpomalit celý proces. Pokud máte velký počet souborů, doporučuji zvážit Metodu č. 2 pro efektivnější přístup.
Změň vzorový soubor na funkci #2
V této metodě zopakujeme počáteční kroky z první metody. Abyste nemuseli skrolovat, přidám je i sem:
-- Začátek zkopírované metody 1 --
Tady přichází ta nejtěžší část—ale nebojte, nemusíte být žádný Hackerman, abyste to zvládli. Potřebujeme přistoupit ke kódu, který byl použit k vytvoření vzorového souboru, a upravit část odkazující se na název souboru tak, aby byl dynamický.
Pokud jste klikali jako já, dostane něco takového:
Upozornění: Přidal jsem komentáře a přejmenoval kroky pro lepší čitelnost. Ve vašem případě uvidíte jiné názvy kroků (a žádné komentáře), ale struktura zůstane stejná.
-- Konec zkopírované metody 1 --
V Metodě č. 2 zvolíme odlišný přístup k vytvoření funkce. Protože pracujeme se složkou SharePoint, můžeme využít její schopnosti poskytovat název souboru i jeho obsah ve stejném řádku. Díky tomu můžeme přímo extrahovat obsah, aniž bychom museli procházet celou složku SharePoint při hledání konkrétního souboru, což proces výrazně zefektivňuje.
Jak můžete vidět, zjednodušili jsme kód tím, že jsme zcela eliminovali potřebu názvu souboru. Místo toho jsme zavedli nový parametr "content", který přijímá binární data. Tato binární data představují skutečný obsah souboru, což nám umožňuje přímo s ním pracovat a provádět potřebné transformace.
Tento přístup je mnohem jednodušší z hlediska kódu, ale vyžaduje, aby dataset obsahoval další sloupec "Content" před voláním funkce.
Nyní, když máme naši funkci, pojďme dokončit tuto druhou metodu a zavolat funkci pro naše data.
Sloučení souborů #2
V této fázi se musíme vrátit do složky SharePoint a načíst jak názvy souborů, tak jejich odpovídající obsah. Názvy souborů jsou užitečné pro sledování, který soubor je právě zpracováván. Pokud si ale věříte a nepotřebujete identifikovat jednotlivé soubory, můžete pokračovat pouze se sloupcem "Content".
Načtěte složku SharePoint stejným způsobem jako dříve ⚓. Poté filtrujte soubory podle vzoru, který jste přiřadili k názvům souborů.
Zvolil jsem takové soubory, které obsahují "yearly_file_excel_".
Odstraňme nepotřebné sloupce a ponechme pouze sloupec "Content", který obsahuje data souboru, a sloupec "Name" pro lepší přehled o tom, který soubor je právě zpracováván.
Nyní zavolejme funkci.
V záložce Add Column klikněte na Invoke Custom Function.
V novém dialogovém okně vyplňte pole následovně:
Název sloupce: Můžete zadat jakýkoliv název podle vašich preferencí.
Dotaz funkce: Vyberte funkci, kterou jsme vytvořili v předchozích krocích.
Hodnota pro parametr funkce: Zvolte sloupec "Content".
Pokud jste vše provedli správně, získáte tuto novou tabulku.
Posledním krokem je rozbalení sloupce "content" kliknutím na malé šipky v pravém horním rohu záhlaví sloupce. Zde si můžete vybrat, které sloupce chcete rozbalit. V našem případě rozbalíme vše
Nezapomeňte odškrtnout možnost "Use original column name as prefix", jinak dostanete názvy sloupců jako "content.Month" nebo "content.Revenue".
A to je finální výsledek! V této fázi byste měli přiřadit správné datové typy jednotlivým sloupcům. Dále můžete zvážit odstranění sloupců Name a Content, aby zůstaly pouze sloupce Month a Revenue (nebo jiné relevantní pro vaši analýzu).
Rozdíly ve výkonu mezi Metodou č. 1 a Metodou č. 2 jsou následující: Na vzorku 99 souborů, každý o 12 řádcích, trvala Metoda č. 1 na mém zařízení 53 sekund, zatímco Metoda č. 2 pouze 25 sekund. S rostoucím počtem souborů bude tento rozdíl ještě výraznější.
Shrnutí
V tomto článku jsem vám ukázal, jak si můžete vytvořit vlastní nástroj pro kombinování souborů s možností jej kdykoliv upravit, aniž byste museli vytvářet mnoho dalších komponentů. Představil jsem vám také dvě metody, jak tohoto cíle dosáhnout. Díky těmto znalostem nejste omezeni pouze na data ze SharePointu—můžete rozšířit koncept „seznamu věcí“ a přístup „pro každou, něco udělej“ na jakýkoliv typ dat.
Jako cvičení si zkuste vytvořit několik Excelových souborů, z nichž každý obsahuje více listů. Poté zkombinujte listy v každém souboru a nakonec spojte všechny soubory dohromady.
Děkuji, že jste si článek přečetli!
תגובות