Bruke egendefinerte funksjoner
Hvis du befinner deg i en situasjon der du må bruke det samme settet med transformasjoner på forskjellige spørringer eller verdier, kan det være nyttig å opprette en egendefinert Power Query-funksjon som kan brukes på nytt så mange ganger du trenger. En egendefinert Power Query-funksjon er en tilordning fra et sett med inndataverdier til én enkelt utdataverdi, og opprettes fra opprinnelige M-funksjoner og operatorer.
Selv om du manuelt kan opprette din egen egendefinerte Power Query-funksjon ved hjelp av kode som beskrevet i Forstå Power Query M-funksjoner, gir Brukergrensesnittet i Power Query deg funksjoner for å øke hastigheten, forenkle og forbedre prosessen med å opprette og administrere en egendefinert funksjon.
Denne artikkelen fokuserer på denne opplevelsen, bare gitt gjennom brukergrensesnittet i Power Query, og hvordan du får mest mulig ut av den.
Viktig
Denne artikkelen beskriver hvordan du oppretter en egendefinert funksjon med Power Query ved hjelp av vanlige transformeringer som er tilgjengelige i brukergrensesnittet i Power Query. Den fokuserer på kjernekonseptene for å opprette egendefinerte funksjoner, og koblinger til flere artikler i Power Query-dokumentasjonen for mer informasjon om bestemte transformeringer som det refereres til i denne artikkelen.
Obs!
Følgende eksempel ble opprettet ved hjelp av skrivebordsopplevelsen som finnes i Power BI Desktop, og kan også følges ved hjelp av Power Query-opplevelsen som finnes i Excel for Windows.
Du kan følge dette eksemplet ved å laste ned eksempelfilene som brukes i denne artikkelen, fra følgende nedlastingskobling. Denne artikkelen bruker mappekoblingen for enkelhet. Hvis du vil ha mer informasjon om mappekoblingen, kan du gå til mappe. Målet med dette eksemplet er å opprette en egendefinert funksjon som kan brukes på alle filene i denne mappen før du kombinerer alle dataene fra alle filene i én enkelt tabell.
Begynn med å bruke mappekoblingsopplevelsen til å navigere til mappen der filene er plassert, og velg Transformer data eller Rediger. Disse trinnene tar deg til Power Query-opplevelsen. Høyreklikk den binære verdien du ønsker fra Innhold-feltet , og velg alternativet Legg til som ny spørring . I dette eksemplet ble valget gjort for den første filen fra listen, som tilfeldigvis er filen 2019.csv.
Dette alternativet oppretter effektivt en ny spørring med et navigasjonstrinn direkte til filen som binær, og navnet på denne nye spørringen er filbanen til den valgte filen. Gi denne spørringen nytt navn som eksempelfil.
Opprett en ny parameter med navnet Filparameter og binærtype. Bruk eksempelfilspørringen som standardverdi og gjeldende verdi.
Obs!
Vi anbefaler at du leser artikkelen om parametere for bedre å forstå hvordan du oppretter og administrerer parametere i Power Query.
Egendefinerte funksjoner kan opprettes ved hjelp av en hvilken som helst parametertype. Det er ingen krav om at en egendefinert funksjon skal ha en binær som parameter.
Den binære parametertypen vises bare i rullegardinmenyen Type for parametere når du har en spørring som evalueres til en binær.
Det er mulig å opprette en egendefinert funksjon uten en parameter. Dette vises vanligvis i scenarioer der inndata kan utledes fra miljøet der funksjonen aktiveres. En funksjon som for eksempel tar miljøets gjeldende dato og klokkeslett, og oppretter en bestemt tekststreng fra disse verdiene.
Høyreklikk Filparameter fra Spørringer-ruten . Velg Referanse-alternativet.
Gi nytt navn til den nylig opprettede spørringen fra Filparameter (2) for å transformere eksempelfilen.
Høyreklikk denne nye transformeringseksempelfilspørringen , og velg alternativet Opprett funksjon .
Denne operasjonen oppretter effektivt en ny funksjon som kobler til transformeringseksempelfilspørringen . Eventuelle endringer du gjør i spørringen for transformeringseksempelfilen , replikeres automatisk til den egendefinerte funksjonen. Under opprettingen av denne nye funksjonen bruker du Transformer fil som funksjonsnavn.
Når du har opprettet funksjonen, kan du legge merke til at en ny gruppe er opprettet for deg med navnet på funksjonen. Denne nye gruppen inneholder:
- Alle parametere som ble referert til i transformeringseksempelfilspørringen .
- Spørringen for transformeringseksemplet , kjent som eksempelspørringen.
- Den nyopprettede funksjonen, i dette tilfellet Transformer fil.
Når den nye funksjonen er opprettet, velger du spørringen med navnet Transformer eksempelfil. Denne spørringen er nå koblet til Transformer fil-funksjonen , slik at eventuelle endringer i denne spørringen gjenspeiles i funksjonen. Denne tilkoblingen er det som kalles konseptet med en eksempelspørring som er koblet til en funksjon.
Den første transformasjonen som må skje med denne spørringen, er en som tolker binærfilen. Du kan høyreklikke binærfilen fra forhåndsvisningsruten og velge CSV-alternativet for å tolke binærfilen som en CSV-fil.
Formatet for alle CSV-filene i mappen er det samme. Alle har en topptekst som strekker seg over de fire første radene. Kolonneoverskriftene er plassert i rad 5, og dataene starter fra rad 6 nedover, som vist i neste bilde.
Det neste settet med transformasjonstrinn som må brukes på transformeringseksempelfilen , er:
Fjern de fire øverste radene – Denne handlingen fjerner radene som regnes som en del av topptekstinndelingen i filen.
Obs!
Hvis du vil lære mer om hvordan du fjerner rader eller filtrerer en tabell etter radplassering, går du til Filtrer etter radplassering.
Hev overskrifter – Topptekstene for den endelige tabellen er nå i den første raden i tabellen. Du kan heve dem som vist i neste bilde.
Når du har forfremmet kolonneoverskriftene, legger Power Query som standard automatisk til et nytt endringstypetrinn som automatisk oppdager datatypene for hver kolonne. Transformer eksempelfilspørringen ser ut som det neste bildet.
Obs!
Hvis du vil lære mer om hvordan du hever og senker topptekster, går du til Hev nivå eller senker kolonneoverskrifter.
Forsiktig
Transformer fil-funksjonen er avhengig av trinnene som utføres i spørringen Transformer eksempelfil . Hvis du imidlertid prøver å endre koden manuelt for Transformer fil-funksjonen , vil du bli møtt med en advarsel som leser 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'.
Med den egendefinerte funksjonen nå opprettet og alle transformasjonstrinnene innlemmet, kan du gå tilbake til den opprinnelige spørringen der du har listen over filer fra mappen (CSV-filer i dette eksemplet). Velg Aktiver egendefinert funksjon fra Generelt-gruppen på fanen Legg til kolonne på båndet. Skriv inn Utdatatabell som nytt kolonnenavn i vinduet Aktiver egendefinert funksjon. Velg navnet på funksjonen, Transformer fil, fra rullegardinlisten funksjonsspørring . Når du har valgt funksjonen fra rullegardinmenyen, vises parameteren for funksjonen, og du kan velge hvilken kolonne fra tabellen som skal brukes som argument for denne funksjonen. Velg Innhold-kolonnen som verdi/argument som skal sendes for filparameteren.
Når du har valgt OK, opprettes en ny kolonne med navnet Utdatatabell . Denne kolonnen har tabellverdier i cellene, som vist i neste bilde. Du kan enkelt fjerne alle kolonner fra denne tabellen unntatt Navn og Utdatatabell.
Obs!
Hvis du vil lære mer om hvordan du velger eller fjerner kolonner fra en tabell, kan du gå til Velg eller fjern kolonner.
Funksjonen ble brukt på hver eneste rad fra tabellen ved hjelp av verdiene fra Innhold-kolonnen som argument for funksjonen. Nå som dataene transformeres til figuren du leter etter, kan du utvide utdatatabellkolonnen ved å velge Utvid-ikonet. Ikke bruk noe prefiks for de utvidede kolonnene.
Du kan bekrefte at du har data fra alle filene i mappen ved å kontrollere verdiene i kolonnen Navn eller Dato . I dette tilfellet kan du kontrollere verdiene fra Dato-kolonnen , siden hver fil bare inneholder data for én måned fra et gitt år. Hvis du ser mer enn én, betyr det at du har kombinert data fra flere filer til én enkelt tabell.
Obs!
Det du har lest så langt, er i bunn og grunn den samme prosessen som skjer under kombinering av filer , men gjort manuelt.
Vi anbefaler at du også leser artikkelen om oversikt over kombiner filer og kombinerer CSV-filer for å forstå hvordan kombinering av filer fungerer i Power Query og rollen som egendefinerte funksjoner spiller av.
Tenk deg at det er et nytt krav på toppen av det du har bygget for øyeblikket. Det nye kravet krever at før du kombinerer filene, filtrerer du dataene i dem for å bare få radene der landet er lik Panama.
Hvis du vil at dette kravet skal skje, oppretter du en ny parameter kalt Marked med tekstdatatypen. Angi verdien Panama for gjeldende verdi.
Med denne nye parameteren velger du spørringen Transformer eksempelfil og filtrerer Land-feltet ved hjelp av verdien fra markedsparameteren.
Obs!
Hvis du vil lære mer om hvordan du filtrerer kolonner etter verdier, kan du gå til Filtrer verdier.
Hvis du bruker dette nye trinnet i spørringen , oppdateres transformeringsfilfunksjonen automatisk, som nå krever to parametere basert på de to parameterne som transformeringseksempelfilen bruker.
Men CSV-filspørringen har et advarselstegn ved siden av seg. Nå som funksjonen er oppdatert, krever den to parametere. Så trinnet der du aktiverer funksjonen resulterer i feilverdier, siden bare ett av argumentene ble sendt til Transformer fil-funksjonen under trinnet Aktiver egendefinert funksjon .
Hvis du vil løse feilene, dobbeltklikker du Påkalt egendefinert funksjon i brukte trinn for å åpne vinduet Aktiver egendefinert funksjon . Angi verdien Panama manuelt i markedsparameteren.
Nå kan du gå tilbake til utvidet utdatatabell i brukte trinn. Kontroller spørringen for å bekrefte at bare rader der Land er lik Panama , vises i det endelige resultatsettet for CSV-filer-spørringen .
Hvis du har flere spørringer eller verdier som krever samme sett med transformasjoner, kan du opprette en egendefinert funksjon som fungerer som en gjenbrukbar logikk. Senere kan denne egendefinerte funksjonen aktiveres mot spørringene eller verdiene du ønsker. Denne egendefinerte funksjonen kan spare deg for tid og hjelpe deg med å administrere settet med transformasjoner på en sentral plassering, som du kan endre når som helst.
Tenk deg for eksempel en spørring som har flere koder som en tekststreng, og du vil opprette en funksjon som dekoder disse verdiene, som i følgende eksempeltabell:
kode |
---|
PTY-CM1090-LAX |
LAX-CM701-PTY |
PTY-CM4441-MIA |
MIA-UA1257-LAX |
LAX-XY2842-MIA |
Du begynner med å ha en parameter som har en verdi som fungerer som et eksempel. I dette tilfellet er det verdien PTY-CM1090-LAX.
Fra denne parameteren oppretter du en ny spørring der du bruker transformasjonene du trenger. I dette tilfellet vil du dele koden PTY-CM1090-LAX i flere komponenter:
- Origin = PTY
- Mål = LAX
- Flyselskap = CM
- FlightID = 1090
Følgende M-kode demonstrerer dette settet med transformasjoner.
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"
Obs!
Hvis du vil lære mer om power query M-formelspråket, kan du gå til Power Query M-formelspråket.
Deretter kan du transformere spørringen til en funksjon ved å høyreklikke på spørringen og velge Opprett funksjon. Til slutt kan du aktivere den egendefinerte funksjonen i alle spørringer eller verdier, som vist i neste bilde.
Etter noen flere transformasjoner kan du se at du har nådd ønsket utdata og brukt logikken for en slik transformasjon fra en egendefinert funksjon.