Power Query valemite koostamine Excelis

Power Query Editor pakub andmepäringut ja Excel, mida saate kasutada paljudest andmeallikatest pärit andmete ümberkujutamiseks. Power Query redaktori akna kuvamiseks importigeandmed välistest andmeallikatest Excel, valige andmetes lahter ja seejärel valige Päring > Redigeeri. Järgnevalt on esitatud põhikomponentide kokkuvõte.

1. Power Query Editori lint, mida kasutate andmete kujundamiseks

2. Paan Päringud, mida kasutate andmeallikate ja tabelite leidmiseks

3. Kontekstimenüüd, mis on mugavad otseteed lindi käskudele

4. Andmeeelvaade, mis kuvab andmetele rakendatud toimingute tulemid

5. Päringupaan Sätted, kus on loetletud päringu atribuudid ja kõik etapid.

Stseenide taga põhinevad kõik päringu etapid valemiribal nähtaval valemil.

Võimalik, et soovite valemit muuta või luua. Valemid kasutavad Power Query valemikeelt, mille abil saate koostada nii lihtsaid kui ka keerukaid avaldisi. Lisateavet süntaksi, argumentide, kommentaaride, funktsioonide ja näidete kohta leiate teemast Power Query M valemikeel.

Näitena jalgpalli meistrivõistluste loendi abil saate Power Query abil võtta veebisaidilt leitud toorandmed ja muuta need hästi vormindatud tabeliks. Vaadake, kuidas luuakse päringu etapid ja vastavad valemid iga ülesande jaoks paanil Päring Sätted jaotises Rakendatud toimingud ja valemiribal.

Protseduur

1. Andmete importimiseks valige Data > From Web(Andmed veebist), sisestage väljale URL http://en.wikipedia.org/wiki/UEFA_European_Football_Championship "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" ja seejärel valige OK.

2. Valige dialoogiboksis Navigaator vasakul tabel Tulemid [Redigeeri] ja seejärel valige allservas Käsk Teisenda andmed. Kuvatakse Power Query redaktor.

3. Päringu vaikenime muutmiseks kustutage paanil Päring Sätted Atribuudid väärtus "Results [Edit]" (Tulemid [Redigeeri]) ja sisestage "UEFA champs".

4. Soovimatute veergude eemaldamiseks valige esimene, neljas ja viies veerg ning seejärel valige Avaleht > Eemalda veerg > Eemalda muud veerud.

5. Soovimatute väärtuste eemaldamiseks valige Veerg1, valige Avaleht > Asendaväärtused , sisestage väljale Otsitav väärtus "üksikasjad" ja seejärel valige OK.

6. Kui soovite eemaldada read, kus on sõna "Year" (Aasta), valige filtrinool veerus Veerg1,tühjendage ruut "Year" (Aasta) ja seejärel valige OK.

7. Veerupäiste ümbernimetamiseks topeltklõpsake neid ja seejärel muutke "Veerg1" väärtuseks "Year", "Column4" to "Winner" (Võitja) ja "Column5" ("Lõppskoor").

8. Päringu salvestamiseks valige Avaleht >Sule & Laadi.

Tulem

Järgmises tabelis on iga rakendatud etapi ja vastava valemi kokkuvõte.

Päringu etapp ja tööülesanne	Valem
Allikas Veebiandmeallikaga ühenduse loomine	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navigeerimine Valige ühendatav tabel	=Source{2}[Data]
Muudetud tüüp Andmetüüpide muutmine (mida Power Query automaatselt teeb)	= 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}})
Eemaldatud muud veerud Muude veergude eemaldamine ainult oluliste veergude kuvamiseks	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Asendatud väärtus Väärtuste asendamine valitud veerus väärtuste puhastamiseks	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filtreeritud read filtreerige väärtused veerus	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Ümbernimetatud veerud Muudetud veerupäised on tähendusrikkad	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Tähtis    Olge ettevaatlik juhiste Allikas,Navigeerimine jaMuudetud tüüp redigeerimisel, kuna Power Query on need andmeallika   määratlemiseks ja häälestamiseks loonud.

Valemiriba kuvamine või peitmine

Valemiriba kuvatakse vaikimisi, kuid kui see pole nähtav, saate seda uuesti kuvada.

• Valige >Küljendus > Valemiriba.

