Aanbevolen procedures bij het werken met Power Query

Deze Power Query aanbevolen procedures helpen u bij het verbeteren van de queryprestaties, het profiteren van het vouwen van query's, het selecteren van de juiste gegevenstypen, het organiseren van transformaties en het opnieuw gebruiken van logica met parameters en aangepaste functies. Ze zijn van toepassing op zowel Power Query Desktop- als Power Query Online-ervaringen.

De juiste verbindingslijn kiezen

Power Query biedt veel gegevensverbindingen. Deze connectors variëren van gegevensbronnen zoals TXT-, CSV- en Excel-bestanden tot databases zoals Microsoft SQL Server en populaire SaaS-producten (Software as a Service), zoals Microsoft Dynamics 365 en Salesforce. Als een speciaal gebouwde connector niet beschikbaar is in het venster Gegevens ophalen , gebruikt u een algemene connector, zoals ODBC of OLE DB.

Kies de speciaal gebouwde connector voor uw gegevensbron wanneer deze beschikbaar is. De SQL Server-connector biedt bijvoorbeeld een betere ervaring gegevens ophalen dan de algemene ODBC-connector wanneer u verbinding maakt met een SQL Server-database. De SQL Server-connector ondersteunt ook prestatiefuncties zoals het vouwen van query's. Ga voor meer informatie naar Overzicht van de query-evaluatie en het vouwen van query's in Power Query.

Elke gegevensconnector volgt een standaardervaring, zoals wordt 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 .

Gegevens vroeg filteren om de prestaties te verbeteren

Filter de gegevens zo vroeg mogelijk, zodat Power Query in latere bewerkingen minder rijen hoeft te verwerken. Voor connectors die ondersteuning bieden voor het vouwen van query's, kunt Power Query filters terugsturen naar de gegevensbron, zoals beschreven in Overzicht van de queryevaluatie en het vouwen van query's in Power Query. Als u irrelevante gegevens filtert, worden ook de gegevens beperkt die worden weergegeven in de voorbeeldweergave van de gegevens.

Gebruik het menu voor automatisch filteren, 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. Gebruik de zoekbalk om de waarden in uw kolom te vinden.

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

Met deze typespecifieke filters kunt u een dynamisch filter maken dat altijd gegevens ophaalt die zich in het vorige x aantal seconden, minuten, uren, dagen, weken, maanden, kwartalen of jaren bevindt.

Voer kostbare bewerkingen als laatste uit om de prestaties te verbeteren

Als u de preview-prestaties in de Power Query-editor wilt verbeteren, voert u dure bewerkingen als laatste uit. Bepaalde bewerkingen vereisen dat de volledige gegevensbron wordt gelezen om enige resultaten te geven en zijn daarom traag in de voorvertoning. 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 resultaten wilt retourneren, moet de sorteerbewerking eerst alle rijen lezen.

Andere bewerkingen (zoals filters) hoeven niet alle gegevens te lezen voordat ze resultaten retourneren. In plaats daarvan verwerken ze de gegevens op een manier die bekend staat als "streaming". De gegevens 'stromen' door en de resultaten worden onderweg geretourneerd. In de Power Query-editor hoeven dergelijke bewerkingen alleen voldoende brongegevens te lezen om het voorbeeld te vullen.

Voer, indien mogelijk, eerst dergelijke streamingbewerkingen uit en doe duurdere bewerkingen pas later. Door bewerkingen in deze volgorde uit te voeren, kunt u de tijd die u wacht tot de preview wordt gerenderd minimaliseren telkens wanneer u een nieuwe stap aan uw query toevoegt.

Een gegevenssubset gebruiken tijdens het ontwikkelen van een query

Als het toevoegen van nieuwe stappen in de Power Query-editor traag is, gebruikt u Eerste rijen behouden om de hoeveelheid verwerkte gegevens te beperken terwijl u de query ontwikkelt. Nadat u alle vereiste stappen hebt toegevoegd, verwijdert u de stap Eerste rijen behouden , zodat de voltooide query de volledige gegevensset verwerkt.

De juiste gegevenstypen gebruiken

