Aanbevolen procedures bij het werken met Power Query

Dit artikel bevat enkele tips en trucs om optimaal gebruik te maken van uw gegevens-wrangling-ervaring in Power Query.

De juiste verbindingslijn kiezen

Power Query biedt een groot aantal gegevensconnectors. 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 weergegeven in het venster Gegevens ophalen , 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, beschikt u over 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 ervaring gegevens ophalen, maar biedt de SQL Server-connector ook functies waarmee u uw ervaring en prestaties kunt verbeteren, zoals het vouwen van query's. Zie Power Query query folding voor meer informatie over het vouwen van query's.

Elke gegevensconnector volgt een standaardervaring zoals uitgelegd in Gegevens ophalen. 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 ophalen, als de connector dit toestaat en een eenvoudig voorbeeld van die gegevens. U kunt zelfs meerdere gegevenssets uit uw gegevensbron selecteren via het navigatorvenster , zoals wordt weergegeven in de volgende afbeelding.

Sample navigator window.

Notitie

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

Vroeg filteren

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

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

Auto filter menu in Power Query.

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

type specific filter for a date column.

Met deze typespecifieke filters kunt u een dynamisch filter maken waarmee altijd gegevens worden opgehaald die zich in het vorige x aantal seconden, minuten, uren, dagen, weken, maanden, kwartalen of jaren bevinden, zoals wordt weergegeven in de volgende afbeelding.

Is in the previous date-specific filter.

Notitie

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

Duurste bewerkingen uitvoeren

Voor bepaalde bewerkingen is het lezen van de volledige gegevensbron vereist om resultaten te retourneren en is het dus traag om een voorbeeld te bekijken in de Power Query-editor. Als u bijvoorbeeld een sortering uitvoert, is het mogelijk dat de eerste paar gesorteerde rijen zich aan het einde van de brongegevens bevinden. 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 geretourneerd. In plaats daarvan werken ze via de gegevens op een zogenaamde 'streaming'-manier. De gegevens 'stromen' door en de resultaten worden onderweg geretourneerd. In de Power Query-editor hoeven dergelijke bewerkingen alleen voldoende brongegevens te lezen om de preview te vullen.

Voer, indien mogelijk, eerst dergelijke streamingbewerkingen uit en voer eventuele duurdere bewerkingen uit. Zo kunt u de hoeveelheid tijd die u besteedt aan het wachten totdat het voorbeeld wordt weergegeven telkens wanneer u een nieuwe stap aan uw query toevoegt, minimaliseren.

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 eerst een bewerking 'Eerste rijen behouden' uitvoeren en het aantal rijen beperken waarmee u werkt. Wanneer u vervolgens 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. Als u bijvoorbeeld een datumkolom selecteert, zijn de beschikbare opties onder de kolomgroep Datum en tijd beschikbaar in het menu Kolom toevoegen . Maar als de kolom geen gegevenstypeset heeft, worden deze opties grijs weergegeven.

Type specific option in add column menu.

Er treedt een vergelijkbare situatie 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.

type specific filter for a date column.

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

Notitie

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

Uw gegevens verkennen

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

Data preview or data profiling tools in Power Query.

Met deze hulpprogramma's voor gegevensprofilering krijgt u meer inzicht in uw gegevens. 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 onder de categorieën geldige, fout- of lege waarden.
  • Kolomdistributie: biedt een set visuals onder de namen van de kolommen die de frequentie en verdeling van de waarden in elk van de kolommen laten zien.
  • Kolomprofiel: biedt een uitgebreidere weergave van uw kolom en de statistieken die eraan zijn gekoppeld.

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

Data Quality hover options.

Uw werk documenteer

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

Terwijl 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.

Applied steps pane with documented steps and description added.

Notitie

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

Een modulaire benadering gebruiken

Het is volledig 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, is het misschien een goed idee om de query op te splitsen in meerdere query's, waarbij de ene query verwijst naar de volgende. Het doel van deze aanpak 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 worden weergegeven in de volgende afbeelding.

Applied steps pane with documented steps and description added.

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

Extract previous step.

U wordt vervolgens gevraagd om een dialoogvenster om uw nieuwe query een naam te geven. Hierdoor wordt uw query in twee query's gesplitst. Eén query bevat alle query's vóór de samenvoegbewerking. 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 in de samenvoegbewerking hebt uitgevoerd met de tabel Prijzen omlaag.

Original query after the extract previous step action.

U kunt ook gebruikmaken van het gebruik van queryverwijsfunctie naar wens. Maar het is een goed idee om uw query's op een niveau te houden dat op het eerste gezicht niet lastig lijkt te zijn met zoveel stappen.

Notitie

Zie Het deelvenster Query's begrijpen 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 groepen in het deelvenster Query's.

Working with groups in Power Query.

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

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

Notitie

Zie Het deelvenster Query's begrijpen voor meer informatie over alle beschikbare functies en onderdelen in het deelvenster Query's.

Toekomstbestendige query's

Zorg ervoor dat u een query maakt die geen problemen ondervindt tijdens een toekomstige vernieuwing, heeft de hoogste prioriteit. Er zijn verschillende functies in Power Query om uw query tolerant te maken voor wijzigingen en om te vernieuwen, zelfs wanneer sommige onderdelen van uw gegevensbron worden gewijzigd.

Het is een best practice om het bereik van uw query te definiëren wat deze moet doen en waarvoor het rekening moet houden met structuur, indeling, kolomnamen, gegevenstypen en andere onderdelen die u relevant vindt voor het bereik.

Enkele voorbeelden van transformaties waarmee u uw query tolerant kunt maken voor wijzigingen, zijn:

  • 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 opheffen, kunt u de functie alleen geselecteerde kolommen opheffen .

    Notitie

    Zie Draaitabelkolommen opheffen voor meer informatie over de opties voor het ongedaan maken van de draaitabel.

  • Als uw query een stap heeft 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 met foutwaarden verwijderen.

    Notitie

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

Parameters gebruiken

Het maken van query's die dynamisch en flexibel zijn, is een best practice. Met parameters in Power Query kunt u uw query's dynamischer en flexibeler 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. Maar het wordt vaker gebruikt in twee scenario's:

  • Stapargument: u kunt een parameter gebruiken als het argument van meerdere transformaties die zijn gebaseerd op de gebruikersinterface.

    Select parameter for transformation argument.

  • 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.

    Create Function.

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

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

    Manage Parameters window.

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

  • Maakt het maken van aangepaste functies eenvoudig en eenvoudig.

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

SQL Server database dialog with parameter for server name.

Als u de serverlocatie wijzigt, hoeft u alleen maar de parameter bij te werken voor uw servernaam 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 zich in een situatie bevindt waarin u dezelfde set transformaties moet toepassen op verschillende query's of waarden, kunt u een Power Query aangepaste functie maken die net zo vaak opnieuw kan worden gebruikt als u nodig hebt. Een Power Query aangepaste functie is een toewijzing van een set invoerwaarden naar éé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 op basis van 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 bijvoorbeeld een query met verschillende codes als tekenreeks voor en u wilt een functie maken waarmee deze waarden worden gedecodeerd.

List of codes.

U begint met het hebben van een parameter met een waarde die als voorbeeld fungeert.

Sample parameter code value.

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

  • Oorsprong = PTY
  • Bestemming = LAX
  • Luchtvaartmaatschappij = CM
  • FlightID = 1090

Sample transform query.

U kunt die query vervolgens omzetten in 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.

Invoking a custom function.

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

Final output query after invoking a custom function.

Notitie

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