Power Query valemite koostamine Excelis

Power Query redaktor pakub Exceli jaoks andmepäringuid ja nende kujundamist, mille abil saate paljudest andmeallikatest pärinevaid andmeid ümber kujundada. Power Query redaktor akna kuvamiseks importige Andmed Exceli töölehel välistest andmeallikatest, valige andmetes lahter ja seejärel valige Päring > Redigeeri. Järgnevalt on toodud põhikomponentide kokkuvõte.

1. The Power Query redaktor ribbon that you use to shape your data

2. The Queries pane that you use to locate data sources and tables

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

4. The Data Preview that displays the results of the steps applied to the data

5. The Query Settings pane that lists properties and each step in the query

Taustal põhineb iga päringu etapp valemiribal kuvataval valemil.

Vahel võib teil tekkida soov valemit muuta või luua. Valemites kasutatakse Power Query valemikeelt, mille abil saate koostada nii lihtsaid kui ka keerukaid avaldisi. Süntaksi, argumentide, kommentaaride, funktsioonide ja näidete kohta leiate lisateavet teemast Power Query M-valemi keel.

Kui kasutate näiteks jalgpalli meistrivõistluste loendit, kasutage Power Query veebisaidilt leitud toorandmete kogumiseks ja muutke see hästi vormindatud tabeliks. Vaadake, kuidas luuakse päringuetapid ja vastavad valemid iga ülesande jaoks , paani Päringusätted jaotises Rakendatud etapid ja valemiribal.

Protseduur

1. Andmete importimiseks valige Andmed > Veebist, sisestage väljale URL "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" ja seejärel valige OK.

2. Valige dialoogiboksis Navigaator vasakul tabel Tulemid [Redigeerimine] ja seejärel valige allservas Transformatsiooniandmed . Kuvatakse Power Query redaktor.

3. Päringu vaikenime muutmiseks kustutage paani Päringusätted jaotises Atribuudid tekst "Results [Edit]" (Tulemid [Redigeerimine]) ja sisestage "UEFA šamplid".

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 > Asenda väärtused, sisestage väljale Otsitav väärtused "üksikasjad" ja seejärel valige OK.

6. Kui soovite eemaldada read, milles on sõna "Aasta", valige filtrinool veerus Veerg1, tühjendage märkeruut "Aasta" ja seejärel valige OK.

7. Veerupäiste ümbernimetamiseks topeltklõpsake neid ja seejärel muutke veeru "Veerg1" nimeks "Year" (Veerg1), "Column4" (Veerg4) nimeks "Winner" (Võitja) ja seejärel "Column5" (Veerg5) nimeks "Final Score" (Lõplik tulemus).

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

Tulem

Järgmises tabelis on kokkuvõte igast rakendatud etapist ja vastavast valemist.

Päringu etapp ja ü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 muudetakse)	= 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 veeru 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 Tähendusrikkad veerupäised on muudetud	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

NB!    Olge lähte-, navigeerimis - ja muudetud tüübi etappide redigeerimisel ettevaatlik, kuna Power Query on need loonud andmeallika määratlemiseks ja häälestamiseks.

Valemiriba kuvamine või peitmine

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

• Valige > Vaade> valemiriba.

Edit a formula in the formula bar

1. Päringu avamiseks otsige üles Power Query redaktor varem laaditud päring, valige andmetes lahter ja seejärel valige Päring > Redigeeri. Lisateavet leiate teemast Päringu loomine, laadimine või redigeerimine Excelis.

2. Valige paani Päringu sätted jaotises Rakendatud etapid etapp, mida soovite redigeerida.

3. Otsige valemiribal üles parameetriväärtused ja muutke neid ning seejärel valige ikoon Enter või vajutage sisestusklahvi (Enter). Näiteks muutke seda valemit nii, et see säilitaks ka veeru 2:
   
   Before: = 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. Tulemi kuvamiseks Exceli töölehel valige Avaleht > Sule & Laadi.

Valemi loomine valemiribal

Lihtsa valemi näites teisendame tekstiväärtuse algsalvtähtedeks, kasutades funktsiooni Text.Proper.

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

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

3. Tulemi kuvamiseks Exceli töölehel valige Avaleht > Sule & Laadi.

Tulem:

 Valemi loomisel Power Query valideerib valemi süntaksi. Päringusse vaheetapi lisamisel, ümberjärjestamisel või kustutamisel võite aga päringu potentsiaalselt katkestada.  Kontrollige tulemeid alati andmeeelvaates.

NB!    Olge lähte-, navigeerimis - ja muudetud tüübi etappide redigeerimisel ettevaatlik, kuna Power Query on need loonud andmeallika määratlemiseks ja häälestamiseks.

Valemi redigeerimine dialoogiboksi abil

