- Power Query umožňuje konsolidovať viacero súborov z lokálneho alebo cloudového priečinka, pokiaľ zdieľajú rovnakú dátovú schému.
- Použitie vzorového súboru uľahčuje hromadné transformácie, ako je napríklad odstránenie horných riadkov alebo zmena hlavičiek.
- Existujú obmedzenia, keď sa stĺpce úplne nezhodujú, čo si vyžaduje manuálne čistenie alebo použitie skriptov VBA v zložitých prípadoch.
Predstavte si, že máte kopu mesačných alebo týždenných prehľadov roztrúsených po rôznych priečinkoch a namiesto toho, aby ste strácali ráno zúfalým kopírovaním a vkladaním údajov, môžete všetko zjednotiť do jednej excelovskej tabuľky len niekoľkými kliknutiami. Presne tu prichádza na rad Power Query, nástroj, ktorý je v podstate pravou rukou každého, kto pracuje s údajmi a umožňuje nám bez námahy konsolidovať súbory s rovnakou štruktúrou.
Najúžasnejšie na tomto systéme je, že vás neobmedzuje na lokálny priečinok v počítači; dáta môžete sťahovať z lokalít SharePointu, Azure Data Lake alebo Azure blobov . Kľúčom k bezproblémovému fungovaniu je, aby súbory mali rovnakú schému, čo znamená, že stĺpce sa zhodujú, hoci Power Query je dostatočne inteligentný na to, aby pomohol vyčistiť dáta, ak obsahujú akýkoľvek šum alebo nepotrebné riadky.
Postupný proces kombinovania
Keď sa pripojíte k zdroju údajov, prvá vec, ktorú uvidíte, je zobrazenie súborového systému. Najdôležitejším stĺpcom je tu Obsah, ktorý obsahuje binárnu reprezentáciu každého dokumentu. Ak kliknete na jednu z týchto binárnych hodnôt, program sa pokúsi určiť, či ide o súbor CSV, JSON alebo Excel, aby ho mohol otvoriť. Ak chcete zlúčiť celú dávku, máte dve možnosti: kliknúť priamo na tlačidlo Zlúčiť alebo, ak ste dôkladnejší, vybrať možnosť Transformovať údaje, aby ste filtrovali zoznam súborov (napríklad aby ste ignorovali konkrétne priečinky) pred pokračovaním v zlúčení.
Po otvorení dialógového okna zlúčenia Power Query použije vzorový súbor na analýzu štruktúry . Predvolene vyberie prvý súbor v zozname, ale môžete ho zmeniť, ak existuje iný, ktorý lepšie reprezentuje množinu údajov. Tu môžete vybrať možnosť preskočiť súbory s chybami , čo je záchrana, keď je zostava poškodená alebo prázdna, a zabráni sa tak zastaveniu celého procesu.
Správa výstupov a čistenie dát
Po prijatí zlúčenia systém v zákulisí vykoná technické kúzlo: vytvorí vzorový dotaz a parametrizovanú funkciu. To znamená, že všetky zmeny, ktoré vykonáte vo vzorovom súbore, sa automaticky replikujú do všetkých ostatných súborov v priečinku. Ak napríklad vaše súbory CSV obsahujú štyri riadky nepotrebných hlavičiek pred skutočnými údajmi, môžete prejsť na transformačný dotaz a použiť možnosť odstránenia horných riadkov , aby ste tento neporiadok vyčistili.
Po vyčistení riadkov je nevyhnutné použiť funkciu „ Použiť prvý riadok ako hlavičky“ . Majte na pamäti, že keď to urobíte, Power Query zvyčajne pridá krok automatickej konverzie dátového typu. Ak sa po návrate do hlavného dotazu zobrazí chyba, že stĺpec „Stĺpec1“ sa nenašiel, nebojte sa; je to normálne, pretože ste stĺpce v príklade premenovali a nasledujúci krok už nerozpoznáva staré názvy. Jednoducho odstráňte tento chybný krok z tably Použité kroky a všetko sa vráti do normálu.
Bežné výzvy a pokročilé alternatívy
Niekedy život nie je taký jednoduchý a narazíme na súbory, ktoré nie sú identické. Napríklad, ak máte zostavy ServiceNow, kde jedna hlavička sa nazýva „resolved_on“ a druhá „closed_on“, Power Query vám môže spôsobiť problémy, pretože vyžaduje konzistentnú štruktúru . Ak vzorový súbor presne nezodpovedá zvyšku, nástroj môže vynechať informácie z nezhodných súborov alebo vyvolať chyby importu.
V prípadoch, keď je štruktúra úplne chaotická alebo objem dát je masívny (presahuje jeden milión riadkov programu Excel), existujú aj iné možnosti. Niektorí používatelia sa rozhodnú pre skripty VBA pomocou rozhraní CSV , ktoré umožňujú spájanie súborov bez opakovania hlavičiek, bez ohľadu na to, či sa oddeľovač polí alebo záznamov v jednotlivých súboroch líši. Hoci je prostredie Windows Shell mimoriadne rýchle, často zlyháva pri spracovaní vložených zalomení riadkov, zatiaľ čo robustné riešenie VBA nepreťažuje pamäť RAM a dokáže bez problémov spracovať milióny záznamov.
Na dokončenie procesu nezabudnite po skombinovaní údajov v Power Query priradiť správny typ údajov ku každému stĺpcu (dátum pre dátumy, mena pre príjem atď.) pred načítaním finálnej tabuľky. Správnosť všetkých údajov môžete overiť filtrovaním stĺpca Názov zdroja, aby ste sa uistili, že všetky súbory v priečinku boli úspešne spracované.
Možnosť automatizovať spájanie viacerých súborov CSV vám umožňuje prejsť od zdĺhavej manuálnej práce k profesionálnemu pracovnému postupu, či už využitím flexibility Power Query na čistenie údajov alebo použitím VBA, keď sú štruktúry nekompatibilné alebo objemy presahujú limity tabuliek.