Lue englanniksi

Jaa


Mukautettujen funktioiden käyttäminen

Jos olet tilanteessa, jossa sinun on käytettävä samoja muunnoksia eri kyselyihin tai arvoihin, mukautetun Power Query -funktion luominen voidaan käyttää uudelleen niin monta kertaa kuin tarvitaan. Power Queryn mukautettu funktio on yhdistämismääritys syötearvojen ryhmästä yksittäiseen tulostearvoon, ja se on luotu alkuperäisistä M-funktioista ja -operaattoreista.

Vaikka voit luoda mukautetun Power Query -funktion manuaalisesti koodilla, joka on kuvattu artikkelissa Power Query M -funktioiden ymmärtäminen, Power Queryn käyttöliittymä tarjoaa ominaisuuksia, joilla voit nopeuttaa, yksinkertaistaa ja parantaa mukautetun funktion luonti- ja hallintaprosessia.

Tässä artikkelissa keskitytään tähän kokemukseen, joka tarjotaan vain Power Query -käyttöliittymän kautta, ja siihen, miten voit hyödyntää sitä edempänä.

Tärkeä

Tässä artikkelissa kerrotaan, miten voit luoda mukautetun funktion Power Queryn avulla käyttämällä yleisiä muunnoksia, joita voi käyttää Power Query -käyttöliittymässä. Siinä keskitytään keskeisiin käsitteisiin ja luodaan mukautettuja funktioita. Lisäksi se sisältää linkkejä Power Query -ohjeartikkeleihin, joissa on lisätietoja tässä artikkelissa viitatuksista erityisistä muunnuksista.

Mukautetun funktion luominen taulukkoviittauksen avulla

Huomautus

Seuraavassa esimerkissä käytetään Power BI Desktopin työpöytäversiota. Sitä voi myös seurata käyttämällä Power Query -käyttökokemusta, joka löytyy Excel for Windowsista.

Voit seurata tätä esimerkkiä lataamalla tässä artikkelissa käytetyt mallitiedostot seuraavasta latauslinkistä. Selvyyden vuoksi tässä artikkelissa käytetään Kansio-yhdistintä. Saat lisätietoja Kansio-liittimestä kansiosta. Tämän esimerkin tavoitteena on luoda mukautettu funktio, jota voidaan käyttää kaikkiin kyseisen kansion tiedostoihin, ennen kuin kaikkien tiedostojen kaikki tiedot yhdistetään yhdeksi taulukoksi.

Aloita kansioyhdistimen avulla siirtymällä kansioon, jossa tiedostot sijaitsevat, ja valitse Muunna tiedot tai Muokkaa. Nämä vaiheet vievät sinut Power Query -käyttökokemukseen. Napsauta hiiren kakkospainikkeella valitsemaasi Binaariarvoa Sisältö-kentässä ja valitse Lisää uutena kyselynä -vaihtoehto. Tässä esimerkissä valittiin luettelosta ensimmäinen tiedosto, joka sattuu olemaan tiedosto 2019.csv.

Näyttökuva siitä, miten voit valita tiedoston mallitiedostoksi.

Tämä vaihtoehto luo käytännössä uuden kyselyn, jossa on siirtymisvaihe suoraan kyseiseen tiedostoon binaarimuodossa, ja tämän uuden kyselyn nimi on valitun tiedoston tiedostopolku. Nimeä tämä kysely uudelleen nimellä Mallitiedosto.

Näyttökuva, joka näyttää uuden mallitiedostokyselyn.

Luo uusi parametri , jonka nimi on TiedostoParametri ja tyyppi Binary. Käytä mallitiedostokyselyä oletusarvona ja nykyisenä arvona.

Näyttökuva, jossa on täytetty tiedoston parametriarvot.

Huomautus

Suosittelemme, että luet Parametrit-artikkelin , jonka avulla opit paremmin luomaan ja hallitsemaan parametreja Power Queryssa.

Mukautettuja funktioita voidaan luoda millä tahansa parametrityypillä. Mukautetuilla funktioilla ei tarvitse olla parametrina binaaria.

Binaariparametrityyppi näkyy vain Parametrit-valintaikkunan avattavassa Tyyppi-valikossa, kun sinulla on kysely, joka antaa tulokseksi binaariarvon.

Mukautettu funktio voidaan luoda ilman parametria. Tätä esiintyy yleensä tilanteissa, joissa syöte voidaan johtaa ympäristöstä, jossa funktiota käynnistetään. Esimerkiksi funktio, joka ottaa ympäristön nykyisen päivämäärän ja kellonajan ja luo kyseisistä arvoista tietyn tekstimerkkijonon.

