Applies ToMicrosoft 365 rakendus Excel Excel 2024 Excel 2021 Excel 2019 Excel 2016 Excel 2013

Ainult Power Query redaktor abil olete loonud Power Query valemeid. Vaatame, kuidas Power Query töötab, vaadates kaane alla. Siit saate teada, kuidas valemeid värskendada või lisada, kui vaatate lihtsalt Power Query redaktor toimimas.  Saate isegi oma valemeid Täpsem redaktor kasutada.           

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.

Päringuredaktori osad

  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.

Päringuredaktori valeminäidis

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.

Teie brauser ei toeta videot. Installige Microsoft Silverlight, Adobe Flash Player või Internet Explorer 9.

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

Tutvustuse tulemused – esimesed paar rida

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 Power Query valemiribast vasakul asuv ikoon Sisesta 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 Power Query valemiribast vasakul asuv ikoon Sisesta 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 Power Query valemiribast vasakul asuv ikoon Sisesta või vajutage sisestusklahvi (Enter).Tulemid kuvatakse andmeeelvaates.

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

Tulem:

Text.Proper

 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 Sätted 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 Funktsiooni ikoon . 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.")

Valemi näide

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 Kustuta etapp või paremklõpsake etappi ja seejärel valige Kustuta või Kustuta kuni lõpuni. Ikoon Kustuta Kustuta etapp 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:

Enne

Pärast:

4. juhis – tulemus

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.

Täpsem redaktor 2

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:

1. juhis – tulemus

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:

3. juhis – tulemus

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:

4. juhis – tulemus

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.

Lisateave

Power Query for Exceli spikker

Kohandatud funktsiooni loomine ja kutsumine

Loendi Rakendatud etapid (docs.com) kasutamine

Kohandatud funktsioonide kasutamine (docs.com)

M-valemite Power Query (docs.com)

Tõrgetega tegelemine (docs.com)

Kas vajate veel abi?

Kas soovite rohkem valikuvariante?

Siin saate tutvuda tellimusega kaasnevate eelistega, sirvida koolituskursusi, õppida seadet kaitsma ja teha veel palju muud.

Kogukonnad aitavad teil küsimusi esitada ja neile vastuseid saada, anda tagasisidet ja saada nõu rikkalike teadmistega asjatundjatelt.