Power Query -kaavojen luominen Excelissä

Power Query -editori tarjoaa Excelille tietokyselyn ja muotoilukokemuksen, jonka avulla voit muokata tietoja monista tietolähteistä. Jos haluat näyttää Power Query -editori-ikkunan, tuo tiedot Excel-laskentataulukon ulkoisista tietolähteistä, valitse solu tiedoista ja valitse sitten Kysely > Muokkaa. Seuraavassa on yhteenveto pääkomponenteista.

1. Power Query -editori valintanauha, jota käytät tietojen muokkaamiseen

2. Kyselyt-ruutu, jota käytetään tietolähteiden ja taulukoiden etsimiseen

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

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

5. Kyselyn asetukset -ruutu, jossa näkyvät kyselyn ominaisuudet ja jokainen vaihe

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

Joskus voi olla, kun haluat muokata tai luoda kaavan. Kaavat käyttävät Power Query kaavakieltä, jonka avulla voit luoda sekä yksinkertaisia että monimutkaisia lausekkeita. Lisätietoja syntaksista, argumenteista, huomautuksista, funktioista ja esimerkeistä on artikkelissa Power Query M-kaavakieli.

Käyttämällä esimerkkinä jalkapallomestaruuskilpailujen luetteloa voit käyttää Power Query, jotta voit ottaa verkkosivustolta löytämäsi raakatiedot ja muuttaa ne hyvin muotoilluksi taulukoksi. Katso, miten kyselyvaiheet ja vastaavat kaavat luodaan kuhunkin tehtävään , Kyselyasetukset-ruudunKäytössä olevat vaiheet -kohdassa ja kaavarivillä.

Toimenpide

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

2. Valitse Siirtymistoiminto-valintaikkunan vasemmasta reunasta Tulokset [Muokkaa] -taulukko ja valitse sitten alareunasta Muunna tiedot . Näkyviin tulee Power Query editori.

3. Jos haluat muuttaa kyselyn oletusnimeä, poista Kyselyasetukset-ruudun Ominaisuudet-kohdassa "Tulokset [Muokkaa]" ja kirjoita sitten "UEFA-mestarit".

4. Jos haluat poistaa ei-toivotut sarakkeet, valitse ensimmäinen, neljäs ja viides sarake ja valitse sitten Aloitus > Poista sarake > Poista muut sarakkeet.

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

6. Voit poistaa rivit, joissa on sana "Vuosi", valitsemalla suodatinnuolen Sarake1:ssä, poistamalla Valintaruudun valinta "Vuosi" -kohdan vierestä ja valitsemalla sitten OK.

7. Voit nimetä sarakeotsikot uudelleen kaksoisnapsauttamalla niitä ja muuttamalla sitten Sarake1-asetukseksi "Vuosi", "Sarake4" arvoksi "Voittaja" ja "Sarake5" arvoksi "Lopullinen pistemäärä".

8. Tallenna kysely valitsemalla Aloitus > Sulje & Lataa.

Tulos

Seuraavassa taulukossa on yhteenveto kustakin käytetystä vaiheesta ja sitä vastaavasta kaavasta.