Napsauta hiiren kakkospainikkeella Kyselyt-ruudun Tiedostoparametri-kohtaa. Valitse Viite-vaihtoehto.

Näyttökuva, jossa Viittaus-vaihtoehto on valittuna Tiedosto-parametrille.

Anna juuri luodun kyselyn nimeksi Tiedostoparametri (2) ja Muunna mallitiedosto.

Näyttökuva, jossa on nimetty uudelleen Muunna mallitiedosto -kysely.

Napsauta hiiren kakkospainikkeella tätä uutta Muunna mallitiedosto - kyselyä ja valitse Luo funktio - vaihtoehto.

Näyttökuva Muunna malli -tiedostokyselyssä käytetystä Luo funktio -vaihtoehdosta.

Tämä toiminto luo tehokkaasti uuden funktion, joka linkittää Muunna mallitiedosto -kyselyyn. Muunna mallitiedostoa -kyselyyn tekemäsi muutokset replikoidaan automaattisesti mukautetulle funktiolle. Käytä uuden funktion luomisen aikana Muunna-tiedostoa funktion nimenä.

Näyttökuva Muunna-tiedoston Luo funktio -ikkunasta.

Kun olet luonut funktion, näet, että sinulle luodaan uusi ryhmä funktion nimellä. Tämä uusi ryhmä sisältää:

  • Kaikki parametrit, joihin on viitattu Muunna malli -tiedostokyselyssä .
  • Muunna mallitiedostoa -kysely, jota kutsutaan yleisesti mallikyselyksi.
  • Juuri luotu funktio, tässä tapauksessa Muunna tiedosto.

Näyttökuva funktioryhmästä Kyselyt-ruudussa.

Muunnosten käyttäminen mallikyselyssä

Kun uusi funktio on luotu, valitse kysely nimellä Muunna mallitiedosto. Tämä kysely on nyt linkitetty Transform file - funktioon, joten tähän kyselyyn tehdyt muutokset näkyvät funktiossa. Tätä yhteyttä kutsutaan funktioon linkitetyn mallikyselyn käsitteeksi.

Ensimmäinen muunnos, jonka on tapahduttava tälle kyselylle, on sellainen, joka tulkitsee binaariarvon. Voit napsauttaa binaaria hiiren kakkospainikkeella esikatseluruudussa ja valita CSV-vaihtoehdon , jolla binaari tulkitaan CSV-tiedostoksi.

Näyttökuva binaarivalikon avattavasta valikosta, jossa korostetaan CSV-tiedostoa.

Kansiossa olevien kaikkien CSV-tiedostojen muoto on sama. Niissä kaikissa on otsikko, joka kattaa neljä ensimmäistä riviä. Sarakeotsikot sijaitsevat rivillä 5 ja tiedot alkavat riviltä 6 alaspäin seuraavassa kuvassa esitetyllä tavalla.

Näyttökuva CSV-mallitiedoista ennen käsittelyä.

Seuraavat muunnosvaiheet, jotka on otettava käyttöön Muunna malli - tiedostossa , ovat seuraavat:

  1. Poista neljä ylintä riviä – Tällä toiminnolla päästään eroon riveistä, joita pidetään tiedoston otsikko-osiossa.

    Näyttökuva, kun ylimmät rivit on poistettu Muunna malli -tiedostosta.

    Huomautus

    Lisätietoja siitä, miten voit poistaa rivejä tai suodattaa taulukon rivin sijainnin mukaan, siirry kohtaan Suodata rivin sijainnin mukaan.

  2. Ylennä otsikot – lopullisen taulukon otsikot ovat nyt taulukon ensimmäisellä rivillä. Voit ylentää ne seuraavassa kuvassa esitetyllä tavalla.

    Näyttökuva ensimmäisen rivin jälkeen on otsikot.

Kun olet ylentänyt sarakeotsikot, Power Query lisää oletusarvoisesti automaattisesti uuden Changed Type - vaiheen, joka tunnistaa automaattisesti kunkin sarakkeen tietotyypit. Muunna mallitiedostoa -kyselysi näyttää samalta kuin seuraavassa kuvassa.

Huomautus

Jos haluat lisätietoja otsikoiden ylentämisestä ja alentamisesta, siirry sarakeotsikoiden ylentämiseen tai alentamiseen.

