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

Muistiinpano

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.

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.

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

Muistiinpano

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.

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

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

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ä.

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.

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.

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.

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.

   

   Muistiinpano

   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.

   

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.

Muistiinpano

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

Varoitus

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.

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.

Muistiinpano

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.

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.

Muistiinpano

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.

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

Muistiinpano

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.

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.

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.

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.

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

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

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

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"

Muistiinpano

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.

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