Kyselyvaihe 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 (mitä 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 valitun sarakkeen arvojen puhdistamiseksi	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Suodatetut rivit Suodata sarakkeen arvot	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Uudelleennimettyjä sarakkeita Sarakeotsikoiden muuttaminen kuvaavaksi	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Tärkeää    Muokkaa lähde-, siirtymis- ja Muutettu tyyppi -vaiheita harkitusti,  koska Power Query luo ne tietolähteen määrittämistä ja määrittämistä varten.

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

Kaavan lisääminen kaavariville

1. Jos haluat avata kyselyn, etsi Power Query -editori aiemmin ladattu kysely, valitse tiedoista solu ja valitse sitten Kysely > Muokkaa. Lisätietoja on artikkelissa Kyselyn luominen, lataaminen tai muokkaaminen Excelissä.

2. Valitse Kyselyasetukset-ruudunKäytössä olevat vaiheet -kohdassa vaihe, jota haluat muokata.

3. Etsi ja muuta parametriarvoja kaavarivillä ja valitse sitten Enter -kuvake tai paina Enter-näppäintä. Voit esimerkiksi muuttaa tätä kaavaa niin, että sarake2:
   
   Ennen: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Jälkeen:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Valitse Enter -kuvake tai paina Enter-näppäintä, jotta näet uudet tulokset tietojen esikatselussa.

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

Kaavan luominen kaavariville

Jos haluat yksinkertaisen kaavaesimerkin, muunnetaan tekstiarvo erisnimen kirjainkokoon Käyttämällä Teksti.Erisnimi-funktiota.

1. Voit avata tyhjän kyselyn valitsemalla Excelissä Tiedot > Nouda tiedot > muista lähteistä > Tyhjä kysely. Lisätietoja on artikkelissa Kyselyn luominen, lataaminen tai muokkaaminen Excelissä.

2. Kirjoita kaavariville=Text.Proper("text value")ja valitse sitten Enter -kuvake tai paina Enter-näppäintä.
   
   Tulokset näkyvät tietojen esikatselussa .

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

Tulos:

 Kun luot kaavan, Power Query tarkistaa kaavan syntaksin. Kun lisäät kyselyn välivaiheen, järjestät sen uudelleen tai poistat sen, saatat kuitenkin mahdollisesti rikkoa kyselyn.  Tarkista tulokset aina tietojen esikatselussa.

Tärkeää    Muokkaa lähde-, siirtymis- ja Muutettu tyyppi -vaiheita harkitusti,  koska Power Query luo ne tietolähteen määrittämistä ja määrittämistä varten.

Kaavan muokkaaminen valintaikkunan avulla

Tässä menetelmässä käytetään valintaikkunoita, jotka vaihtelevat vaiheen mukaan. Sinun ei tarvitse tietää kaavan syntaksia.

1. Jos haluat avata kyselyn, etsi Power Query -editori aiemmin ladattu kysely, valitse tiedoista solu ja valitse sitten Kysely > Muokkaa. Lisätietoja on artikkelissa Kyselyn luominen, lataaminen tai muokkaaminen Excelissä.

2. Valitse Kyselyasetukset-ruudunKäytössä olevat vaiheet -kohdassa muokattavan vaiheen Muokkaa asetuksia -kuvake tai napsauta vaihetta hiiren kakkospainikkeella ja valitse sitten Muokkaa asetuksia.

3. Tee haluamasi muutokset valintaikkunassa ja valitse sitten OK.

Vaiheen lisääminen

Kun olet suorittanut kyselyn vaiheen, joka muotoilee tiedot uudelleen, kyselyvaihe lisätään nykyisen kyselyvaiheen alapuolelle. mutta kun lisäät kyselyn vaiheen vaiheiden keskelle, virhe voi ilmetä seuraavissa 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 kaikissa lisätyn vaiheen vaiheissa.

1. Valitse Kyselyasetukset-ruudunKäytössä olevat vaiheet -kohdassa vaihe, jonka haluat välittömästi edeltävän uutta vaihetta ja sitä vastaavaa kaavaa.

2. Valitse Lisää vaihe -kuvake kaavarivin vasemmalla puolella. Vaihtoehtoisesti voit napsauttaa vaihetta hiiren kakkospainikkeella ja valita sitten Lisää vaiheen 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 "Herra" henkilön sukupuolen mukaan. Kaava on:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Vaiheen järjestäminen uudelleen

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

Poista vaihe

• Valitse poista -kuvake vaiheen vasemmalla puolella tai napsauta vaihetta hiiren kakkospainikkeella ja valitse sitten Poista tai Poista loppuun asti. Poista -kuvake on käytettävissä myös kaavarivin vasemmalla puolella.

Muunnetaan tässä esimerkissä sarakkeen teksti erisnimen muotoon käyttämällä Laajennettu editori kaavojen yhdistelmää. 

Käytössäsi on esimerkiksi Tilaukset-niminen Excel-taulukko, jossa on ProductName-sarake, jonka haluat muuntaa erisnimen muotoon. 

Ennen

Jälkeen

Kun luot lisäkyselyn, luot joukon kyselykaavan vaiheita sallitun lausekkeen perusteella. 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 Kaavan luominen kaavariville -osassa.

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

Näet, että jokainen vaihe rakentuu edelliseen vaiheeseen viittaamalla vaiheeseen nimeltä. Muistutuksena on, että Power Query kaavakielen kirjainkoko on merkitsevä.

Vaihe 1: Avaa Laajennettu editori

1. Valitse Excelissä Tiedot > Nouda tiedot > Muut lähteet > tyhjä kysely. Lisätietoja on artikkelissa Kyselyn luominen, lataaminen tai muokkaaminen Excelissä.

2. Valitse Power Query -editori Aloitus -> Laajennettu editori, jossa on let-lausekkeen malli.

Vaihe 2: Tietolähteen määrittäminen

1. Luo let-lauseke Käyttämällä Excel.CurrentWorkbook-funktiota seuraavasti:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. Jos haluat ladata kyselyn laskentataulukkoon, valitse Valmis ja valitse sitten Aloitus > Sulje & Lataa > Sulje & Lataa.

Tulos:

Vaihe 3: Ensimmäisen rivin ylentäminen otsikoiksi

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

2. Valitse Power Query -editori Aloitus > Laajennettu editori, joka avautuu vaiheessa 2 luomasi lausekkeen kanssa: Tietolähteen määrittäminen.

3. Lisää let-lausekkeeseen #"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 Valmis ja valitse sitten Aloitus > Sulje & Lataa > Sulje & Lataa.

Tulos:

Vaihe 4: Sarakkeen kunkin arvon muuttaminen erisnimen muotoon

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

2. Valitse Power Query -editori Aloitus -> Laajennettu editori, joka avautuu vaiheessa 3 luodulla lausekkeella: Ensimmäisen rivin ylentäminen otsikoiksi.

3. Muunna let-lausekkeessa jokainen ProductName-sarakearvo erisnimen mukaiseksi tekstiksi Käyttämällä Table.TransformColumns-funktiota, viittaamalla edelliseen "Ensimmäinen rivi otsikkona" -kyselyn kaavavaiheeseen, lisäämällä tietolähteeseen #"Isoilla isolla alkukirjaimet Word" ja määrittämällä sitten tulokselle #"Isoilla isolla alkukirjaimet jokaiselle Word".
   
   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 Valmis ja valitse sitten Aloitus > Sulje & Lataa > Sulje & Lataa.

Tulos:

Voit hallita kaikkien työkirjojen Power Query -editori kaavarivin toimintaa.

Kaavarivin näyttäminen tai piilottaminen

1. Valitse Tiedoston > asetukset ja Asetukset > kyselyasetukset.

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

3. Valitse oikeanpuoleisessa ruudussa Asettelu-kohdassaNäytä kaavarivi tai poista sen valinta.

M Intellisensen ottaminen käyttöön tai poistaminen käytöstä

1. Valitse Tiedoston > asetukset ja Asetukset > kyselyasetukset .

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

3. Valitse oikeanpuoleisen ruudun Kaava-kohdassaOta M Intellisense käyttöön kaavarivillä, lisäeditorissa ja mukautetun sarakkeen valintaikkunassa tai poista sen valinta.

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