Power Query -kaavojen luominen Excelissä

Olet luonut Power Query -kaavoja koko ajan Power Query -editorin avulla. Katsotaan, miten Power Query toimii, katsomalla sen alla. Opit päivittämään tai lisäämään kaavoja katsomalla, miten Power Query -editori on toiminnassa.  Voit myös pyöristää omia kaavojasi laajennettujen editorin avulla.           

Power Query -editori tarjoaa tietokyselyn ja muotoilun Excel, joiden avulla voit muotoilla tietoja uudelleen useista tietolähteistä. Tuo Power Query -editori-ikkuna näkyviin tuomalla tietoja ulkoisista tietolähteistä Excel laskentataulukossa, valitsemalla solu tiedoista ja valitsemalla sitten Kysely > Muokkaa. Seuraavassa on yhteenveto tärkeimmistä komponenteista.

Kyselyeditoriosat

  1. Tietojen muokkaamiseen käytettävä Power Query Editor -valintanauha

  2. Kyselyt-ruutu, jonka avulla tietolähteet ja taulukot paikannetaan

  3. Pikavalikot, jotka ovat käteviä valintanauhan komentojen pikanäppäimiä

  4. Tietojen esikatselu, joka näyttää tietoihin sovellettujen vaiheiden tulokset

  5. Kyselyn Asetukset, jossa on luettelo ominaisuuksista ja kyselyn jokaisesta vaiheesta

Kyselyn jokainen vaihe perustuu kaavaan, joka näkyy kaavarivillä.

Kyselyeditorin Kaava-esimerkki

Joskus haluat ehkä muokata tai luoda kaavan. Kaavoissa käytetään Power Queryn kaavakieltä, jonka avulla voit luoda sekä yksinkertaisia että monimutkaisia lausekkeita. Lisätietoja syntaksista, argumenteista, huomautuksia, funktioista ja esimerkeistä on artikkelissa Power Query M -kaavakieli.

Jos käytät esimerkkinä jalkapallon mestaruuskisojen luetteloa, voit Power Queryn avulla ottaa sivustossa löydettyjä raakatietoja ja muuttaa sen muotoiltuun taulukkoon. Katso, miten kyselyvaiheet ja niitä vastaavat kaavat luodaan kullekin tehtävälle Kyselyvaiheet Asetukset ruudun Kohdassa Käytössä olevat vaiheet ja Kaavarivillä.

Käyttämäsi selain ei tue videon toistoa.

Toimenpide

  1. Tuo tiedot valitsemalla Tiedot> Verkosta, kirjoittamalla "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" URL-ruutuun ja valitsemalla SITTEN OK.

  2. Valitse Siirtymistoiminto-valintaikkunassa Tulokset [Muokkaa] -taulukko vasemmalla ja valitse sitten alareunasta Muunna tiedot. Power Query -editori tulee näkyviin.

  3. Jos haluat muuttaa kyselyn oletusnimeä, Asetukset Kyselyasetukset-ruudussa Ominaisuudet ,poista "Tulokset [Muokkaa] ja kirjoita sitten "UEFA" (UEFA).

  4. Voit poistaa tarpeettomat sarakkeet valitsemalla ensimmäisen, neljännen ja viidennen sarakkeen ja valitsemalla sitten Aloitus> Poista sarake >muut sarakkeet.

  5. Jos haluat poistaa ei-toivotut arvot, valitse Sarake1, valitse Aloitus> Korvaa arvot, kirjoita "tiedot" Etsi-ruutuun ja valitse sitten OK.

  6. Jos haluat poistaa rivit, joissa on sana "Vuosi", valitse suodatinnuoli Sarake1:ssä,poista Vuosi-kohdan vieressä olevan valintaruudun valinta ja valitse sitten OK.

  7. Voit nimetä sarakeotsikot uudelleen kaksoisnapsauttamalla kutakin niistä ja vaihtamalla sitten Sarake1-, Sarake4-, Voittaja- ja Sarake5-otsikot Lopulliseksi pistemääräksi.

  8. Tallenna kysely valitsemalla Aloitus ja >Sulje & lataa.

Tulos

Vaiheittaiset ohjeet – muutama ensimmäinen rivi

