Best practices voor het werken met Power Query

Dit artikel bevat enkele tips en trucs om het meeste uit uw data-wrangling-ervaring in uw Power Query.

De juiste connector kiezen

Power Query biedt een groot aantal gegevensconnectoren. Deze connectors variëren van gegevensbronnen zoals TXT-, CSV- en Excel-bestanden tot databases zoals Microsoft SQL Server en populaire SaaS-services zoals Microsoft Dynamics 365 en Salesforce. Als uw gegevensbron niet wordt vermeld in het venster Gegevens verzamelen, kunt u altijd de ODBC- of OLEDB-connector gebruiken om verbinding te maken met uw gegevensbron.

Als u de beste connector voor de taak gebruikt, krijgt u de beste ervaring en prestaties. Als u bijvoorbeeld de SQL Server-connector gebruikt in plaats van de ODBC-connector wanneer u verbinding maakt met een SQL Server-database, beschikt u niet alleen over een veel betere get data-ervaring, maar biedt de SQL Server-connector ook functies die uw ervaring en prestaties kunnen verbeteren, zoals het vouwen van query's. Zie query folding (Query Folding) voor Power Query over het vouwen van query's.

Elke gegevensconnector volgt een standaardervaring zoals uitgelegd in Gegevens verkrijgen. Deze gestandaardiseerde ervaring heeft een fase met de naam Data Preview. In deze fase krijgt u een gebruiksvriendelijk venster om de gegevens te selecteren die u uit uw gegevensbron wilt halen, als de connector dit toestaat, en een eenvoudig gegevensvoorbeeld van die gegevens. U kunt zelfs meerdere gegevenssets uit uw gegevensbron selecteren via het venster Navigator, zoals wordt weergegeven in de volgende afbeelding.

Venster voorbeeldnavigator.

Notitie

Zie Connectors in Power Query voor de volledige lijst met beschikbare connectors in Power Query.

Vroeg filteren

Het is altijd raadzaam om uw gegevens te filteren in de vroege fasen van uw query of zo vroeg mogelijk. Sommige connectors profiteren van uw filters via het vouwen van query's, zoals beschreven in Power Query query folding. Het is ook een best practice gegevens te filteren die niet relevant zijn voor uw case. Hierdoor kunt u zich beter richten op uw taak door alleen gegevens weer te geven die relevant zijn in de sectie gegevensvoorbeelden.

U kunt het menu automatisch filteren gebruiken om een afzonderlijke lijst weer te geven met de waarden in uw kolom om de waarden te selecteren die u wilt behouden of eruit wilt filteren. U kunt ook de zoekbalk gebruiken om de waarden in uw kolom te vinden.

Menu Automatisch filteren in Power Query.

U kunt ook profiteren van de typespecifieke filters, zoals In de vorige voor een datum-, datum/tijd- of zelfs datumtijdzonekolom.

typ specifiek filter voor een datumkolom.

Deze typespecifieke filters kunnen u helpen bij het maken van een dynamisch filter dat altijd gegevens op haalt uit het vorige x aantal seconden, minuten, uren, dagen, weken, maanden, kwartalen of jaren, zoals wordt getoond in de volgende afbeelding.

Staat in het vorige datumspecifieke filter.

Notitie

Zie Filteren op waarden voor meer informatie over het filteren van uw gegevens op basis van waarden uit een kolom.

Dure bewerkingen als laatste uitvoeren

Voor bepaalde bewerkingen is het lezen van de volledige gegevensbron vereist om resultaten te retourneren. De preview-versie in de Power Query Editor is dus traag. Als u bijvoorbeeld een sorteert, is het mogelijk dat de eerste paar gesorteerde rijen aan het einde van de brongegevens staan. Als u dus resultaten wilt retourneren, moet de sorteerbewerking eerst alle rijen lezen.

Andere bewerkingen (zoals filters) hoeven niet alle gegevens te lezen voordat er resultaten worden retourneren. In plaats daarvan worden de gegevens op een zogenaamde streaming-manier gebruikt. De gegevens worden 'gestreamd' door en de resultaten worden langs de weg geretourneerd. In de Power Query Editor hoeven dergelijke bewerkingen alleen voldoende brongegevens te lezen om de preview te vullen.