Näyttökuva lopullisesta muunnosmallikyselystä.

Huomautus

Muunna tiedosto -funktio käyttää Transform Sample -tiedostokyselyssä suoritettuja vaiheita. Jos kuitenkin yrität muokata manuaalisesti Transform file -funktion koodia, saat varoituksen, jossa lukeeThe 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'.

Kutsu mukautettua funktiota uutena sarakkeena

Nyt kun mukautettu funktio on luotu ja kaikki muunnosvaiheet on sisällytetty, voit palata alkuperäiseen kyselyyn, jossa on kansion tiedostojen luettelo (tässä esimerkissä CSV-tiedostot ). Valitse valintanauhan Lisää sarake -välilehdeltä Kutsu mukautettua funktiota Yleiset-ryhmästä. Kirjoita Kutsu mukautettua funktiota -ikkunassa Tulostetaulukko Uuden sarakkeen nimeksi. Valitse funktion nimi, Muunna tiedosto avattavasta Funktiokysely-valikosta. Kun valitset funktion avattavasta valikosta, funktion parametri näytetään ja voit valita, mitä saraketta taulukosta käytetään argumenttina tälle funktiolle. Valitse Sisältö-sarake Tiedosto-parametrille välitettäväksi arvoksi/argumentiksi.

Näyttökuva, jossa Kutsu mukautettua funktiota -painike on korostettu kutsu mukautetun funktion valintaikkunan asetuksissa.

Kun olet valinnut OK, uusi sarake, jonka nimi on Output Table , luodaan. Tämän sarakkeen soluissa on Taulukko-arvot seuraavassa kuvassa esitetyllä tavalla. Yksinkertaisuuden vuoksi poista kaikki sarakkeet tästä taulukosta lukuun ottamatta Nimi - ja Tulostetaulukko-sarakkeita.

Näyttökuva, jossa on käynnistetty mukautettu funktio ja vain Name- ja Output Table -sarakkeet ovat jäljellä.

Huomautus

Saat lisätietoja sarakkeiden valitsemisesta tai poistamisesta taulukosta artikkelista Sarakkeiden valitseminen tai poistaminen.

Funktiotasi käytettiin taulukon jokaisella rivillä käyttäen Sisältö-sarakkeen arvoja funktion argumenttina. Nyt kun tiedot on muunnettu haluamaasi muotoon, voit laajentaa Output Table -sarakkeen valitsemalla Laajenna-kuvakkeen. Älä käytä etuliitettä laajennetuissa sarakkeissa.

Näyttökuva laajennetun tulostetaulukon valintaikkunasta, jossa on valittuna kaikki taulukon sarakkeet.

Voit varmistaa, että sinulla on tietoja kaikista kansiossa olevista tiedostoista, tarkistamalla Nimi- tai Päivämäärä-sarakkeen arvot. Tässä tapauksessa voit tarkistaa arvot Date-sarakkeesta, koska jokainen tiedosto sisältää tietoja vain yhden kuukauden ajalta tietystä vuodesta. Jos näet useamman kuin yhden, se tarkoittaa, että olet yhdistänyt onnistuneesti tietoja useista tiedostoista yhdeksi taulukoksi.

Näyttökuva, jossa on päivämäärän avattava valikko, joka vahvistaa, että lopullinen taulukko sisältää tietoja kaikista tiedostoista.

Huomautus

Tähän mennessä olet lukenut saman prosessin, joka suoritetaan Yhdistä tiedostot -toiminnossa, mutta tehdään manuaalisesti.

Suosittelemme, että luet myös Yhdistä tiedostot -yleiskatsauksen ja CSV-tiedostojen yhdistäminen -artikkelin, jossa kerrotaan tarkemmin, miten tiedostojen yhdistäminen toimii Power Queryssä ja miten mukautetuilla funktioilla on oma roolinsa.

Lisää uusi parametri olemassa olevaan mukautettuun funktioon

Kuvittele, että tällä hetkellä rakentamasi lisäksi vaatimuksena on uusi. Uusi vaatimus edellyttää, että ennen tiedostojen yhdistämistä suodatat tiedot niiden sisällä saadaksesi vain rivit, joiden maa on Panaman tasoinen.

Jotta tämä vaatimus toteutuu, luo uusi parametri nimeltä Market tekstitietotyypillä. Anna nykyiselle arvolle arvo Panama.

Näyttökuva uudesta parametrista, jossa on tarvittavat arvot.

