Aangepaste functies gebruiken

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.

Hoewel u handmatig uw eigen aangepaste Power Query-functie kunt maken met behulp van code zoals wordt weergegeven in Power Query M-functies, biedt de Power Query-gebruikersinterface u functies om het proces voor het maken en beheren van een aangepaste functie te versnellen, vereenvoudigen en verbeteren.

Dit artikel is gericht op deze ervaring, alleen beschikbaar via de gebruikersinterface van Power Query en hoe u er optimaal gebruik van kunt maken.

Belangrijk

In dit artikel wordt beschreven hoe u een aangepaste functie maakt met Power Query met behulp van algemene transformaties die toegankelijk zijn in de gebruikersinterface van Power Query. Het richt zich op de belangrijkste concepten voor het maken van aangepaste functies en koppelingen naar aanvullende artikelen in Power Query-documentatie voor meer informatie over specifieke transformaties waarnaar in dit artikel wordt verwezen.

Een aangepaste functie maken op basis van een tabelreferentie

Notitie

Het volgende voorbeeld is gemaakt met behulp van de bureaubladervaring in Power BI Desktop en kan ook worden gevolgd met behulp van de Power Query-ervaring in Excel voor Windows.

U kunt dit voorbeeld volgen door de voorbeeldbestanden te downloaden die in dit artikel worden gebruikt via de volgende downloadkoppeling. Ter vereenvoudiging gebruikt dit artikel de mapconnector. Ga naar Map voor meer informatie over de mapconnector. Het doel van dit voorbeeld is het maken van een aangepaste functie die kan worden toegepast op alle bestanden in die map voordat alle gegevens uit alle bestanden in één tabel worden gecombineerd.

Begin met het gebruik van de mapconnector om naar de map te navigeren waar uw bestanden zich bevinden en selecteer Gegevens transformeren of bewerken. Hiermee gaat u naar de Power Query-ervaring. Klik met de rechtermuisknop op de binaire waarde van uw keuze in het veld Inhoud en selecteer de optie Toevoegen als nieuwe query. In dit voorbeeld ziet u dat de selectie is gemaakt voor het eerste bestand in de lijst. Dit komt overeen met het bestand april 2019.csv.

Met deze optie maakt u effectief een nieuwe query met een navigatiestap rechtstreeks naar dat bestand als een binair bestand. De naam van deze nieuwe query is het bestandspad van het geselecteerde bestand. Wijzig de naam van deze query in voorbeeldbestand.

Maak een nieuwe parameter met de bestandsnaamparameter. Gebruik de query Voorbeeldbestand als de huidige waarde, zoals wordt weergegeven in de volgende afbeelding.

Notitie

We raden u aan het artikel over parameters te lezen om beter inzicht te krijgen in het maken en beheren van parameters in Power Query.

Aangepaste functies kunnen worden gemaakt met behulp van elk type parameters. Er is geen vereiste dat een aangepaste functie een binair bestand als parameter heeft.

Het binaire parametertype wordt alleen weergegeven in het vervolgkeuzemenu Parameterstype wanneer u een query hebt die resulteert in een binair bestand.

Het is mogelijk om een aangepaste functie te maken zonder een parameter. Dit wordt vaak gezien in scenario's waarin een invoer kan worden afgeleid uit de omgeving waarin de functie wordt aangeroepen. Een functie die bijvoorbeeld de huidige datum en tijd van de omgeving gebruikt en een specifieke tekenreeks maakt op basis van deze waarden.

Klik met de rechtermuisknop op Bestandsparameter in het deelvenster Query's . Selecteer de optie Verwijzing .

Wijzig de naam van de zojuist gemaakte query op basis van bestandsparameter (2) om het voorbeeldbestand te transformeren.

Klik met de rechtermuisknop op deze nieuwe transformatievoorbeeldquery en selecteer de optie Functie maken.

Met deze bewerking maakt u effectief een nieuwe functie die wordt gekoppeld aan de query Voorbeeldbestand transformeren. Wijzigingen die u aanbrengt in de query Voorbeeldbestand transformeren, worden automatisch gerepliceerd naar uw aangepaste functie. Gebruik tijdens het maken van deze nieuwe functie het transformatiebestand als de naam van de functie.