Voer dergelijke streamingbewerkingen zo mogelijk eerst uit en voer als laatste nog duurdere bewerkingen uit. Zo minimaliseert u de hoeveelheid tijd die u nodig hebt om te wachten tot de preview wordt weergegeven telkens wanneer u een nieuwe stap aan uw query toevoegt.

Tijdelijk werken met een subset van uw gegevens

Als het toevoegen van nieuwe stappen aan uw query in de Power Query-editor traag is, kunt u een bewerking 'Eerste rijen behouden' uitvoeren en het aantal rijen beperken dat u gebruikt. Nadat u alle stappen hebt toegevoegd die u nodig hebt, verwijdert u de stap 'Eerste rijen behouden'.

De juiste gegevenstypen gebruiken

Sommige functies in Power Query zijn contextueel voor het gegevenstype van de geselecteerde kolom. Wanneer u bijvoorbeeld een datumkolom selecteert, zijn de beschikbare opties onder de kolomgroep Datum en tijd in het menu Kolom toevoegen beschikbaar. Maar als de kolom geen gegevenstypeset heeft, worden deze opties grijs.

Typ een specifieke optie in het menu Kolom toevoegen.

Een vergelijkbare situatie treedt op voor de typespecifieke filters, omdat deze specifiek zijn voor bepaalde gegevenstypen. Als uw kolom niet het juiste gegevenstype heeft gedefinieerd, zijn deze typespecifieke filters niet beschikbaar.

typ specifiek filter voor een datumkolom.

Het is van cruciaal belang dat u altijd met de juiste gegevenstypen voor uw kolommen werkt. Wanneer u werkt met gestructureerde gegevensbronnen zoals databases, worden de gegevenstypegegevens uit het tabelschema in de database gehaald. Maar voor niet-gestructureerde gegevensbronnen, zoals TXT- en CSV-bestanden, is het belangrijk dat u de juiste gegevenstypen instuurt voor de kolommen die afkomstig zijn van die gegevensbron. Standaard biedt Power Query automatische detectie van gegevenstype voor ongestructureerde gegevensbronnen. U kunt meer lezen over deze functie en hoe u hiermee gegevenstypen kunt gebruiken.

Notitie

Zie Gegevenstypen voor meer informatie over het belang van gegevenstypen en hoe u er mee kunt werken.

Uw gegevens verkennen

Voordat u begint met het voorbereiden van uw gegevens en het toevoegen van nieuwe transformatiestappen, raden we u aan de hulpprogramma's Power Query gegevensprofilering in te stellen om eenvoudig informatie over uw gegevens te ontdekken.

Hulpprogramma's voor gegevensvoorbeelden of gegevensprofilering in Power Query.

Met deze hulpprogramma's voor gegevensprofilering kunt u uw gegevens beter begrijpen. De hulpprogramma's bieden u kleine visualisaties die u informatie per kolom laten zien, zoals:

  • Kolomkwaliteit — Biedt een klein staafdiagram en drie indicatoren met de weergave van het aantal waarden in de kolom dat onder de categorieën geldige, fout- of lege waarden valt.
  • Kolomdistributie — Biedt een set visuals onder de namen van de kolommen die de frequentie en distributie van de waarden in elk van de kolommen laten zien.
  • Kolomprofiel — Biedt een uitgebreidere weergave van uw kolom en de bijbehorende statistieken.

U kunt ook communiceren met deze functies, zodat u uw gegevens kunt voorbereiden.

Opties voor de muisaanwijzer voor gegevenskwaliteit.

Notitie

Zie Hulpprogramma's voor gegevensprofilering voor meer informatie over de hulpprogramma's voor gegevensprofilering.

Uw werk documenteren

U wordt aangeraden uw query's te documenteren door de naam van uw query's te wijzigen of een beschrijving toe te voegen aan uw stappen, query's of groepen.

Hoewel Power Query automatisch een stapnaam voor u maakt in het deelvenster Toegepaste stappen, kunt u ook de naam van uw stappen wijzigen of een beschrijving toevoegen aan een van deze stappen.

Deelvenster Toegepaste stappen met gedocumenteerde stappen en beschrijving toegevoegd.

Notitie

Zie De lijst Toegepaste stappen gebruiken voor meer informatie over alle beschikbare functies en onderdelen in het deelvenster Toegepaste stappen.

Een modulaire benadering kiezen

