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.
U kunt handmatig uw eigen aangepaste Power Query-functie maken met behulp van de Power Query M-formuletaal, of de gebruikersinterface van Power Query biedt u functies waarmee u het proces voor het maken en beheren van een aangepaste functie kunt versnellen, vereenvoudigen en verbeteren.
Eerst behandelen we de basisstappen voor het maken van een aangepaste functie met code in de gebruikersinterface. Vervolgens richten we ons op het gebruik van de interface om complexe acties om te zetten in een herbruikbare functie.
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 andere artikelen in Power Query-documentatie voor meer informatie over specifieke transformaties waarnaar in dit artikel wordt verwezen.
Een aangepaste functie maken op basis van code in de gebruikersinterface
Notitie
De volgende stappen kunnen worden gevolgd in Power BI Desktop of met de Power Query-ervaring in Excel voor Windows.
- Gebruik de connectorervaring om verbinding te maken met uw gegevens waar deze zijn ondergebracht. Wanneer u uw gegevens hebt geselecteerd, selecteert u de knop Gegevens transformeren of bewerken. Hiermee gaat u naar de Power Query-ervaring.
- Klik met de rechtermuisknop op een lege plek in het deelvenster Query's aan de linkerkant.
- Selecteer lege query.
- Selecteer in het nieuwe lege queryvenster het menu Start, en vervolgens Geavanceerde editor.
- Vervang de sjabloon door uw aangepaste functie. Bijvoorbeeld:
let HelloWorld = () => ("Hello World") in HelloWorld - Selecteer Gereed.
Zie dit artikel voor meer informatie over het ontwikkelen van aangepaste functies met de Power Query M-formuletaal: Informatie over Power Query M-functies. In de volgende secties vindt u handleidingen die beschrijven hoe u de Power Query-gebruikersinterface kunt gebruiken om aangepaste functies te ontwikkelen zonder code te schrijven. Er zijn ook instructies over hoe u uw aangepaste functie in uw query kunt aanroepen.
Een aangepaste functie maken uit een handleiding voor tabelreferenties
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 de eenvoud gebruikt dit artikel de Folder-connector. Ga naar Mapconnectorvoor 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 de ervaring van de mapconnector om naar de map te navigeren waar zich uw bestanden 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 naam Bestandsparameter en het type Binary. Gebruik de query met het voorbeeldbestand als zowel de standaardwaarde als de 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 dialoogvenster Parametersvervolgkeuzelijst Type 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 van bestandsparameter (2) naar voorbeelddocument transformeren.
Klik met de rechtermuisknop op dit nieuwe Voorbeeldbestand transformeren query en selecteer de optie Functie maken.
Met deze bewerking creëert u effectief een nieuwe functie die is gekoppeld aan het Transformeer Voorbeeldbestand query. Wijzigingen die je aanbrengt in het Transform Sample-bestand query, worden automatisch gekopieerd naar je aangepaste functie. Gebruik tijdens het maken van deze nieuwe functie Transform-bestand 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 uw Transformatievoorbeeldbestand-query.
- Uw Transformatie Voorbeeldbestand-query, ook wel bekend als de voorbeeldquery.
- Uw zojuist gemaakte functie, in dit geval transformeer bestand.
Transformaties toepassen op een voorbeeldquery
Wanneer de nieuwe functie is gemaakt, selecteert u de query met de naam Voorbeeldbestand transformeren. Deze query is nu gekoppeld aan de Transform-bestand functie, 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 optie CSV- 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 Voorbeeldbestand transformeren zijn:
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 rijpositievoor meer informatie over het verwijderen van rijen of het filteren van een tabel op rijpositie.
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 Gewijzigd type stap toegevoegd waarmee automatisch de gegevenstypen voor elke kolom worden gedetecteerd. Uw transformatiemonsterbestand opdracht ziet eruit als de volgende afbeelding.
Notitie
Ga naar Kolomkoppen promoveren of verlagenvoor meer informatie over het promoten en verlagen van kopteksten.
Voorzichtigheid
Uw transformatiebestand functies zijn afhankelijk van de stappen die worden uitgevoerd in de query van het transformatievoorbeeldbestand. Als u de code voor het Transform-bestand echter handmatig probeert te wijzigen functie, wordt u begroet met een waarschuwing die 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). Binnen het tabblad Kolom toevoegen op het lint selecteer je Aangepaste functie aanroepen uit de groep Algemeen. Voer in het venster Aangepaste functie aanroepenuitvoertabel in als de nieuwe kolomnaam. Selecteer de naam van uw functie, Transformeer bestand, uit de Functiequery vervolgkeuzelijst. 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 bestandsparameter.
Nadat u OKhebt geselecteerd, wordt er een nieuwe kolom met de naam Uitvoertabel gemaakt. Deze kolom bevat tabel waarden in de cellen, zoals wordt weergegeven in de volgende afbeelding. Verwijder voor het gemak alle kolommen uit deze tabel, behalve Name en Output Table.
Notitie
Ga naar Kolommen kiezen of verwijderenvoor 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 uitvoertabel uitvouwen kolom 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 Name of Date te controleren. In dit geval kunt u de waarden uit de kolom Date 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 gelezen hebt, is fundamenteel hetzelfde proces als dat plaatsvindt tijdens het Bestanden combineren proces, maar dan handmatig.
U wordt aangeraden ook het artikel te lezen over Overzicht van bestanden combineren en CSV-bestanden combineren om meer inzicht te hebben in de werking van de combinatiebestanden 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 waarin de Land gelijk is aan Panama.
Als u deze vereiste wilt instellen, maakt u een nieuwe parameter met de naam Market met het gegevenstype tekst. Voer voor de huidige waardede waarde Panamain.
Selecteer met deze nieuwe parameter de query Transformeer Voorbeeldbestand en filter het veld Land met behulp van de waarde uit de parameter Markt.
Notitie
Ga naar Filterwaardenvoor meer informatie over het filteren van kolommen op waarden.
Als u deze nieuwe stap toepast op uw query, wordt de functie in het Transform-bestand automatisch bijgewerkt, die nu twee parameters nodig heeft op basis van de twee parameters die uw Transform Voorbeeldbestand gebruikt.
Maar de CSV-bestanden query 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 Transform-bestand tijdens de aangeroepen aangepaste functie stap.
Als u de fouten wilt oplossen, dubbelklikt u op Opgeroepen aangepaste functie in de Toegepaste stappen om het venster Opgeroepen aangepaste functie te openen. Voer in de parameter Market handmatig de waarde Panamain.
U kunt nu terugkeren naar 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-bestanden query.
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 splitsen PTY-CM1090-LAX- in meerdere onderdelen:
- Oorsprong = PTY
- Bestemming = LAX
- Luchtvaartmaatschappij = CM
- FlightID = 1090
De volgende M-code demonstreert deze set van 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 Power Query M-formuletaalvoor meer informatie over de Power Query M-formuletaal.
U kunt die query vervolgens omzetten in een functie door met de rechtermuisknop op de query te klikken en Functie makente 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.