Nadat u de functie hebt gemaakt, ziet u dat er een nieuwe groep voor u wordt gemaakt met de naam van uw functie. Deze nieuwe groep bevat:

• Alle parameters waarnaar wordt verwezen in de query Van het transformatievoorbeeldbestand .
• Uw voorbeeldbestandsquery transformeren, ook wel bekend als de voorbeeldquery.
• Uw zojuist gemaakte functie, in dit geval Transform-bestand.

Transformaties toepassen op een voorbeeldquery

Wanneer de nieuwe functie is gemaakt, selecteert u de query met de naam Transform Sample-bestand. Deze query is nu gekoppeld aan de functie Bestand transformeren, zodat eventuele wijzigingen in deze query worden doorgevoerd in de functie. Dit wordt het concept van een voorbeeldquery genoemd die is gekoppeld aan een functie.

De eerste transformatie die met deze query moet worden uitgevoerd, is een transformatie die het binaire bestand interpreteert. U kunt met de rechtermuisknop op het binaire bestand klikken in het voorbeeldvenster en de CSV-optie selecteren om het binaire bestand te interpreteren als een CSV-bestand.

De indeling van alle CSV-bestanden in de map is hetzelfde. Ze hebben allemaal een koptekst die de eerste vier bovenste vier rijen omvat. De kolomkoppen bevinden zich in rij vijf en de gegevens beginnen vanaf rij zes naar beneden, zoals wordt weergegeven in de volgende afbeelding.

De volgende set transformatiestappen die moeten worden toegepast op het transformatievoorbeeldbestand zijn:

1. Verwijder de bovenste vier rijen. Met deze actie worden de rijen verwijderd die worden beschouwd als onderdeel van de koptekstsectie van het bestand.

   

   Notitie

   Ga naar Filteren op rijpositie voor meer informatie over het verwijderen van rijen of het filteren van een tabel op rijpositie.

2. Kopteksten promoveren: de kopteksten voor de uiteindelijke tabel bevinden zich nu in de eerste rij van de tabel. U kunt deze promoveren zoals wordt weergegeven in de volgende afbeelding.

   

Power Query voegt standaard automatisch een nieuwe stap Gewijzigd type toe nadat u de kolomkoppen hebt gepromoot waarmee automatisch de gegevenstypen voor elke kolom worden gedetecteerd. De voorbeeldquery Transform Sample ziet eruit als in de volgende afbeelding.

Notitie

Ga naar Kolomkoppen promoveren of degraderen voor meer informatie over het promoveren en degraderen van kopteksten.

Let op

De functie Transform-bestand is afhankelijk van de stappen die worden uitgevoerd in de query Voorbeeldbestand transformeren. Als u echter de code voor de functie Transform-bestand handmatig probeert te wijzigen, wordt u begroet met een waarschuwing die wordt gelezen The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

Een aangepaste functie aanroepen als een nieuwe kolom

Nu de aangepaste functie is gemaakt en alle transformatiestappen zijn opgenomen, kunt u teruggaan naar de oorspronkelijke query waarin u de lijst met bestanden uit de map hebt. Selecteer op het tabblad Kolom toevoegen op het lint de optie Aangepaste functie aanroepen in de groep Algemeen . Voer in het venster Aangepaste functie aanroepen uitvoertabel in als de naam van de nieuwe kolom. Selecteer de naam van uw functie, Transform-bestand, in de vervolgkeuzelijst Functiequery . Nadat u de functie hebt geselecteerd in het vervolgkeuzemenu, wordt de parameter voor de functie weergegeven en kunt u selecteren welke kolom in de tabel moet worden gebruikt als argument voor deze functie. Selecteer de kolom Inhoud als de waarde/het argument dat moet worden doorgegeven voor de parameter Bestand.

Nadat u OK hebt geselecteerd, wordt er een nieuwe kolom met de naam Uitvoertabel gemaakt. Deze kolom bevat tabelwaarden in de cellen, zoals wordt weergegeven in de volgende afbeelding. Verwijder voor het gemak alle kolommen uit deze tabel, behalve Naam en Uitvoertabel.

Notitie

