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 beschreven in Power Query M-functies, biedt de Power Query-gebruikersinterface u functies om het proces van 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. Voor het gemak 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. Met deze stappen 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 is de selectie gemaakt voor het eerste bestand uit de lijst. Dit is 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 en 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 en het type Binair. Gebruik de voorbeeldbestandsquery als de standaardwaarde en huidige waarde.
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 is 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. Deze verbinding staat bekend als het concept van een voorbeeldquery 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 5 en de gegevens beginnen vanaf rij 6 naar beneden, zoals wordt weergegeven in de volgende afbeelding.
De volgende set transformatiestappen die moeten worden toegepast op het transformatievoorbeeldbestand zijn:
Verwijder de bovenste vier rijen. Met deze actie worden de rijen verwijderd die worden beschouwd als onderdeel van de koptekstsectie van het bestand.
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.
Nadat u de kolomkoppen hebt gepromoot, wordt in Power Query standaard automatisch een nieuwe stap Gewijzigd type toegevoegd waarmee automatisch de gegevenstypen voor elke kolom worden gedetecteerd. De voorbeeldquery Transform Sample ziet eruit als in de volgende afbeelding.
Notitie
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 (CSV-bestanden in dit voorbeeld). 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 in de vervolgkeuzelijst hebt geselecteerd, 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 omgezet in de shape die u zoekt, kunt u de kolom Uitvoertabel uitvouwen door het pictogram Uitvouwen te selecteren. Gebruik geen voorvoegsel 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 momenteel hebt gebouwd. De nieuwe vereiste vereist dat voordat u de bestanden combineert, u de gegevens erin filtert om alleen de rijen te krijgen 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 terugkeren naar de uitgevouwen uitvoertabel in de toegepaste stappen. Controleer uw query 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 u een query voor die meerdere codes als een tekenreeks heeft 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 volgende M-code demonstreert die set transformaties.
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 toegepast.