Seuraavassa taulukossa on yhteenveto kustakin käytössä olevasta vaiheesta ja sitä vastaavasta kaavasta.

Kyselyn vaihe ja tehtävä

Kaava

Lähde

Yhdistä verkkotietolähteeseen

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Siirtyminen

Valitse taulukko, johon haluat muodostaa yhteyden

=Source{2}[Data]

Muutettu tyyppi

Tietotyyppien muuttaminen (power Query tekee automaattisesti)

= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})

Muut sarakkeet poistettu

Näytä vain halutut sarakkeet poistamalla muut sarakkeet

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

Korvattu arvo

Korvaa arvot, kun haluat puhdistaa valitun sarakkeen arvot

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

Suodatetut rivit

Suodata sarakkeen arvot

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

Uudelleennimetyt sarakkeet

Sarakeotsikot muutettu merkityksellisiksi

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Tärkeää    Ole varovainen, kun muokkaat Lähde-, Siirtyminen-ja Muutettu tyyppi -vaiheita, koska ne on luotu Power Queryn luomalla tietolähteen   määrittämiseen ja määrittämiseen.

Kaavarivin näyttäminen tai piilottaminen

Kaavarivi näkyy oletusarvoisesti, mutta jos se ei ole näkyvissä, voit näyttää sen uudelleen.

  • Valitse Näytä > asettelu > kaavarivillä.

Kaavanlaskeminen kaavarivillä

  1. Avaa kysely etsimällä se aiemmin Power Query -editorista, valitsemalla solu tiedoista ja valitsemalla sitten Kysely > Muokkaa. Lisätietoja on kohdassa Kyselyn luominen, lataaminen tai muokkaaminen Excel.

  2. Valitse Asetukset-ruudun Käytössä olevat vaiheet-kohdassa vaihe, jota haluat muokata.

  3. Etsi ja muuta parametriarvoja kaavarivillä ja valitse sitten Enter- Kirjoita-kuvake Power Queryn kaavapalkin vasemmalla puolella tai paina Enter-näppäintä. Voit esimerkiksi muuttaa tämän kaavan niin, että myös Sarake2:

    Ennen: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    jälkeen:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Valitse Enter- Kirjoita-kuvake Power Queryn kaavapalkin vasemmalla puolella tai paina Enter-näppäintä, jotta näet uudet tulokset tietojen esikatselussa.

  5. Jos haluat nähdä tuloksen Excel, valitse Aloitus >Sulje & Lataa.

Kaavan luominen kaavarivillä

Muunnetaan esimerkiksi tekstiarvo erisnyksi käyttämällä Text.Proper-funktiota.

  1. Jos haluat avata tyhjän kyselyn, Excel Valitse> Tiedot >muista lähteistä -> Tyhjä kysely. Lisätietoja on kohdassa Kyselyn luominen, lataaminen tai muokkaaminen Excel.

  2. Kirjoita kaavarioliin=Text.Proper("text value")ja valitse sitten Enter- Kirjoita-kuvake Power Queryn kaavapalkin vasemmalla puolella tai paina Enter-näppäintä.

    Tulokset näkyvät tietojen esikatselussa.

  3. Jos haluat nähdä tuloksen Excel, valitse Aloitus >Sulje & Lataa.

Tulos:

Text.Proper

 Kun luot kaavan, Power Query tarkistaa kaavan syntaksin. Kun lisäät, järjestät tai poistat välivaiheen kyselyssä, kysely saattaa kuitenkin katketa.  Tarkista tulokset aina tietojen esikatselussa.

Tärkeää    Ole varovainen, kun muokkaat Lähde-, Siirtyminen-ja Muutettu tyyppi -vaiheita, koska ne on luotu Power Queryn luomalla tietolähteen   määrittämiseen ja määrittämiseen.

Kaavan muokkaaminen valintaikkunan avulla

