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.

Selvom du manuelt kan oprette din egen brugerdefinerede funktion i Power Query ved hjælp af kode, som beskrevet i Om M-funktioner i Power Query, tilbyder Power Query-brugergrænsefladen dig funktioner, der gør det hurtigere, forenkler og forbedrer processen med at oprette og administrere en brugerdefineret funktion.

I denne artikel fokuseres der på denne oplevelse, som kun leveres via Power Query-brugergrænsefladen, og hvordan du får mest ud af den.

Vigtigt

I denne artikel beskrives det, hvordan du opretter en brugerdefineret funktion med Power Query ved hjælp af almindelige transformationer, der er tilgængelige i brugergrænsefladen i Power Query. Der fokuseres på kernebegreberne til oprettelse af brugerdefinerede funktioner og links til yderligere artikler i power-forespørgselsdokumentationen for at få flere oplysninger om specifikke transformeringer, der henvises til i denne artikel.

Opret en brugerdefineret funktion ud fra en tabelreference

Bemærk

Følgende eksempel blev oprettet ved hjælp af skrivebordsoplevelsen i Power BI Desktop og kan også følges ved hjælp af Power Query-oplevelsen, der findes i Excel til Windows.

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, du vælger, 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 File Parameter og typen Binary. Brug forespørgslen Eksempelfil som standardværdi og aktuel værdi.

Bemærk

Vi anbefaler, at du læser artiklen om Parametre for bedre at forstå, hvordan du opretter og administrerer parametre i Power Query.

Brugerdefinerede funktioner kan oprettes ved hjælp af en hvilken som helst parametertype. Der er ikke noget krav om, at en brugerdefineret funktion skal have en binær værdi som en parameter.

Den binære parametertype vises kun i rullemenuen Type i dialogboksen Parametre, når du har en forespørgsel, der evalueres til en binær.

Det er muligt at oprette en brugerdefineret funktion uden en parameter. Dette ses ofte i scenarier, hvor et input kan udledes fra det miljø, hvor funktionen aktiveres. En funktion, der f.eks. tager miljøets aktuelle dato og klokkeslæt og opretter en bestemt tekststreng ud fra disse værdier.

Højreklik på Filparameter i ruden Forespørgsler . Vælg indstillingen Reference .

Omdøb den nyoprettede forespørgsel fra Filparameter (2) til Transformér eksempelfil.

Højreklik på denne nye transformeringseksempelfilforespørgsel , og vælg indstillingen Opret funktion .

Denne handling opretter effektivt en ny funktion, der er sammenkædet med forespørgslen Transformér eksempelfil . Alle ændringer, du foretager i forespørgslen Omdan eksempel på fil , 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 forespørgslen transformér eksempelfil .
• Din transformationseksempelfilforespørgsel, der ofte kaldes eksempelforespørgslen.
• Din 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 eksempelfil. Denne forespørgsel er nu sammenkædet med funktionen Transformér fil , så alle ændringer, der foretages i 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 visningsruden og vælge CSV-indstillingen 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 øverste fire rækker – denne handling fjerner de rækker, der betragtes som en del af sidehovedsektionen i filen.

   

   Bemærk

   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 et nyt trin af typen Ændret type , der automatisk registrerer datatyperne for hver kolonne. Forespørgslen transformér eksempelfil ligner det næste billede.

Bemærk

Hvis du vil vide mere om, hvordan du hæver og sænker overskrifter, skal du gå til Hæv eller sænk kolonneoverskrifter.

Advarsel

Funktionen Transformér fil er afhængig af de trin, der udføres i forespørgslen Transformér eksempelfil . Men hvis du forsøger at ændre koden for funktionen Transformér fil manuelt, får du vist en advarsel, der læser 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'.

Aktivér en brugerdefineret funktion som en ny kolonne

Når den brugerdefinerede funktion nu er oprettet, og alle transformationstrinnene er indarbejdet, 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 kolonne 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 kolonnenavn. Vælg navnet på funktionen Transformér fil på rullelisten Funktionsforespø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 filparameteren.

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

Bemærk

Hvis du vil vide mere om, hvordan du vælger eller fjerner kolonner fra en tabel, skal du gå til Vælg eller fjern kolonner.

Din funktion blev anvendt på hver enkelt række fra tabellen ved hjælp af værdierne fra kolonnen Content som argument for din funktion. 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 Navn eller Dato . 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.

Bemærk

Det, du har læst indtil videre, er grundlæggende den samme proces, der sker under oplevelsen Kombiner filer , men som udføres manuelt.

Vi anbefaler, at du også læser artiklen om Oversigt over kombiner filer og Kombiner CSV-filer for yderligere at forstå, hvordan oplevelsen med kombiner filer fungerer i Power Query, og hvilken rolle brugerdefinerede funktioner spiller.

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 få de rækker, hvor Country er lig med Panama.

Du kan få dette krav til at ske ved at oprette en ny parameter med navnet Market med tekstdatatypen. Angiv værdien Panama for den aktuelle værdi.

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

Bemærk

Hvis du vil vide mere om, hvordan du filtrerer kolonner efter værdier, skal du gå til Filterværdier.

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 transformeringseksempelfilen bruger.

Men forespørgslen om CSV-filer har et advarselstegn ud for den. Nu, hvor din funktion er opdateret, kræver den to parametre. Så det trin, hvor du aktiverer funktionen, resulterer i fejlværdier, da kun ét af argumenterne blev overført til funktionen Transformér fil under trinnet Med aktiveret brugerdefineret funktion .

Hvis du vil rette fejlene, skal du dobbeltklikke på Aktiveret brugerdefineret funktion i Anvendte trin for at åbne vinduet Aktivér brugerdefineret funktion . Angiv værdien Panama manuelt i parameteren Market.

Du kan nu vende tilbage til udvidet outputtabel i Anvendte trin. Kontrollér din forespørgsel for at validere, at det kun er rækker, hvor Country er lig med Panama , der 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:

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

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
• Flyselskab = 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"

Bemærk

Hvis du vil vide mere om M-formelsproget i Power Query, skal du gå til M-formelsproget i Power Query.

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.