Rychlé sloučení souborů v Power Query

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 budeš 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.

Disclaimer & update: Sharepoint.Files není optimální pro větší množství dat, mrkni na (ještě) lepší způsob, jak nahrávat data ze Sharepointu zde.

Nativní “slučovač” souborů

Pravděpodobně ses už setkal 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íš tolik řešit, pokud provádíš jen jednoduché transformace nebo plánuješ 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ůžeš 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 tebe, 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 ti 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ůžeš aplikovat i s jakýmkoliv jiným konektorem podobného charakteru.

Pojďme navigovat Power Query do našeho SharePoint Folderu.

Nyní zadej Sharepoint stránku, ke které chceš přistupovat, buď ručně, nebo pomocí parametru. Pro větší flexibilitu a snazší manipulaci obvykle doporučuji použít parametr. Mezi metodami můžeš přepínat pomocí ikony vlevo.

V dalším okně se vyhni lákavému, zelenému okénku a klikni 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 svoje 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 klikneš 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ěj 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 budeš muset znovu upravit po sloučení souborů. Přesto stojí za to je zkontrolovat, aby ses ujistil, že vše vypadá správně (i když tento krok později odstraníš).

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 ti dvě metody:

1. Přímočarý, méně optimalizovaný (vhodné pro malý počet souborů)
2. S extra krokem navíc, ale optimalizovanější (vhodné jak pro malé, tak velké množství souborů)

Změň vzorový soubor na funkci #1

Tady přichází ta nejtěžší část - ale neboj, nemusíš být žádný Hackerman, abys to zvládl. 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 jsi klikal jako já, dostaneš něco takového:

Upozornění: Přidal jsem komentáře a přejmenoval kroky pro lepší čitelnost. Ve tvém případě uvidíš 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ůžeš 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čti složku SharePoint stejným způsobem jako dříve. Poté filtruj soubory podle vzoru, který jsi přiřadil 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 klikni na Invoke Custom Function.

V novém dialogovém okně vyplň pole následovně:

• Název sloupce: Můžeš zadat jakýkoliv název podle svých preferencí.
• Dotaz funkce: Vyber funkci, kterou jsme vytvořili v předchozích krocích.
• Hodnota pro parametr funkce: Zvol sloupec “Name”.

Pokud jsi vše provedl správně, získáš 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ůžeš vybrat, které sloupce chceš rozbalit. V našem případě rozbalíme vše

Nezapomeň odškrtnout možnost “Use original column name as prefix”, jinak dostaneš názvy sloupců jako “content.Month” nebo “content.Revenue”.

A to je finální výsledek! V této fázi bys měl přiřadit správné datové typy jednotlivým sloupcům. Dále možná budeš chtít odstranit sloupec s názvem souboru, aby ti zůstaly pouze sloupce Month a Revenue (nebo jiné, které jsou relevantní pro tvoji 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áš 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. Abys nemusel skrolovat, přidám je i sem:

— Začátek zkopírované metody 1 —

Tady přichází ta nejtěžší část - ale neboj, nemusíš být žádný Hackerman, abys to zvládl. 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 jsi klikal jako já, dostaneš něco takového:

Upozornění: Přidal jsem komentáře a přejmenoval kroky pro lepší čitelnost. Ve tvém případě uvidíš 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ůžeš 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ěříš a nepotřebuješ identifikovat jednotlivé soubory, můžeš pokračovat pouze se sloupcem “Content”.

Načti složku SharePoint stejným způsobem jako dříve. Poté filtruj soubory podle vzoru, který jsi přiřadil 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 klikni na Invoke Custom Function.

V novém dialogovém okně vyplň pole následovně:

• Název sloupce: Můžeš zadat jakýkoliv název podle svých preferencí.
• Dotaz funkce: Vyber funkci, kterou jsme vytvořili v předchozích krocích.
• Hodnota pro parametr funkce: Zvol sloupec “Content”.

Pokud jsi vše provedl správně, získáš 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ůžeš vybrat, které sloupce chceš rozbalit. V našem případě rozbalíme vše

Nezapomeň odškrtnout možnost “Use original column name as prefix”, jinak dostaneš názvy sloupců jako “content.Month” nebo “content.Revenue”.

A to je finální výsledek! V této fázi bys měl přiřadit správné datové typy jednotlivým sloupcům. Dále můžeš zvážit odstranění sloupců Name a Content, aby zůstaly pouze sloupce Month a Revenue (nebo jiné relevantní pro tvoji 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 ti ukázal, jak si můžeš vytvořit vlastní nástroj pro kombinování souborů s možností jej kdykoliv upravit, aniž bys musel vytvářet mnoho dalších komponentů. Představil jsem ti také dvě metody, jak tohoto cíle dosáhnout. Díky těmto znalostem nejsi omezen pouze na data ze SharePointu; můžeš 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 zkus vytvořit několik Excelových souborů, z nichž každý obsahuje více listů. Poté zkombinuj listy v každém souboru a nakonec spoj všechny soubory dohromady.

Děkuji, že jsi si článek přečetl!