Valemiribal valemi kasutamine

1. Päringu avamiseks otsige Power Query redaktorist eelnevalt laaditud päring üles, valige andmetest lahter ja seejärel valige Päring > Redigeeri. Lisateavet leiate teemast Päringu loomine, laadimine või redigeerimineExcel .

2. Valige paanil Sätted jaotises Rakendatud toimingudtoiming, mida soovite redigeerida.

3. Otsige valemiribal üles ja muutke parameetriväärtusi ning seejärel valige ikoon Enter või vajutage sisestusklahvi (Enter). Näiteks muutke seda valemit nii, et veerg2 oleks alles:
   
   Enne: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Pärast:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Valige ikoon Enter või vajutage sisestusklahvi (Enter), et näha andmete eelvaates kuvatavaid uusi tulemeid.

5. Kui soovite tulemi kuvada Excel töölehel, valige Avaleht> Sule & Laadi.

Valemi loomine valemiribal

Lihtsa valemi näites teisendame tekstiväärtuse funktsiooni Text.Proper abil õigeks.

1. Tühja päringu avamiseks valige Excel > Too andmed > muudest allikatest > Tühi päring. Lisateavet leiate teemast Päringu loomine, laadimine või redigeerimineExcel .

2. Sisestage valemiribale=Text.Proper("text value")ja seejärel valige sisestusklahvi (Enter) või vajutage sisestusklahvi (Enter).
   
   Tulemid kuvatakse andme eelvaates.

3. Kui soovite tulemi kuvada Excel töölehel, valige Avaleht> Sule & Laadi.

Tulem:

 Valemi loomisel kontrollib Power Query valemisüntaksit. Kui aga lisate, järjestate või kustutate päringusse vaheastme, võib see päringut katkeda.  Kontrollige tulemid alati andmeeelvaadet.

Tähtis    Olge ettevaatlik juhiste Allikas,Navigeerimine jaMuudetud tüüp redigeerimisel, kuna Power Query on need andmeallika   määratlemiseks ja häälestamiseks loonud.

Valemi redigeerimine dialoogiboksi abil

Selle meetodi puhul kasutatakse dialoogibokse, mis sõltuvad etapist. Valemi süntaksit pole vaja teada.

1. Päringu avamiseks otsige Power Query redaktorist eelnevalt laaditud päring üles, valige andmetest lahter ja seejärel valige Päring > Redigeeri. Lisateavet leiate teemast Päringu loomine, laadimine või redigeerimineExcel .

2. Valige paanil Päring Sätted jaotises Rakendatud toimingud selle juhise ikoon Redigeeri Sätted , mida soovite redigeerida, või paremklõpsake juhist ja seejärel valige Redigeeri Sätted.

3. Tehke dialoogiboksis oma muudatused ja seejärel valige OK.

Juhise lisamine

Pärast seda, kui olete oma andmeid ümber kujundanud päringu etapi lõpule viinud, lisatakse praeguse päringusammi alla päringu etapp. kuid kui lisate päringu etapi juhiste keskele, võib järgnevates toimingutes ilmneda tõrge. Kui proovite lisada uut juhist, kuvab Power Query uue etapi lisamisel hoiatuse Lisa etapp ja uus toiming muudab välju (nt veerunimesid), mida kasutatakse mis tahes lisatud juhist järgivates juhistes.

1. Valige paanil Sätted jaotises Rakendatud toimingudjuhis, mille soovite kohe uuele etapile ja sellele vastavale valemile eelneda.

2. Valige valemiribast ikoon Lisa juhis. Teise võimalusena paremklõpsake soovitud juhist ja seejärel valige Lisa juhis pärast.Uus valem luuakse vormingus :
   
   = <nameOfTheStepToReference>(nt =Production.WorkOrder).

3. Tippige uus valem järgmise vormingu abil:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Oletagem näiteks, et teil on tabel veeruga Sugu ja soovite lisada veeru väärtusega "Ms". või "Mr.", olenevalt inimese soost. Valem oleks järgmine:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Etapi järjestuse ümberjärjesaamine

• Paremklõpsake paanil Päringud Sätted jaotises Rakendatud toimingudsoovitud juhist ja seejärel valige Nihuta üles võiNihuta alla.

Kustuta etapp

