Brug af brugerdefinerede funktioner

Hvis du befinder dig i en situation, hvor du har brug for at anvende det samme sæt transformationer på forskellige forespørgsler eller værdier, kan det være en fordel at oprette en brugerdefineret funktion i Power Query, der kan genbruges så mange gange, du har brug for. En brugerdefineret funktion i Power Query er en tilknytning fra et sæt inputværdier til en enkelt outputværdi og oprettes ud fra oprindelige M-funktioner og -operatorer.

Du kan manuelt oprette din egen brugerdefinerede Funktion i Power Query ved hjælp af Power Query M-formelsproget, eller brugergrænsefladen i Power Query giver dig funktioner, der kan fremskynde, forenkle og forbedre processen med at oprette og administrere en brugerdefineret funktion.

Først gennemgår vi de grundlæggende trin til oprettelse af en brugerdefineret funktion med kode ifor brugergrænsefladen, så fokuserer vi på at bruge grænsefladen til at omdanne komplekse handlinger til en funktion, der kan genbruges.

Opret en brugerdefineret funktion ud fra kode i brugergrænsefladen

1. Brug connectoroplevelsen til at oprette forbindelse til dine data, hvor de er gemt. Når du har valgt dine data, skal du vælge knappen Transformér data eller knappen Rediger. Dette fører dig til Power Query-oplevelsen.
2. Højreklik på et tomt sted i ruden Forespørgsler til venstre.
3. Vælg Tom forespørgsel.
4. I det nye tomme forespørgselsvindue skal du vælge menuen Start og derefter Avanceret editor.
5. Erstat skabelonen med din brugerdefinerede funktion. For eksempel:

   let
    HelloWorld = () => ("Hello World")
   in
    HelloWorld
   

6. Vælg Udført.

Du kan få flere oplysninger om udvikling af brugerdefinerede funktioner med M-formelsproget i Power Query i denne artikel: Om M-funktioner i Power Query. I følgende afsnit er der selvstudier, der beskriver, hvordan du kan bruge Power Query-brugergrænsefladen til at udvikle brugerdefinerede funktioner uden at skrive kode, og instruktioner til, hvordan du kan aktivere din brugerdefinerede funktion i din forespørgsel.

Opret en brugerdefineret funktion ud fra et selvstudium om tabelreference

Du kan følge med i dette eksempel ved at downloade de eksempelfiler, der bruges i denne artikel, fra følgende downloadlink. For nemheds skyld bruges mappeconnectoren i denne artikel. Hvis du vil vide mere om mappeconnectoren, skal du gå til Mappe. Målet med dette eksempel er at oprette en brugerdefineret funktion, der kan anvendes på alle filerne i den pågældende mappe, før du kombinerer alle dataene fra alle filer i en enkelt tabel.

Start med at bruge mappeconnectoroplevelsen til at navigere til den mappe, hvor filerne er placeret, og vælg Transformér data eller Rediger. Disse trin fører dig til Power Query-oplevelsen. Højreklik på den binære værdi efter eget valg i feltet Indhold, og vælg indstillingen Tilføj som ny forespørgsel. I dette eksempel blev valget foretaget for den første fil på listen, hvilket er filen april 2019.csv.

Denne indstilling opretter effektivt en ny forespørgsel med et navigationstrin direkte til filen som binær, og navnet på den nye forespørgsel er filstien til den valgte fil. Omdøb denne forespørgsel til eksempelfil.

Opret en ny parameter med navnet filparameter og typen af binær. Brug forespørgslen eksempelfilen som standardværdi og aktuel værdi.

Højreklik filparameter i ruden forespørgsler. Vælg indstillingen Reference.

Omdøb den nyligt oprettede forespørgsel fra filparameter (2) for at transformér eksempelfilen.

Højreklik på denne nye Transformér eksempelfil forespørgsel, og vælg indstillingen Opret funktion.

Denne handling opretter effektivt en ny funktion, der er sammenkædet med transformér eksempelfilen forespørgsel. De ændringer, du foretager i transformér eksempelfilen forespørgsel, replikeres automatisk til din brugerdefinerede funktion. Under oprettelsen af denne nye funktion skal du bruge transformér fil som Funktionsnavn.

Når du har oprettet funktionen, kan du se, at der oprettes en ny gruppe med navnet på funktionen. Denne nye gruppe indeholder:

• Alle parametre, der blev refereret til i transformér eksempelfilen, forespørgsel.
• Din transformér eksempelfil forespørgsel, der ofte kaldes eksempelforespørgsel.
• Den nyoprettede funktion i dette tilfælde transformér fil.

Anvendelse af transformationer på en eksempelforespørgsel

Når den nye funktion er oprettet, skal du vælge forespørgslen med navnet transformér eksempelfilen. Denne forespørgsel er nu sammenkædet med funktionen Transformér fil, så eventuelle ændringer af denne forespørgsel afspejles i funktionen. Denne forbindelse er det, der kaldes begrebet en eksempelforespørgsel, der er knyttet til en funktion.

Den første transformation, der skal ske med denne forespørgsel, er en, der fortolker den binære fil. Du kan højreklikke på den binære fil i eksempelruden og vælge indstillingen CSV- for at fortolke den binære fil som en CSV-fil.

Formatet af alle CSV-filer i mappen er det samme. De har alle en overskrift, der strækker sig over de første øverste fire rækker. Kolonneoverskrifterne er placeret i række 5, og dataene starter fra række 6 nedad, som vist på det næste billede.

Det næste sæt transformationstrin, der skal anvendes på transformér eksempelfilen er:

1. Fjern de fire øverste rækker– Denne handling fjerner de rækker, der betragtes som en del af sidehovedsektionen i filen.

   

   Seddel

   Hvis du vil vide mere om, hvordan du fjerner rækker eller filtrerer en tabel efter rækkeplacering, skal du gå til Filtrer efter rækkeplacering.

2. Hæv overskrifter– Overskrifterne til den endelige tabel er nu i den første række i tabellen. Du kan fremhæve dem som vist på det næste billede.

   

Når du har fremhævet kolonneoverskrifterne, tilføjer Power Query som standard automatisk en ny Ændret type trin, der automatisk registrerer datatyperne for hver kolonne. Din Transformér eksempelfil forespørgsel ligner det næste billede.

Aktivér en brugerdefineret funktion som en ny kolonne

Når den brugerdefinerede funktion nu er oprettet, og alle transformationstrinnene er indbygget, kan du gå tilbage til den oprindelige forespørgsel, hvor du har listen over filer fra mappen (CSV-filer i dette eksempel). På fanen Tilføj på båndet skal du vælge Aktivér brugerdefineret funktion i gruppen Generelt. I vinduet Aktivér brugerdefineret funktion skal du angive outputtabel som Nyt kolonnenavn. Vælg navnet på funktionen Transformér filpå rullelisten Function-forespørgsel. Når du har valgt funktionen i rullemenuen, vises parameteren for funktionen, og du kan vælge, hvilken kolonne i tabellen der skal bruges som argument for denne funktion. Vælg kolonnen Content som den værdi/det argument, der skal overføres for File Parameter.

Når du har valgt OK, oprettes der en ny kolonne med navnet Outputtabel. Denne kolonne har Tabel værdier i cellerne, som vist på det næste billede. For nemheds skyld skal du fjerne alle kolonner fra denne tabel undtagen Name og Outputtabel.

Din funktion blev anvendt på hver enkelt række i tabellen ved hjælp af værdierne fra kolonnen Content som argument for funktionen. Nu, hvor dataene er transformeret til den figur, du leder efter, kan du udvide kolonnen Outputtabel ved at vælge ikonet Udvid. Brug ikke præfikset for de udvidede kolonner.

Du kan kontrollere, at du har data fra alle filer i mappen ved at kontrollere værdierne i kolonnen Name eller Date. I dette tilfælde kan du kontrollere værdierne fra kolonnen Dato, da hver fil kun indeholder data for en enkelt måned fra et givet år. Hvis du kan se mere end én, betyder det, at du har kombineret data fra flere filer til en enkelt tabel.

Føj en ny parameter til eksisterende brugerdefineret funktion

Forestil dig, at der er et nyt krav ud over det, du i øjeblikket har bygget. Det nye krav kræver, at før du kombinerer filerne, filtrerer du dataene i dem for kun at hente de rækker, hvor Country- er lig med Panama-.

Hvis dette krav skal ske, skal du oprette en ny parameter med navnet Market med tekstdatatypen. For Aktuel værdiskal du angive værdien Panama.

Med denne nye parameter skal du vælge forespørgslen Transformér eksempelfil og filtrere feltet Land ved hjælp af værdien fra parameteren Market.

Hvis du anvender dette nye trin på din forespørgsel, opdateres funktionen Transformér fil automatisk, hvilket nu kræver to parametre baseret på de to parametre, som transformér eksempelfilen bruger.

Men forespørgslen CSV-filer har et advarselstegn ud for den. Nu, hvor din funktion er opdateret, kræver den to parametre. Det trin, hvor du aktiverer funktionen, resulterer derfor i fejlværdier, da kun ét af argumenterne blev overført til funktionen transformér fil under aktiverede brugerdefinerede funktion trin.

Hvis du vil rette fejlene, skal du dobbeltklikke på aktiverede brugerdefinerede funktion i Anvendte trin for at åbne vinduet Invoke Custom Function. Angiv værdien Panamamanuelt i parameteren Market .

Du kan nu vende tilbage til udvidede outputtabel i Anvendte trin. Kontrollér din forespørgsel for at validere, at det kun er rækker, hvor Country er lig med Panama- vises i det endelige resultatsæt for forespørgslen CSV-filer.

Opret en brugerdefineret funktion ud fra en logik, der kan genbruges

Hvis du har flere forespørgsler eller værdier, der kræver det samme sæt transformationer, kan du oprette en brugerdefineret funktion, der fungerer som en logik, der kan genbruges. Senere kan denne brugerdefinerede funktion aktiveres i forhold til de forespørgsler eller værdier, du vælger. Denne brugerdefinerede funktion kan spare dig tid og hjælpe dig med at administrere dine transformationer på en central placering, som du kan ændre når som helst.

Forestil dig f.eks. en forespørgsel, der har flere koder som en tekststreng, og du vil oprette en funktion, der afkoder disse værdier, som i følgende eksempeltabel:

Du starter med at have en parameter, der har en værdi, der fungerer som et eksempel. I dette tilfælde er det værdien PTY-CM1090-LAX-.

Fra denne parameter opretter du en ny forespørgsel, hvor du anvender de transformationer, du har brug for. I dette tilfælde vil du opdele koden PTY-CM1090-LAX- i flere komponenter:

• oprindelse = PTY
• destination = LAX
• Airline = CM
• FlightID = 1090

Følgende M-kode viser dette sæt transformationer.

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"

Du kan derefter transformere forespørgslen til en funktion ved at højreklikke på forespørgslen og vælge Opret funktion. Endelig kan du aktivere din brugerdefinerede funktion i en hvilken som helst af dine forespørgsler eller værdier, som vist på det næste billede.

Efter nogle få transformationer kan du se, at du har nået det ønskede output og anvendt logikken for en sådan transformation fra en brugerdefineret funktion.