Power Query is gebaseerd op een ETL-motor (Extract, Transform, Load) die geïntegreerd is in Excel en die M-code genereert bij elke bewerking in de editor. Het begrijpen van dit mechanisme verandert radicaal de manier waarop je gegevens schoonmaakt en consolideert, veel verder dan het eenvoudige “slepen en neerzetten” dat de meeste tutorials beschrijven.
M-taal en toegepaste stappen in Power Query: wat de editor verbergt
Elke transformatie die in de Power Query-editor wordt uitgevoerd, produceert een regel M-code die wordt opgeslagen als toegepaste stap in het zijpaneel. De grafische interface vergemakkelijkt de gebruikelijke bewerkingen, maar verbergt de onderliggende functionele logica.
De M-taal werkt met lui evalueren (lazy evaluation): een stap wordt alleen berekend als een volgende stap deze nodig heeft. We raden aan om regelmatig de formulebalk te controleren om te zien wat de editor genereert, omdat sommige transformaties impliciete typeconversies invoegen die de verwerking van grote bestanden vertragen.
De volgorde van de stappen heeft een directe impact op de prestaties. Rijen filteren voordat je twee query’s samenvoegt, vermindert het datavolume in het geheugen aanzienlijk. Omgekeerd dwingt het plaatsen van een sortering vóór een filter de motor om rijen te ordenen die vervolgens worden verwijderd. We kunnen trouwens alles leren over Power Query met Excel door de structuur van deze toegepaste stappen te onderzoeken in plaats van alleen op de visuele interface te blijven.
Privacy-niveaus en gegevensverbindingen beheren
Privacy-niveaus zijn de meest voorkomende bron van fouten in bedrijven wanneer meerdere bronnen in Power Query worden gecombineerd. Er zijn drie niveaus: Publiek, Organisatie, Privé. Deze bepalen of de motor gegevens uit verschillende bronnen mag combineren.
Wanneer twee bronnen incompatibele niveaus hebben, blokkeert Power Query de query met een bericht “Formula.Firewall”. De verleiding is dan om “Privacy-niveaus negeren” aan te vinken in de opties. In een persoonlijke omgeving heeft dit geen gevolgen. In een professionele context schakelt deze optie de barrière uit die voorkomt dat gevoelige gegevens per ongeluk naar een externe bron worden verzonden.
Microsoft heeft zijn recente documentatie over dit punt versterkt. We zien dat bedrijven die Power Query op gedeelde werkplekken implementeren, er baat bij hebben om de privacy-niveaus op het niveau van de gegevensbron te definiëren, niet op het niveau van het werkboek, om te voorkomen dat een gebruiker een instelling wijzigt zonder de impact ervan te begrijpen.
Power Query-connectoren: houd de afschrijvingen in de gaten
Power Query biedt connectoren naar tientallen bronnen (SQL-databases, CSV-bestanden, web-API’s, cloudservices). Deze catalogus evolueert. Microsoft heeft bijvoorbeeld de QuickBooks-connector afgeschreven, met de aanbeveling om bestaande stromen naar andere methoden te migreren voordat deze volledig wordt verwijderd.
Regelmatig de lijst met actieve connectoren controleren voorkomt dat je op een dag ontdekt dat een geautomatiseerde query niet meer werkt. Een afgeschreven connector blijft soms nog enkele maanden werken, maar stopt zonder waarschuwing bij een update van Excel.
Stabiliteit van de Power Query-editor afhankelijk van de versie van Excel
De tutorials gaan uit van een stabiele en responsieve Power Query-editor. De werkelijkheid is genuanceerder. Recente gebruikersfeedback meldt vastlopers van de editor (freezes, “Not Responding”) op bepaalde versies of bèta-kanalen van Excel.
Verschillende factoren beïnvloeden de stabiliteit:
- Het Office-updatekanaal (Current Channel, Monthly Enterprise, Semi-Annual) bepaalt de versie van de ingebouwde Power Query-motor. Snelle kanalen ontvangen nieuwe functies, maar ook potentiële regressies.
- Het volume aan gegevens dat in de preview van de editor wordt geladen, belast het geheugen. Werken met een beperkte preview (de eerste honderden rijen) en het volledige gegevensset pas laden bij de uiteindelijke laadactie verbetert de soepelheid.
- Geneste query’s met meerdere referentieniveaus tussen tabellen verhogen de complexiteit van de afhankelijkheidsgrafiek, wat de evaluatie in de editor vertraagt.
We raden aan om de versie van Excel op een stabiel kanaal te vergrendelen in een productieomgeving en updates op een pilotstation te testen voordat ze worden uitgerold.
Webextractie met Power Query: detectie van tabellen en limieten
Importeren vanaf een webpagina blijft een van de meest onderbenutte functies van Power Query. De webconnector detecteert automatisch de tabelstructuren (HTML table-tags) en biedt aan deze als afzonderlijke query’s te laden.
Recente ontwikkelingen hebben de assistentie bij het detecteren van tabellen verbeterd, zelfs op pagina’s met gedeeltelijk onregelmatige markup. Echter, gegevens die dynamisch door JavaScript worden gegenereerd, worden niet vastgelegd door de native webconnector, die alleen werkt met de statische DOM van de pagina.
Structureren van transformaties voor automatische vernieuwing
Een van de belangrijkste voordelen van Power Query is de mogelijkheid om alle query’s van een werkboek met één klik te vernieuwen. Om ervoor te zorgen dat deze vernieuwing zonder handmatige tussenkomst werkt, moet elke query naar een toegankelijke bron wijzen (geldige netwerkpad, actieve URL, verbonden database).
Een klassieke valkuil: een query bouwen die naar een bestand op het lokale bureaublad wijst, en vervolgens het werkboek delen. De query faalt omdat het pad niet bestaat op de computer van de ontvanger. Het opslaan van de bronnbestanden op een gedeelde netwerklocatie of een gesynchroniseerde map lost dit probleem op vanaf het ontwerp.
Power Query op Excel dekt tegenwoordig een bereik dat verder gaat dan alleen het schoonmaken van kolommen. Het beheer van privacy-niveaus, het volgen van afgeschreven connectoren en de keuze van het updatekanaal zijn operationele parameters die de betrouwbaarheid van gegevensstromen op lange termijn bepalen.