• Valige juhist või paremklõpsake juhist ja seejärel valige Kustuta või Kustuta kuni lõpuni. Ikoon Kustuta on saadaval ka valemiribast vasakul.

Selles näites teisendame veeru teksti õigeks juhtumiks, kasutades täpsemas redaktoris valemite kombinatsiooni. 

Näiteks on teil tabel Excel Tellimused, mille veeru ProductName soovite teisendada õigeks juhtumiks. 

Enne:

Pärast:

Täpsema päringu loomisel loote päringuvalemite etappide sarja, mis põhineb avaldisel Let.  Avaldise let abil saate määrata nimesid ja arvutada väärtusi, millele klausel seejärel viidatakse, mis määratleb etapi. Selles näites tagastatakse sama tulem, mis jaotises "Valemi loomine valemiribal".

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

Näete, et iga juhis põhineb varasemal etapil, viidates samm-sammult nime järgi. Meeldetuletusena on Power Query valemikeel stjuuutundlik.

1. etapp: täpsema redaktori avamine

1. Valige Excel Data> Get Data > Other Sources > Blank Query (Too andmed). Lisateavet leiate teemast Päringu loomine, laadimine või redigeerimineExcel .

2. Valige Power Query redaktoris Avaleht> Täiustatud redaktor, mis avaneb koos letiavaldise malliga.

2. etapp: andmeallika määratlemine

1. Looge avaldis let, kasutades Excel. Funktsioon CurrentWorkbook järgmiselt:
   
   let#x1
   
   in
       Source
   
   

2. Päringu töölehele laadimiseks valige Valmisja seejärel valige Avaleht > Sule & Laadi >Sule & Laadi.

Tulem:

3. etapp: esimese rea liigendamine päisteks

1. Päringu avamiseks valige töölehel andmete lahter ja seejärel valige Päring> Redigeeri. Lisateavet leiate teemast Päringu loomine, laadimine või redigeerimine Excel (Power Query).

2. Valige Power Query redaktoris Avaleht> Täiustatud redaktor, mis avaneb koos 2. faasis loodud lausega: määratlege andmeallikas.

3. Lisage avaldises Let #"First Row as Header" (#"Esimene rida päisena) ja Table.PromoteHeaders (Tabel.PromoteHeaders)
   
   järgmiselt:let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
   
       #"First Row as Header"

4. Päringu töölehele laadimiseks valige Valmisja seejärel valige Avaleht > Sule & Laadi >Sule & Laadi.

Tulem:

4. etapp: veeru iga väärtuse muutmine õigeks suurjuhtumiks

1. Päringu avamiseks valige töölehel andmete lahter ja seejärel valige Päring> Redigeeri. Lisateavet leiate teemast Päringu loomine, laadimine või redigeerimineExcel .

2. Valige Power Query redaktoris Avaleht> Täiustatud redaktor, mis avaneb koos 3. etapis loodud lausega: esimese rea esileviimine päisteks.

3. Teisendage avaldises Avaldis iga veeru ProductName väärtus õigeks tekstiks, kasutades funktsiooni Table.TransformColumns, viidates eelmisele päringuvalemi etapile "Esimene rida päisena", lisades andmeallikasse #"Suurtähestatud iga Sõna" ja määrates tulemile #"Suurtähestatud iga Sõna".
   
   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. Päringu töölehele laadimiseks valige Valmisja seejärel valige Avaleht > Sule & Laadi >Sule & Laadi.

Tulem:

Kõigi töövihikute valemiriba käitumist saate juhtida Power Query redaktoris.

Valemiriba kuvamine või peitmine

1. Valige Fail> Suvandid ja Sätted > Päringusuvandid.

2. Valige vasakpoolsel paanil jaotises GLOBALväärtus Power Query Editor.

3. Valige või tühjendage parempoolselpaanil jaotises Paigutus ruut Kuva valemiriba.

M Intellisense'i sisse- või väljalülitamine

1. Valige Fail> Suvandid ja Sätted > Päringusuvandid .

2. Valige vasakpoolsel paanil jaotises GLOBALväärtus Power Query Editor.

3. Valige või tühjendage parempoolsel paaniljaotises Valem nupp Luba M Intellisense valemiribal, täpsemas redaktoris ja kohandatud veerudialoogis.

Märkus    Selle sätte muutmine jõustub järgmine kord, kui avate Power Query redaktori akna.