Selle meetodi puhul kasutatakse dialoogibokse, mis olenevad juhisest. Te ei pea teadma valemi süntaksit.

1. Päringu avamiseks otsige üles Power Query redaktor varem laaditud päring, valige andmetes lahter ja seejärel valige Päring > Redigeeri. Lisateavet leiate teemast Päringu loomine, laadimine või redigeerimine Excelis.

2. Valige paani Päringu sätted jaotises Rakendatud etapid redigeeritava toimingu ikoon Redigeeri sätteid või paremklõpsake seda ja seejärel valige Redigeeri sätteid.

3. Tehke dialoogiboksis soovitud muudatused ja valige OK.

Lisa etapp

Kui olete andmete ümberkujundit kujundava päringuetapi lõpule viinud, lisatakse päringu etapp praeguse päringuetapi alla. kuid kui lisate päringuetapi juhiste keskele, võib järgnevates toimingutes ilmneda tõrge. Power Query kuvab hoiatuse Lisa etapp, kui proovite lisada uut juhist ja uus juhis muudab lisatud juhise järgivates juhistes kasutatavaid välju (nt veerunimesid).

1. Valige paani Päringu sätted jaotises Rakendatud etapid etapp, mille soovite vahetult uuele etapile ja sellele vastavale valemile vahetult eelneda.

2. Valige valemiribast vasakul ikoon Lisa etapp . Teine võimalus on paremklõpsata etappi ja seejärel valida Lisa juhis pärast. Luuakse uus valem vormingus :
   
   = <nameOfTheStepToReference>, nt =Production.WorkOrder.

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

Etapi järjestuse muutmine

• Paremklõpsake päringute sätete paani jaotises Rakendatud etapid etappi ja seejärel valige Nihuta üles või Nihuta alla.

Kustuta etapp

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

Selles näites teisendame veeru teksti algsalveks, kasutades Täpsem redaktor valemite kombinatsiooni. 

Näiteks on teil Exceli tabel nimega Tellimused ja veerg TooteNimi, mille soovite teisendada algsalveks. 

Enne:

Pärast:

Täpsema päringu loomisel loote let-avaldise põhjal päringuvalemi etappide sarja . Kasutage let-avaldist nimede määramiseks ja väärtuste arvutamiseks, millele in-klausel viitab, mis määratleb etapi. Selles näites tagastatakse sama tulem, mis on jaotises "Valemi loomine valemiribal".

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

Näete, et iga etapp tugineb eelmisele etapile, viidates etapi nime järgi. Power Query valemikeel on tõstutundlik.

Etapp 1: Täpsem redaktor avamine

1. Valige Excelis Andmed > Too andmed > Muud allikad > Tühi päring. Lisateavet leiate teemast Päringu loomine, laadimine või redigeerimine Excelis.

2. Valige Power Query redaktor Avaleht > Täpsem redaktor, mis avatakse let-avaldise malliga.

Faas 2: andmeallika määratlemine

1. Let-avaldise loomine funktsiooni Excel.CurrentWorkbook abil järgmiselt:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]#x2
   
       
   
   #x4Source

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

Tulem:

Etapp 3: esimese rea määramine päisteks

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

2. Valige Power Query redaktor Avaleht > Täpsem redaktor, mis avatakse 2. etapis loodud lausega: andmeallika määratlemine.

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

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

Tulem:

Faas 4: veeru iga väärtuse muutmine algsalveks

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

2. Valige Power Query redaktor Avaleht > Täpsem redaktor, mis avatakse 3. etapis loodud lausega: esimese rea määramine päisteks.

3. Teisendage let-avaldises iga ProductName-veeru väärtus funktsiooni Table.TransformColumns abil õigeks tekstiks, viidates eelmisele päringuvalemi etapile "First Row as Header", lisades andmeallikasse väärtuse #"Capitalized Each Word" ja määrates tulemile #"Capitalized Each 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. Päringu töölehele laadimiseks valige Valmis ja seejärel Avaleht > Sule & Laadi > Sule & Laadi.

Tulem:

Saate reguleerida valemiriba käitumist kõigi töövihikute Power Query redaktor.

Valemiriba kuvamine või peitmine

1. Valige Menüü Fail > Suvandid ja sätted > Päringusuvandid.

2. Valige vasakpoolsel paanil jaotises GLOBAALNEväärtus Power Query redaktor.

3. Märkige või tühjendage parempoolsel paanil 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 GLOBAALNEväärtus Power Query redaktor.

3. Märkige või tühjendage parempoolsel paanil jaotises Valemvalemiribal, täpsemas redaktoris ja kohandatud veerudialoogis Luba M Intellisense.

Märkus    Selle sätte muutmine jõustub järgmisel Power Query redaktor akna avamisel.