Aangepaste functies gebruiken

Als u in een situatie komt 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 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 Understanding Power Query M functions(Inzicht in Power Query M-functies) biedt de Power Query-gebruikersinterface functies waarmee u het proces van het maken en beheren van een aangepaste functie kunt versnellen, vereenvoudigen en verbeteren. Dit artikel is gericht op deze ervaring die alleen wordt geboden Power Query gebruikersinterface en hoe u er het meeste uit kunt halen.

Belangrijk

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

Een aangepaste functie maken op basis van een tabelverwijzing

U kunt dit voorbeeld volgen door de voorbeeldbestanden te downloaden die in dit artikel worden gebruikt via de volgende downloadkoppeling. Voor het gemak wordt in dit artikel gebruik gemaakt van de mapconnector. Zie Map voor meer informatie over de mapconnector. Het doel van dit voorbeeld is om een aangepaste functie te maken die kan worden toegepast op alle bestanden in die map voordat u alle gegevens uit alle bestanden in één tabel combineert.

Begin met het gebruik van de mapconnector om naar de map te navigeren waarin 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 uit de lijst. Dit is het bestand april 2019.csv.

Het selecteren van een bestand als ons voorbeeldbestand.

Met deze optie wordt een nieuwe query met een navigatiestap rechtstreeks naar dat bestand als binair bestand, en de naam van deze nieuwe query wordt het bestandspad van het geselecteerde bestand. Wijzig de naam van deze query in Voorbeeldbestand.

Voorbeeld van een bestandsquery.

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

Bestandsparameter.

Notitie

U wordt aangeraden het artikel parameters te lezen voor meer inzicht in het maken en beheren van parameters in Power Query.

Aangepaste functies kunnen worden gemaakt met elk type parameters. Het is niet vereist dat een aangepaste functie een binair bestand als parameter heeft.

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

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

Verwijs naar de bestandsparameter.

Wijzig de naam van de zojuist gemaakte query van Bestandsparameter (2) in Voorbeeldbestand transformeren.

De naam van het voorbeeldbestand Transformatie van query is gewijzigd.

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

Maak een functie vanuit voorbeeldbestand transformeren.

Met deze bewerking wordt effectief een nieuwe functie aan de query Voorbeeldbestand transformeren gekoppeld. Alle wijzigingen die u aan de query Voorbeeldbestand transformeren aan brengen, worden automatisch gerepliceerd naar uw aangepaste functie. Gebruik tijdens het maken van deze nieuwe functie Bestand transformeren als de functienaam.

Maak een functievenster voor Bestand transformeren.

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 is verwezen in de voorbeeldbestandsquery Transformeren.
  • De query Voorbeeldbestand transformeren, ook wel de voorbeeldquery genoemd.
  • De zojuist gemaakte functie, in dit geval Transform file.

Functiegroep.

Transformaties toepassen op een voorbeeldquery

Nu de nieuwe functie is gemaakt, selecteert u de query met de naam Voorbeeldbestand transformeren. Deze query is nu gekoppeld aan de functie Bestand transformeren, dus alle wijzigingen die in deze query zijn aangebracht, worden doorgevoerd in de functie . Dit is wat bekend staat als het concept van een voorbeeldquery die is gekoppeld aan een functie.

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

Interpreteer binair als CSV.

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

Voorbeeld van CSV-gegevens.

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

  1. De eerste vier rijen verwijderen — Met deze actie worden de rijen die worden beschouwd als onderdeel van de headersectie van het bestand, kwijt.

    Verwijder bovenste rijen uit voorbeeldbestand transformeren.

    Notitie

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

  2. Headers promoveren — De headers voor de uiteindelijke tabel staan nu in de eerste rij van de tabel. U kunt ze promoveren zoals wordt weergegeven in de volgende afbeelding.

    Gebruik de eerste rij als kopteksten.

Power Query voegt standaard automatisch een nieuwe stap Gewijzigd type toe na het promoveren van uw kolomkoppen, die automatisch de gegevenstypen voor elke kolom detecteert. De query Voorbeeldbestand transformeren ziet eruit als in de volgende afbeelding.

Notitie

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

Voorbeeldquery voor laatste transformatie.

Waarschuwing