Tämä menetelmä käyttää valintaikkunoita, jotka vaihtelevat vaiheen mukaan. Sinun ei tarvitse tietää kaavan syntaksia.

  1. Avaa kysely etsimällä se aiemmin Power Query -editorista, valitsemalla solu tiedoista ja valitsemalla sitten Kysely > Muokkaa. Lisätietoja on kohdassa Kyselyn luominen, lataaminen tai muokkaaminen Excel.

  2. Valitse Kyselyruudun Asetukset -ruudun Käytössä olevat vaiheet -kohdassa Muokkaa Asetukset Asetukset-kuvake -kuvake sen vaiheen kuvakkeesta, jota haluat muokata, tai napsauta vaihetta hiiren kakkospainikkeella ja valitse sitten Muokkaa Asetukset.

  3. Tee haluamasi muutokset valintaikkunassa ja valitse sitten OK.

Lisää vaihe

Kun olet suorittanut kyselyn vaiheen, joka muotoilla tiedot uudelleen, kyselyvaihe lisätään nykyisen kyselyn vaiheen alapuolelle. mutta kun lisäät kyselyn vaiheen vaiheiden keskelle, virhe voi ilmetä myöhemmissä vaiheissa. Power Query näyttää Lisää vaihe -varoituksen, kun yrität lisätä uuden vaiheen ja uusi vaihe muuttaa kenttiä, kuten sarakkeiden nimiä, joita käytetään missä tahansa lisätyn vaiheen edetessä.

  1. Valitse Kyselytyökalut Asetukset ruudunKäytössä olevatvaiheet -kohdassa vaihe, jonka haluat välittömästi edeltää uutta vaihetta ja sitä vastaavaa kaavaa.

  2. Valitse Lisää Funktion kuvake -kuvake kaavarivillä vasemmalla. Vaihtoehtoisesti voit napsauttaa vaihetta hiiren kakkospainikkeella ja valita sitten Lisää vaihe jälkeen.Uusi kaava luodaan muodossa :

    = <nameOfTheStepToReference>, kuten =Production.WorkOrder.

  3. Kirjoita uusi kaava käyttämällä muotoa:

    =Class.Function(ReferenceStep[,otherparameters])

    Oletetaan esimerkiksi, että sinulla on taulukko, jossa on sukupuoli-sarake, ja haluat lisätä sarakkeen, jonka arvo on "Ms". tai "Mr." henkilön sukupuolen mukaan. Kaava olisi:

    =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Esimerkkikaava

Vaiheen järjestäminen uudelleen

  • Napsauta Kyselyt Asetukset-ruudunKäytössä olevat vaiheet-kohdassa vaihetta hiiren kakkospainikkeella ja valitse sitten Siirrä ylös tai Siirrä alas.

Poista vaihe

  • Valitse Poista Poista vaihe -kuvake vaiheen vasemmalla puolella tai napsauta vaihetta hiiren kakkospainikkeella ja valitse sitten Poista tai Poista loppuun asti. Poista Poista vaihe -kuvake on myös kaavarivillä vasemmalla puolella.

Tässä esimerkissä sarakkeen teksti muunnetaan erisnimen kirjoitusmuotoon käyttämällä erikoiseditorin kaavojen yhdistelmää. 

Sinulla on esimerkiksi Excel, jonka ProductName-sarake on muunnettava erisnimen tapaukseen. 

Ennen

Ennen

Jälkeen

Vaihe 4 – Tulos

Kun luot tarkennetyn kyselyn, luot let-lausekkeeseen perustuvan kyselykaavan vaiheiden sarjan.  Let-lausekkeen avulla voit määrittää nimiä ja laskea arvoja, joihin in-lause viittaa ja jotka määrittävät vaiheen. Tämä esimerkki palauttaa saman tuloksen kuin "Luo kaavarivillä" -osassa.

let  
    Source = Text.Proper("hello world")
in  
    Source  

Näet, että jokainen vaihe perustuu edelliseen vaiheeseen viittaamalla vaiheeseen nimen mukaan. Muistutuksena Power Queryn kaavakieli on kirjainkoon huomioon oltava.

Vaihe 1: Laajennettu editorin avaaminen

  1. Valitse Excel -kohdassa Tiedot> tiedot > muista lähteistä > tyhjä kysely. Lisätietoja on kohdassa Kyselyn luominen, lataaminen tai muokkaaminen Excel.

  2. Valitse Power Query Editorissa Aloitus > laajennettu editori, joka avautuu let-lausekkeen mallin kanssa.

Laajennettu editori 2