Ga naar Kolommen kiezen of verwijderen voor meer informatie over het kiezen of verwijderen van kolommen uit een tabel.

Uw functie is toegepast op elke rij uit de tabel met behulp van de waarden uit de kolom Inhoud als het argument voor uw functie. Nu de gegevens zijn getransformeerd in de vorm die u zoekt, kunt u de kolom Uitvoertabel uitvouwen, zoals wordt weergegeven in de onderstaande afbeelding, zonder een voorvoegsel te gebruiken voor de uitgevouwen kolommen.

U kunt controleren of u gegevens uit alle bestanden in de map hebt door de waarden in de kolom Naam of Datum te controleren. In dit geval kunt u de waarden uit de kolom Datum controleren, omdat elk bestand slechts gegevens bevat voor één maand van een bepaald jaar. Als u meer dan één bestand ziet, betekent dit dat u gegevens uit meerdere bestanden in één tabel hebt gecombineerd.

Notitie

Wat u tot nu toe hebt gelezen, is fundamenteel hetzelfde proces dat plaatsvindt tijdens de ervaring Bestanden combineren, maar handmatig wordt uitgevoerd.

We raden u aan ook het artikel over het overzicht bestanden combineren en CSV-bestanden combineren te lezen om te begrijpen hoe de ervaring met het combineren van bestanden werkt in Power Query en de rol die aangepaste functies spelen.

Nieuwe parameter toevoegen aan bestaande aangepaste functie

Stel dat er een nieuwe vereiste is bovenop wat u hebt gebouwd. De nieuwe vereiste vereist dat voordat u de bestanden combineert, u de gegevens erin filtert om alleen de rijen op te halen waar het land gelijk is aan Panama.

Als u deze vereiste wilt uitvoeren, maakt u een nieuwe parameter met de naam Market met het gegevenstype tekst. Voer voor de huidige waarde de waarde Panama in.

Met deze nieuwe parameter selecteert u de query Voorbeeldbestand Transformeren en filtert u het veld Land met behulp van de waarde uit de parameter Markt .

Notitie

Ga naar Filterwaarden voor meer informatie over het filteren van kolommen op waarden.

Als u deze nieuwe stap toepast op uw query, wordt de functie Transformatiebestand automatisch bijgewerkt. Hiervoor zijn nu twee parameters vereist op basis van de twee parameters die door het transformatievoorbeeldbestand worden gebruikt.

Maar de QUERY voor CSV-bestanden bevat een waarschuwingsteken ernaast. Nu uw functie is bijgewerkt, zijn er twee parameters vereist. De stap waarin u de functie aanroept, resulteert dus in foutwaarden, omdat slechts één van de argumenten is doorgegeven aan de functie Transformatiebestand tijdens de aangeroepen aangepaste functiestap .

Als u de fouten wilt oplossen, dubbelklikt u op De aangeroepen aangepaste functie in de toegepaste stappen om het venster Aangepaste functie aanroepen te openen. Voer in de parameter Market handmatig de waarde Panama in.

U kunt nu uw query controleren om te controleren of alleen rijen waar Land gelijk is aan Panama worden weergegeven in de uiteindelijke resultatenset van de CSV-bestandenquery .

Een aangepaste functie maken op basis van een herbruikbaar stukje logica

Als u meerdere query's of waarden hebt waarvoor dezelfde set transformaties is vereist, kunt u een aangepaste functie maken die fungeert als een herbruikbaar stukje logica. Deze aangepaste functie kan later worden aangeroepen op basis van de query's of waarden van uw keuze. Deze aangepaste functie kan u tijd besparen en u helpen bij het beheren van uw set transformaties op een centrale locatie, die u op elk moment kunt wijzigen.

Stel dat een query met verschillende codes als een tekenreeks bestaat en u een functie wilt maken waarmee deze waarden worden gedecodeerd, zoals in de volgende voorbeeldtabel:

code
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

U begint met een parameter met een waarde die als voorbeeld fungeert. In dit geval is dit de waarde PTY-CM1090-LAX.

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

De M-code voor die set transformaties wordt hieronder weergegeven.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Notitie

Ga naar de formuletaal Power Query M voor meer informatie over de formuletaal Power Query M.

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.