Het is heel goed mogelijk om één query te maken die alle transformaties en berekeningen bevat die u mogelijk nodig hebt. Maar als de query een groot aantal stappen bevat, kan het een goed idee zijn om de query op te splitsen in meerdere query's, waarbij de ene query naar de volgende verwijst. Het doel van deze benadering is het vereenvoudigen en loskoppelen van transformatiefasen in kleinere delen, zodat ze gemakkelijker te begrijpen zijn.

Stel dat u een query hebt met de negen stappen die in de volgende afbeelding worden weergegeven.

Deelvenster Toegepaste stappen met gedocumenteerde stappen en beschrijving toegevoegd.

U kunt deze query in twee splitsen in de tabelstap Samenvoegen met prijzen. Op die manier is het gemakkelijker om de stappen te begrijpen die zijn toegepast op de verkoopquery vóór de samenvoeging. Als u deze bewerking wilt uitvoeren, klikt u met de rechtermuisknop op de tabelstap Samenvoegen met prijzen en selecteert u de optie Vorige uitpakken.

Extraheren uit de vorige stap.

Vervolgens wordt u gevraagd een dialoogvenster op te geven om uw nieuwe query een naam te geven. Hiermee wordt uw query effectief gesplitst in twee query's. Eén query heeft alle query's vóór de samenvoeging. De andere query heeft een eerste stap die verwijst naar uw nieuwe query en de rest van de stappen die u in de oorspronkelijke query hebt uitgevoerd uit de stap Samenvoegen met prijzen tabel omlaag.

Oorspronkelijke query na de actie uit de vorige stap extraheren.

U kunt ook gebruikmaken van het gebruik van query's die naar eigen goed willen verwijzen. Maar het is een goed idee om uw query's op een niveau te houden dat op het eerste gezicht niet erg lastig lijkt met zo veel stappen.

Notitie

Zie Inzicht in het deelvenster query's voor meer informatie over het verwijzen naar query's.

Groepen maken

Een uitstekende manier om uw werk georganiseerd te houden, is door gebruik te maken van het gebruik van groepen in het deelvenster query's.

Werken met groepen in Power Query.

Het enige doel van groepen is om u te helpen uw werk georganiseerd te houden door te dienen als mappen voor uw query's. U kunt indien nodig groepen binnen groepen maken. Het verplaatsen van query's tussen groepen is net zo eenvoudig als slepen en neerzetten.

Probeer uw groepen een betekenisvolle naam te geven die zinvol is voor u en uw case.

Notitie

Zie Inzicht in het deelvenster query's voor meer informatie over alle beschikbare functies en onderdelen in het deelvenster query's.

Toekomstige proofing-query's

Het is een topprioriteit om ervoor te zorgen dat u een query maakt die geen problemen zal hebben tijdens een toekomstige vernieuwing. Er zijn verschillende functies in Power Query ervoor te zorgen dat uw query bestand is tegen wijzigingen en zelfs kan worden vernieuwd wanneer sommige onderdelen van uw gegevensbron worden gewijzigd.

Het is een best practice om het bereik van uw query te definiëren met betrekking tot wat deze moet doen en waar deze rekening mee moet houden in termen van structuur, indeling, kolomnamen, gegevenstypen en andere onderdelen die u relevant acht voor het bereik.

Enkele voorbeelden van transformaties die u kunnen helpen om uw query bestand te maken tegen wijzigingen:

  • Als uw query een dynamisch aantal rijen met gegevens heeft, maar een vast aantal rijen dat als voettekst moet worden verwijderd, kunt u de functie Onderste rijen verwijderen gebruiken.

    Notitie

    Zie Een tabel filteren op rijpositie voor meer informatie over het filteren van uw gegevens op rijpositie.

  • Als uw query een dynamisch aantal kolommen heeft, maar u alleen specifieke kolommen uit uw gegevensset hoeft te selecteren, kunt u de functie Kolommen kiezen gebruiken.

    Notitie

    Zie Kolommen kiezen of verwijderen voor meer informatie over het kiezen of verwijderen van kolommen.

  • Als uw query een dynamisch aantal kolommen heeft en u alleen een subset van uw kolommen wilt losvaan draait, kunt u de functie Alleen draaitabel voor geselecteerde kolommen gebruiken.

    Notitie

    Zie Draaitabel verwijderen voor meer informatie over de opties om de draaitabel van uw kolommen op te maken.

  • Als uw query een stap bevat die het gegevenstype van een kolom wijzigt, maar sommige cellen fouten opleveren omdat de waarden niet voldoen aan het gewenste gegevenstype, kunt u de rijen verwijderen die foutwaarden hebben veroorzaakt.

    Notitie

    Zie Omgaan met fouten voor meer informatie over het werken met en omgaan met fouten.