Vaihe 2: Tietolähteen määritteleminen

  1. Luo let-lauseke käyttämällä Excel. NykyinenTyökirja-funktio seuraavasti:

    let#x1

    in
        Source

    Vaihe 1 – Laajennettu editori

  2. Jos haluat ladata kyselyn laskentataulukkoon, valitse Valmisja valitse sitten Aloitus > Sulje & lataaminen > sulje & lataaminen.

Tulos:

Vaihe 1 - Tulos

Vaihe 3: Ylennä ensimmäinen rivi otsikoihin

  1. Avaa kysely valitsemalla laskentataulukosta solu tiedoista ja valitsemalla sitten Kysely> Muokkaa. Lisätietoja on kohdassa Kyselyn luominen, lataaminen tai muokkaaminen Excel (Power Query).

  2. Valitse Power Query Editorissa Aloitus > laajennettu editori, joka avautuu vaiheessa 2 luomasi lauseen kanssa: Määritä tietolähde.

  3. Lisää let-lausekkeessa #"Ensimmäinen rivi otsikkona" ja Table.PromoteHeaders-funktio seuraavasti:

    let
        
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   #"First Row as Header" = Table.PromoteHeaders(Source)#x3

        #"First Row as Header"

  4. Jos haluat ladata kyselyn laskentataulukkoon, valitse Valmisja valitse sitten Aloitus > Sulje & lataaminen > sulje & lataaminen.

Tulos:

Vaihe 3 – Tulos

Vaihe 4: Sarakkeen jokaisen arvon muuttaminen erisnyisen tekstin mukaan

  1. Avaa kysely valitsemalla laskentataulukosta solu tiedoista ja valitsemalla sitten Kysely> Muokkaa. Lisätietoja on kohdassa Kyselyn luominen, lataaminen tai muokkaaminen Excel.

  2. Valitse Power Query Editorissa Aloitus > laajennettu editori, joka avautuu vaiheessa 3 luomasi lauseen kanssa:Ylennä ensimmäinen rivi otsikoksi .

  3. Muunna let-lausekkeessa kukin ProductName-sarakkeen arvo erisnimen tekstiksi Table.TransformColumns-funktiolla, joka viittaa edelliseen "First Row as Header" -kyselykaavan vaiheeseen, #"Capitalized Each Word" tietolähteeseen ja määrittää #"Capitalized Each Word" in-tulokseen.

    let
        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
        #"First Row as Header" = Table.PromoteHeaders(Source),
        #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
    in
        #"Capitalized Each Word"

  4. Jos haluat ladata kyselyn laskentataulukkoon, valitse Valmisja valitse sitten Aloitus > Sulje & lataaminen > sulje & lataaminen.

Tulos:

Vaihe 4 – Tulos

Voit hallita kaikkien työkirjojen kaavariviä Power Query Editorissa.

Kaavapalkin näyttäminen tai piilottaminen

  1. Valitse Tiedosto> asetukset ja Asetukset > kyselyasetukset.

  2. Valitse vasemmanpuoleisen ruudun YLEINEN-kohdassaPower Query -editori.

  3. Valitse oikeanpuoleisen ruudun Asettelu-kohdassaNäytä kaavarivi tai poista sen valinta.

M IntelliSensen käyttöönottaminen tai käytöstä lykkäys

  1. Valitse Tiedosto> asetukset ja Asetukset > kyselyasetukset.

  2. Valitse vasemmanpuoleisen ruudun YLEINEN-kohdassaPower Query -editori.

  3. Valitse oikeanpuoleisen ruudun Kaava-kohdassaOta M Intellisensekäyttöön kaavarivillä, laajennettu editori ja mukautetun sarakkeen valintaikkuna tai poista sen valinta.

Huomautus:   Tämän asetuksen muuttaminen tulee voimaan, kun seuraavan kerran avaat Power Query Editor -ikkunan.

Katso myös

Microsoft Power Query for Excelin ohje

Mukautetun funktion luominen ja käynnistäminen

Käytössä olevat vaiheet -luettelon käyttäminen (docs.com)

Mukautettujen funktioiden käyttäminen (docs.com)

Power Query M -kaavat (docs.com)

Virheiden käsitteleminen (docs.com)

Tarvitsetko lisäohjeita?

Kehitä Office-taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

×