Stel het juiste gegevenstype in voor elke kolom, zodat Power Query typespecifieke transformaties en filters beschikbaar kan maken. Wanneer u bijvoorbeeld een datumkolom selecteert, kunt u de opties onder de kolomgroep Datum en tijd gebruiken in het menu Kolom toevoegen . Als de kolom geen gegevenstypeset heeft, worden deze opties grijs weergegeven.

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.

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. Voor niet-gestructureerde gegevensbronnen, zoals TXT- en CSV-bestanden, is het echter belangrijk dat u de juiste gegevenstypen instelt voor de kolommen die afkomstig zijn van die gegevensbron. Power Query biedt standaard automatische detectie van gegevenstypen voor ongestructureerde gegevensbronnen. U kunt meer lezen over deze functie en hoe u deze kunt gebruiken in gegevenstypen.

Analyseer en verken uw gegevens

Voordat u uw gegevens voorbereidt en transformatiestappen toevoegt, schakelt u de Power Query hulpprogramma's voor gegevensprofilering in om informatie over uw gegevens te ontdekken.

Power Query biedt drie hulpprogramma's voor gegevensprofilering:

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

Uw werk documenteer

Documenteer een Power Query oplossing door stappen, query's en groepen betekenisvolle namen en beschrijvingen te geven. Deze details maken het doel van elke transformatie gemakkelijker te begrijpen en te onderhouden.

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.

Splits grote queries op in modules

Splits een grote Power Query-query op in kleinere query’s waarnaar wordt verwezen, zodat de transformatiefasen gemakkelijker te begrijpen en te onderhouden zijn. Hoewel één query alle benodigde transformaties en berekeningen kan bevatten, is een query met veel stappen eenvoudiger te beheren wanneer een query verwijst naar de volgende.

Deze query heeft bijvoorbeeld negen stappen en bevat een stap Samenvoegen met de tabel Prijzen.

U kunt deze query bij de stap "Samenvoegen met tabel Prijzen" in tweeën splitsen. Op die manier is het eenvoudiger 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 extraheren .

Schermopname van het contextmenu van toegepaste stappen met de nadruk op "Vorige stap extraheren."

Vervolgens wordt u gevraagd in een dialoogvenster om een naam te geven aan uw nieuwe query. Met deze stap wordt uw query in twee query's gesplitst. Eén query bevat alle stappen voordat de samenvoegbewerking wordt uitgevoerd. De andere query heeft een initiële stap die verwijst naar uw nieuwe query en vervolgens alle verdere stappen uit uw oorspronkelijke query, te beginnen met de stap in de tabel Samenvoegen met Prijzen en verder naar beneden.

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

Zoekopdrachten in groepen ordenen

Gebruik groepen in het deelvenster Query's om uw werk georganiseerd te houden.

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 dat nodig is. 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.

Toekomstbestendige queries

Ontwerp query’s om verwachte wijzigingen in de brongegevens op te vangen, zodat toekomstige verversingen succesvol blijven. Power Query biedt transformaties die een query tolerant maken wanneer de rijen, kolommen of waarden in een gegevensbron veranderen.

Definieer het bereik van uw query, inclusief wat deze moet doen en waarvoor deze rekening moet houden in termen van structuur, indeling, kolomnamen, gegevenstypen en andere relevante onderdelen.

Met de volgende transformaties kan een query tolerant blijven voor wijzigingen:

Parameters gebruiken

Gebruik Power Query parameters om waarden op te slaan en te beheren die u opnieuw kunt gebruiken in transformaties, gegevensbronfuncties en aangepaste functies. Met parameters kunt u query's gemakkelijker bijwerken, omdat u een waarde op één locatie kunt wijzigen in plaats van elke query te bewerken die deze gebruikt. Twee veelvoorkomende scenario's zijn:

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

  

• Argument Aangepaste functie: maak een nieuwe functie op basis van een query en verwijs naar parameters als de argumenten van uw aangepaste functie.

  

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

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

  

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

• Maakt het maken van aangepaste functies eenvoudig en gemakkelijk.

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

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

Herbruikbare functies maken

Maak een Power Query aangepaste functie wanneer u dezelfde set transformaties wilt toepassen op verschillende query's of waarden. Een Power Query aangepaste functie wijst een set invoerwaarden toe aan één uitvoerwaarde en wordt gemaakt op basis van systeemeigen Power Query M-formuletaalfuncties en -operators.

Stel dat u meerdere query's of waarden hebt waarvoor dezelfde set transformaties is vereist. U kunt een aangepaste functie maken die u later aanroept 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 gewenst moment kunt wijzigen.

Aangepaste Power Query-functies kunnen worden gemaakt op basis van bestaande query's en parameters. Stel dat een query meerdere codes als een tekenreeks bevat en u een functie wilt maken waarmee deze waarden worden gedecodeerd.

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

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

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

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

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