De functie Bestand transformeren is afhankelijk van de stappen die worden uitgevoerd in de bestandsquery Voorbeeld transformeren. Als u echter probeert de code voor de functie Bestand transformeren handmatig te wijzigen, wordt u begroet met een waarschuwing met de tekstThe 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, waar 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, Bestand transformeren, in de vervolgkeuzekeuze lijst Functiequery. Nadat u de functie hebt geselecteerd in de vervolgkeuzelijst, 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 bestandsparameter.

De knop Aangepaste functie aanroepen in het menu Kolom toevoegen.

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

Aangepaste functie aangeroepen.

Notitie

Zie 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 argument voor uw functie. Nu de gegevens zijn getransformeerd in de vorm die u zoekt, kunt u de kolom Uitvoertabel uitbreiden, zoals wordt weergegeven in de onderstaande afbeelding, zonder voorvoegsel te gebruiken voor de uit uitgebreide kolommen.

Vouw de kolom Uitvoertabel uit.

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 in de kolom Datum controleren, omdat elk bestand slechts gegevens voor één maand van een bepaald jaar bevat. Als u meer dan één tabel ziet, betekent dit dat u gegevens uit meerdere bestanden in één tabel hebt gecombineerd.

Valideren dat de uiteindelijke tabel gegevens uit alle bestanden bevat.

Notitie

Wat u tot nu toe hebt gelezen, is in principe hetzelfde proces dat wordt uitgevoerd tijdens het combineren van bestanden, maar handmatig wordt uitgevoerd.

U wordt aangeraden ook het artikel Bestandenoverzicht combineren en CSV-bestanden combineren te lezen om meer inzicht te krijgen in de manier waarop bestanden combineren werkt in Power Query en de rol die aangepaste functies spelen.

Nieuwe parameter toevoegen aan bestaande aangepaste functie

Imagine dat er een nieuwe vereiste is boven op wat u hebt gemaakt. De nieuwe vereiste vereist dat u, voordat u de bestanden combineert, de gegevens erin filtert om alleen de rijen op te halen waarin Country gelijk is aan Country.

Als u deze vereiste wilt realiseren, maakt u een nieuwe parameter met de naam Markt met het tekstgegevenstype. Voer voor de Huidige waarde de waarde Voor .

Nieuwe parameter.

Selecteer met deze nieuwe parameter de query Voorbeeldbestand transformeren en filter het veld Land met behulp van de waarde van de parameter Market.

Filter de kolom Country met behulp van de nieuwe parameter Market.

Notitie

Zie Waarden filteren voor meer informatie over het filteren van kolommen op waarden.

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

Functie bijgewerkt met nu twee parameters.

Maar er naast de query csv-bestanden staat een waarschuwingsbord. Nu uw functie is bijgewerkt, zijn er twee parameters vereist. De stap waarbij u de functie aanroept, resulteert dus in foutwaarden, omdat slechts een van de argumenten is doorgegeven aan de functie Bestand transformeren tijdens de stap Aangeroepen aangepaste functie.

Fouten na functie-update.

Dubbelklik in toegepaste stappen op Aangeroepen aangepaste functie om het venster Aangepaste functie aanroepen te openen om de fouten op te lossen. Voer in de parameter Market handmatig de waarde Voor in.

Aangeroepen aangepaste functieargumenten bijwerken.

U kunt nu uw query controleren om te controleren of alleen rijen waar Country gelijk is aan DevD worden weergeven in de uiteindelijke resultatenset van de CSV Files-query.

Laatste uitvoertabel na bijgewerkte argumenten.

Een aangepaste functie maken op grond 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. Later kan deze aangepaste functie worden aangeroepen voor de query's of waarden van uw keuze. Deze aangepaste functie kan u tijd besparen en helpen bij het beheren van uw set transformaties op een centrale locatie, die u op elk moment kunt wijzigen.

Stel dat u een query hebt met verschillende codes als tekstreeks 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. In dit geval is dit de waarde PTY-CM1090-LAX.

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
  • Doel = LAX
  • Luchtvaartmaatschappij = CM
  • FlightID = 1090

Voorbeeld van transformatiequery.

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

Zie M-formuletaal voor Power Query meer informatie over Power Query M-formuletaal

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.