- Met Power Query kunt u meerdere bestanden uit een lokale of cloudmap samenvoegen, zolang ze maar hetzelfde gegevensschema delen.
- Door een voorbeeldbestand te gebruiken, is het eenvoudiger om批量bewerkingen uit te voeren, zoals het verwijderen van bovenste rijen of het wijzigen van kopteksten.
- Er zijn beperkingen wanneer kolommen niet exact overeenkomen, waardoor handmatige correcties of het gebruik van VBA-scripts nodig zijn voor complexe gevallen.
Stel je voor dat je een heleboel maandelijkse of wekelijkse rapporten verspreid over verschillende mappen hebt staan, en in plaats van je ochtend te verspillen aan het hectisch kopiëren en plakken van gegevens, zou je alles met een paar klikken in één Excel-spreadsheet kunnen samenvoegen. Precies hier komt Power Query van pas, een tool die in feite de rechterhand is van iedereen die met data werkt, waarmee we bestanden met dezelfde structuur moeiteloos kunnen consolideren.
Het mooiste aan dit systeem is dat je niet beperkt bent tot een lokale map op je pc; je kunt gegevens ophalen uit SharePoint-sites, Azure Data Lake of Azure blobs . De sleutel tot een soepele werking is dat de bestanden hetzelfde schema hebben, oftewel dat de kolommen overeenkomen. Power Query is echter slim genoeg om de gegevens op te schonen als ze ruis of onnodige rijen bevatten.
Het stapsgewijze combinatieproces
Wanneer u verbinding maakt met een gegevensbron, ziet u eerst de bestandsweergave. De belangrijkste kolom is 'Inhoud', die de binaire representatie van elk document bevat. Als u op een van deze binaire waarden klikt, probeert het programma te bepalen of het een CSV-, JSON- of Excel-bestand is om het te openen. Om de hele batch samen te voegen, hebt u twee opties: u kunt direct op de knop 'Samenvoegen' klikken, of, als u nauwkeuriger te werk wilt gaan, ' Gegevens transformeren' selecteren om de bestandenlijst te filteren (bijvoorbeeld om specifieke mappen te negeren) voordat u verdergaat met het samenvoegen.
Zodra u het dialoogvenster 'Samenvoegen' opent, gebruikt Power Query een voorbeeldbestand om de structuur te analyseren . Standaard selecteert het het eerste bestand in de lijst, maar u kunt dit wijzigen als er een ander bestand is dat de gegevensset beter vertegenwoordigt. Hier kunt u de optie selecteren om bestanden met fouten over te slaan . Dit is erg handig wanneer een rapport beschadigd of leeg is, zodat het hele proces niet vastloopt.
Outputbeheer en gegevensopschoning
Nadat de samenvoeging is geaccepteerd, voert het systeem op de achtergrond een aantal technische bewerkingen uit: het genereert een voorbeeldquery en een geparameteriseerde functie. Dit betekent dat alle wijzigingen die u aan het voorbeeldbestand aanbrengt, automatisch worden doorgevoerd in alle andere bestanden in de map. Als uw CSV-bestanden bijvoorbeeld vier rijen met onnodige kopteksten vóór de eigenlijke gegevens bevatten, kunt u in de transformatiequery de optie gebruiken om de bovenste rijen te verwijderen en zo de overbodige informatie op te ruimen.
Na het opschonen van de rijen is het essentieel om de functie ' De eerste rij als kopteksten gebruiken' toe te passen . Houd er rekening mee dat Power Query in dat geval meestal een automatische conversie van gegevenstypen uitvoert. Als u bij terugkeer naar de hoofdquery een foutmelding ziet dat kolom 'Kolom1' niet kan worden gevonden, hoeft u zich geen zorgen te maken; dit is normaal omdat u de kolommen in het voorbeeld hebt hernoemd en de volgende stap de oude namen niet meer herkent. Verwijder de betreffende stap in het deelvenster 'Toegepaste stappen' en alles zal weer normaal functioneren.
Algemene uitdagingen en geavanceerde alternatieven
Soms is het leven niet zo eenvoudig en komen we bestanden tegen die niet identiek zijn. Als je bijvoorbeeld ServiceNow-rapporten hebt waarin de ene kop 'resolved_on' heet en de andere 'closed_on', kan Power Query problemen opleveren omdat het een consistente structuur vereist . Als het voorbeeldbestand niet exact overeenkomt met de rest, kan de tool de informatie uit de afwijkende bestanden weglaten of importfouten genereren.
Voor gevallen waarin de structuur volledig chaotisch is of het datavolume enorm is (meer dan een miljoen Excel-rijen), zijn er andere opties. Sommige gebruikers kiezen voor VBA-scripts met CSV-interfaces , waarmee bestanden kunnen worden samengevoegd zonder dat kopteksten worden herhaald, ongeacht of het veld- of recordscheidingsteken per bestand verschilt. Hoewel de Windows Shell extreem snel is, faalt deze vaak bij het verwerken van ingebedde regeleinden, terwijl een robuuste VBA-oplossing het RAM-geheugen niet overbelast en miljoenen records met gemak kan verwerken.
Om het proces te voltooien, moet u, zodra de gegevens in Power Query zijn gecombineerd, het juiste gegevenstype aan elke kolom toewijzen (Datum voor datums, Valuta voor inkomsten, enz.) voordat u de uiteindelijke tabel laadt. U kunt controleren of alles correct is door de kolom Bronnaam te filteren om er zeker van te zijn dat alle bestanden in de map succesvol zijn verwerkt.
De mogelijkheid om het samenvoegen van meerdere CSV-bestanden te automatiseren, stelt u in staat om van tijdrovend handmatig werk over te stappen naar een professionele workflow. Dit kan bijvoorbeeld door de flexibiliteit van Power Query te benutten voor het opschonen van gegevens, of door VBA te gebruiken wanneer structuren incompatibel zijn of de hoeveelheden gegevens de limieten van spreadsheets overschrijden.