- Med Power Query kan du konsolidere flere filer fra en lokal mappe eller en skymappe så lenge de deler samme dataskjema.
- Å bruke en eksempelfil gjør det enklere å bruke massetransformasjoner, for eksempel å fjerne øverste rader eller endre overskrifter.
- Det finnes begrensninger når kolonner ikke samsvarer nøyaktig, noe som krever manuell opprydding eller bruk av VBA-skript for komplekse tilfeller.
Tenk deg at du har en haug med månedlige eller ukentlige rapporter spredt utover mapper, og i stedet for å kaste bort morgenen med å kopiere og lime inn data, kan du samle alt i ett enkelt Excel-regneark med bare et par klikk. Det er nettopp her Power Query kommer inn i bildet, et verktøy som i hovedsak er høyre hånd for alle som jobber med data, slik at vi kan konsolidere filer som deler samme struktur uten problemer.
Det kuleste med dette systemet er at det ikke begrenser deg til en lokal mappe på PC-en din; du kan hente data fra SharePoint-nettsteder, Azure Data Lake eller Azure-blobs . Nøkkelen til at dette fungerer knirkefritt er at filene har samme skjema, noe som betyr at kolonnene samsvarer, selv om Power Query er smart nok til å hjelpe med å rydde opp i dataene hvis de inneholder støy eller unødvendige rader.
Den trinnvise kombinasjonsprosessen
Når du kobler til en datakilde, er det første du ser filsystemvisningen. Her er den viktigste kolonnen Innhold, som inneholder den binære representasjonen av hvert dokument. Hvis du klikker på en av disse binære verdiene, vil programmet prøve å avgjøre om det er en CSV-, JSON- eller Excel-fil for å kunne åpne den. For å kombinere hele bunken har du to alternativer: klikk på Slå sammen-knappen direkte, eller, hvis du er mer nøyaktig, velg Transformer data for å filtrere fillisten (for eksempel for å ignorere bestemte mapper) før du fortsetter med sammenslåingen.
Når du åpner dialogboksen for sammenslåing, bruker Power Query en eksempelfil til å analysere strukturen . Som standard velger den den første filen i listen, men du kan endre den hvis det finnes en annen som representerer datasettet bedre. Her kan du velge alternativet for å hoppe over filer med feil , noe som er en livredder når en rapport er skadet eller tom, og forhindrer at hele prosessen stopper.
Utdatahåndtering og datarensing
Etter at sammenslåingen er godkjent, utfører systemet litt teknisk magi bak kulissene: det oppretter en eksempelspørring og en parameterisert funksjon. Dette betyr at eventuelle endringer du gjør i eksempelfilen automatisk blir replikert til alle andre filer i mappen. Hvis CSV-filene dine for eksempel har fire rader med unødvendige overskrifter før de faktiske dataene, kan du gå til transformasjonsspørringen og bruke alternativet for å fjerne de øverste radene for å rydde opp i det rotet.
Etter at du har renset radene, er det viktig å bruke funksjonen « Bruk den første raden som overskrifter» . Husk at når du gjør dette, legger Power Query vanligvis til et automatisk trinn for datatypekonvertering. Hvis du ser en feilmelding om at kolonnen «Kolonne1» ikke finnes når du går tilbake til hovedspørringen, trenger du ikke bekymre deg. Dette er normalt fordi du har gitt nytt navn til kolonnene i eksemplet , og det påfølgende trinnet gjenkjenner ikke lenger de gamle navnene. Bare slett det feilaktige trinnet fra ruten Anvendte trinn, så vil alt gå tilbake til det normale.
Vanlige utfordringer og avanserte alternativer
Noen ganger er ikke livet så enkelt, og vi støter på filer som ikke er identiske. Hvis du for eksempel har ServiceNow-rapporter der én overskrift heter «resolved_on» og en annen «closed_on», kan Power Query gi deg problemer fordi den krever en konsistent struktur . Hvis eksempelfilen ikke samsvarer nøyaktig med resten, kan verktøyet utelate informasjonen fra de ikke-samsvarende filene eller generere importfeil.
I tilfeller der strukturen er fullstendig kaotisk eller datavolumet er enormt (over én million Excel-rader), finnes det andre alternativer. Noen brukere velger VBA-skript ved hjelp av CSV-grensesnitt , som lar filer slås sammen uten at overskrifter gjentas, uavhengig av om felt- eller postskilletegnet varierer fra fil til fil. Selv om Windows-skallet er ekstremt raskt, feiler det ofte når det håndterer innebygde linjeskift, mens en robust VBA-løsning ikke overbelaster RAM og kan håndtere millioner av poster med letthet.
For å fullføre prosessen, når dataene er kombinert i Power Query, husk å tilordne riktig datatype til hver kolonne (dato for datoer, valuta for inntekt osv.) før du laster inn den endelige tabellen. Du kan bekrefte at alt er riktig ved å filtrere kildenavn-kolonnen for å sikre at alle filene i mappen er behandlet.
Muligheten til å automatisere sammenkoblingen av flere CSV-filer lar deg gå fra kjedelig manuelt arbeid til en profesjonell arbeidsflyt, enten ved å utnytte fleksibiliteten til Power Query for datarensing eller ty til VBA når strukturer er inkompatible eller volumer overskrider grensene for regneark.