Aanbevolen procedures bij het werken met Power Query
Dit artikel bevat enkele tips en trucs om optimaal gebruik te maken van uw gegevens 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 get Data-ervaring , maar biedt de SQL Server-connector ook functies die uw ervaring en prestaties kunnen verbeteren, zoals het vouwen van query's. Als u meer wilt lezen over het vouwen van query's, gaat u 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 , zoals wordt weergegeven in de volgende afbeelding.
Notitie
Als u de volledige lijst met beschikbare connectors in Power Query wilt zien, gaat u naar Verbinding maken ors 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 Overzicht van de query-evaluatie en het vouwen van query's in Power Query. Het is ook een best practice om alle gegevens te filteren die niet relevant zijn voor uw case. 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 waarmee 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.
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 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.
Notitie
Ga naar Filteren op waarden voor meer informatie over het filteren van uw gegevens op basis van waarden uit een kolom.
Duur van bewerkingen uitvoeren
Voor bepaalde bewerkingen moet de volledige gegevensbron worden gelezen om eventuele 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 ze resultaten retourneren. In plaats daarvan werken ze via de gegevens op een '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. Dit helpt de hoeveelheid tijd die u nodig hebt om te wachten totdat het voorbeeld 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 eerst een bewerking 'Eerste rijen behouden' uitvoeren en het aantal rijen beperken waarmee u werkt. Als u vervolgens alle benodigde stappen hebt toegevoegd, 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 weergegeven.
Er treedt een vergelijkbare situatie op voor de typespecifieke filters, omdat deze specifiek zijn voor bepaalde gegevenstypen. Als in uw kolom niet het juiste gegevenstype is 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.
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 uw gegevens en het toevoegen van nieuwe transformatiestappen, raden we u aan de hulpprogramma's voor gegevensprofilering van Power Query in te schakelen om eenvoudig informatie over uw gegevens te ontdekken.
Deze hulpprogramma's voor gegevensprofilering helpen u meer inzicht te krijgen in uw gegevens. De hulpprogramma's bieden u kleine visualisaties die informatie per kolom weergeven, 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 bijbehorende statistieken.
U kunt ook communiceren met deze functies, zodat u uw gegevens kunt voorbereiden.
Notitie
Ga naar Hulpprogramma's voor gegevensprofilering voor meer informatie over de hulpprogramma's voor gegevensprofilering.
Uw werk documenteer
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 zoals u dat wilt.
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.
Notitie
Voor meer informatie over alle beschikbare functies en onderdelen in het deelvenster toegepaste stappen gaat u naar De lijst met toegepaste stappen gebruiken.
Een modulaire benadering hanteren
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 één query verwijst naar de volgende. Het doel van deze aanpak is het vereenvoudigen en loskoppelen van transformatiefasen in kleinere stukken, zodat ze gemakkelijker te begrijpen zijn.
Stel dat u een query hebt met de negen stappen die in de volgende afbeelding worden weergegeven.
U kunt deze query splitsen in twee bij de stap Samenvoegen met tabel Prijzen. 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.
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 samenvoeging met prijzentabel hebt uitgevoerd, omlaag.
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 met zoveel stappen.
Notitie
Ga naar Het deelvenster Query's begrijpen voor meer informatie over het verwijzen naar query's.
Groepenmaken
Een uitstekende manier om uw werk georganiseerd te houden, is door gebruik te maken van groepen in het deelvenster Query's.
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.
Notitie
Voor meer informatie over alle beschikbare functies en onderdelen in het deelvenster Query's gaat u naar Het deelvenster Query's begrijpen.
Toekomstbestendige query's
Zorg ervoor dat u een query maakt die geen problemen ondervindt tijdens een toekomstige vernieuwing. 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 over wat deze moet doen en waarvoor rekening moet worden gehouden in termen van structuur, indeling, kolomnamen, gegevenstypen en andere onderdelen die u relevant acht 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 bevat, maar een vast aantal rijen dat als voettekst moet worden verwijderd, kunt u de functie Onderste rijen verwijderen gebruiken.
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.
Als uw query een dynamisch aantal kolommen heeft en u alleen een subset van de kolommen wilt opheffen, kunt u de functie alleen geselecteerde kolommen opheffen.
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.
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.
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.
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 eenvoudig.
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.
Notitie
Ga naar 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 aangepaste Power Query-functie maken die net zo vaak opnieuw kan worden gebruikt als u nodig hebt. Een aangepaste 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. Met 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.
Aangepaste Power Query-functies kunnen worden gemaakt op basis van bestaande query's en parameters. Stel bijvoorbeeld dat een query met verschillende codes als een tekenreeks bestaat 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 PTY-CM1090-LAX splitsen 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, zoals wordt weergegeven in de volgende afbeelding.
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 gebruikt.
Notitie
Meer informatie over het maken en gebruiken van aangepaste functies in Power Query vindt u in het artikel Aangepaste functies.
Feedback
Binnenkort beschikbaar: In de loop van 2024 zullen we GitHub-problemen geleidelijk uitfaseren als het feedbackmechanisme voor inhoud en deze vervangen door een nieuw feedbacksysteem. Zie voor meer informatie: https://aka.ms/ContentUserFeedback.
Feedback verzenden en weergeven voor