Valitse tämän uuden parametrin avulla Muunna mallitiedosto -kysely ja suodata Maa-kenttä käyttämällä Markkina-parametrin arvoa.

Näyttökuva suodata rivit -valintaikkunasta, jossa suodata maa -sarake käyttää uutta Markkina-parametria.

Huomautus

Jos haluat lisätietoja sarakkeiden suodattamisesta arvojen mukaan, siirry kohtaan Suodata arvot.

Tämän uuden vaiheen käyttäminen kyselyssä päivittää automaattisesti Transform file -funktion, joka edellyttää nyt kahta parametria Transform Sample -tiedostosi käyttämien kahden parametrin perusteella.

Näyttökuva funktiosta, joka on nyt päivitetty kahdella parametrilla.

CSV-tiedostojen kyselyn vieressä on kuitenkin varoitusmerkki. Funktion päivittäminen edellyttää nyt kahta parametria. Vaihe, jossa kutsut funktion, tuottaa siis virhearvoja, koska vain yksi argumenteista välitettiin Transform file -funktioon Käynnistetty mukautettu funktio -vaiheen aikana.

Näyttökuva virhesanomasta, joka näytetään funktiopäivityksen jälkeen.

Voit korjata virheet kaksoisnapsauttamalla käynnistettyä mukautettua funktiota Käytössä olevat vaiheet -kohdassa, jolloin Kutsu mukautettua funktiota -ikkuna avautuu. Syötä Market-parametriin arvo Panama manuaalisesti.

Näyttökuva päivitetyistä käynnistettyjen mukautettujen funktioiden argumenteista.

Voit nyt palata Laajennettu tuloste -taulukkoon Käytössä olevat vaiheet - kohdassa. Tarkista kyselysi ja varmista, että VAIN rivit, joiden maa on Panama, näkyvät CSV-tiedostot-kyselyn lopullisessa tulosjoukossa.

Näyttökuva lopullisesta tulostetaulukosta päivitettyjen argumenttien jälkeen.

Mukautetun funktion luominen uudelleenkäytettävästä logiikasta

Jos sinulla on useita kyselyjä tai arvoja, jotka edellyttävät samoja muunnoksia, voit luoda mukautetun funktion, joka toimii uudelleenkäytettävänä logiikkayksikkönä. Myöhemmin tämä mukautettu funktio voidaan käynnistää käyttämällä kyselyjä tai valitsemiasi arvoja. Tämä mukautettu funktio voi säästää aikaa ja auttaa sinua hallitsemaan muunnosjoukkoasi keskitetyssä sijainnissa, jota voit muokata milloin tahansa.

Kuvitellaan esimerkiksi kysely, jossa on useita koodeja merkkijonona, ja haluat luoda funktion, joka purkaa näiden arvojen koodauksen, kuten seuraavassa mallitaulukossa:

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

Näyttökuva alkuperäisestä koodiluettelosta.

Aloitat ottamalla parametrin, jolla on esimerkkinä toimiva arvo. Tässä tapauksessa se on arvo PTY-CM1090-LAX.

Näyttökuva Parametrien hallinta -valintaikkunasta, johon on syötetty parametrikoodin malliarvot.

Tässä parametrissa luot uuden kyselyn, jossa käytät tarvitsemiasi muunnoksia. Tässä tapauksessa haluat jakaa koodin PTY-CM1090-LAX useisiin osiin:

  • Alkuperä = PTY
  • Kohde = LAX
  • Lentoyhtiö = CM
  • FlightID = 1090

Näyttökuva mallimuunnoskyselystä, jossa kukin osa on omassa sarakkeessaan.

Seuraava M-koodi esittelee näitä muunnoksia.

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"

Huomautus

Jos haluat lisätietoja Power Query M -kaavakielestä, siirry Power Query M -kaavakieleen.

Voit sitten muuntaa kyselyn funktioksi napsauttamalla kyselyä hiiren kakkospainikkeella ja valitsemalla Luo funktio. Lopuksi voit käynnistää mukautetun funktion mihin tahansa kyselyösi tai arvoosi seuraavassa kuvassa esitetyllä tavalla.

Näyttökuva koodiluettelosta, jossa on täytetty Kutsu mukautettuja funktioita -arvot.

Muutaman muunnoksen jälkeen näet, että saavutit haluamasi tuloksen ja käytit tällaisen muunnoksen logiikkaa mukautetusta funktiosta.

Näyttökuva, jossa näkyy lopullinen tulostekysely mukautetun funktion kutsumisen jälkeen.