Parameters gebruiken

Het maken van query's die dynamisch en flexibel zijn, is een best practice. Parameters in Power Query helpen u om uw query's dynamischer en flexibeler te maken. Een parameter fungeert als een manier om eenvoudig een waarde op te slaan en te beheren die op veel verschillende manieren opnieuw kan worden gebruikt. Het wordt echter vaker gebruikt in twee scenario's:

  • Stapargument — U kunt een parameter gebruiken als argument van meerdere transformaties die worden aangestuurd vanuit de gebruikersinterface.

    Selecteer parameter voor transformatieargument.

  • Argument Aangepaste functie — U kunt een nieuwe functie maken op basis van een query en verwijzen naar parameters als de argumenten van uw aangepaste functie.

    Functie maken.

De belangrijkste voordelen van het maken en gebruiken van parameters zijn:

  • Gecentraliseerde weergave van al uw parameters via het venster Parameters beheren.

    Het venster Parameters beheren.

  • Herbruikbaarheid van de parameter in meerdere stappen of query's.

  • Maakt het maken van aangepaste functies eenvoudig en eenvoudig.

U kunt zelfs parameters gebruiken in sommige argumenten van de gegevensconnectoren. U kunt bijvoorbeeld een parameter voor de servernaam maken wanneer u verbinding maakt met uw SQL Server database. Vervolgens kunt u die parameter in het dialoogvenster SQL Server database gebruiken.

SQL Server databasevenster met de parameter voor servernaam.

Als u de serverlocatie wijzigt, hoeft u alleen de parameter voor de servernaam bij te werken en worden uw query's bijgewerkt.

Notitie

Zie Parameters gebruiken voor meer informatie over het maken en gebruiken van parameters.

Herbruikbare functies maken

Als u in een situatie komt waarin u dezelfde set transformaties moet toepassen op verschillende query's of waarden, kan het nuttig zijn om een aangepaste Power Query-functie te maken die net zo vaak opnieuw kan worden gebruikt als u nodig hebt. Een Power Query functie is een toewijzing van een set invoerwaarden aan één uitvoerwaarde en wordt gemaakt op basis van systeemeigen M-functies en -operators.

Stel dat u meerdere query's of waarden hebt waarvoor dezelfde set transformaties is vereist. U kunt een aangepaste functie maken die later kan worden aangeroepen voor de query's of waarden van uw keuze. Deze aangepaste functie bespaart u tijd en helpt u bij het beheren van uw set transformaties op een centrale locatie, die u op elk moment kunt wijzigen.

Power Query aangepaste functies kunnen worden gemaakt op basis van bestaande query's en parameters. Stel dat u een query hebt met verschillende codes als tekenreeks en u een functie wilt maken die deze waarden decodeert.

Lijst met codes.

U begint met een parameter met een waarde die als voorbeeld fungeert.

Voorbeeld van parametercodewaarde.

Met deze parameter maakt u een nieuwe query waarin u de transformaties die u nodig hebt, kunt toepassen. In dit geval wilt u de code PTY-CM1090-LAX splitsen in meerdere onderdelen:

  • Origin = PTY
  • Destination = LAX
  • Luchtvaartmaatschappij = CM
  • FlightID = 1090

Voorbeeldtransformeerquery.

U kunt die query vervolgens transformeren naar een functie door met de rechtermuisknop op de query te klikken en Functie maken te selecteren. Ten slotte kunt u uw aangepaste functie aanroepen in een van uw query's of waarden, zoals wordt weergegeven in de volgende afbeelding.

Een aangepaste functie aanroepen.

Na nog een paar transformaties kunt u zien dat u de gewenste uitvoer hebt bereikt en de logica hebt gebruikt voor een dergelijke transformatie van een aangepaste functie.

Laatste uitvoerquery na het aanroepen van een aangepaste functie.

Notitie

Voor meer informatie over het maken en gebruiken van aangepaste functies in Power Query het artikel Aangepaste functies.