„Power Query“ formulių kūrimas naudojant „Excel“

"Power Query" rengyklė pateikia "Excel" duomenų užklausą ir formuoja funkcijas, kurias galite naudoti norėdami pertvarkyti duomenis iš daugelio duomenų šaltinių. Norėdami rodyti "Power Query" rengyklė langą, importuokite duomenis iš išorinių duomenų šaltinių"Excel" darbalapyje, pažymėkite duomenų langelį, tada pasirinkite Užklausos > Redaguoti. Toliau pateikiama pagrindinių komponentų suvestinė.

1. The "Power Query" rengyklė ribbon that you use to shape your data

2. Užklausų sritis, kurią naudojate duomenų šaltiniams ir lentelėms rasti

3. Kontekstiniai meniu, kurie yra patogūs juostelės komandų spartieji klavišai

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

Užkulisiuose kiekvienas užklausos veiksmas pagrįstas formule, matoma formulės juostoje.

Gali būti, kad norėsite modifikuoti arba kurti formulę. Formulės naudoja "Power Query" formulės kalbą, kurią galite naudoti kurdami tiek paprastus, tiek sudėtingus reiškinius. Daugiau informacijos apie sintaksę, argumentus, pastabas, funkcijas ir pavyzdžius žr. "Power Query" M formulės kalbą.

Pavyzdžiui, naudodami futbolo čempionatų sąrašą, naudokite "Power Query", kad galėtumėte rinkti svetainėje rastus neapdorotus duomenis ir paversti juos tinkamai suformatuota lentele. Peržiūrėkite, kaip kuriami kiekvienos užduoties užklausų veiksmai ir atitinkamos formulės srities Užklausos parametrai dalyje Taikomi veiksmai ir formulės juostoje.

Procedūra

1. Norėdami importuoti duomenis, pasirinkite Duomenys > Iš žiniatinklio, URL lauke įveskite "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship", tada pasirinkite Gerai.

2. Naršyklės dialogo lango kairėje pasirinkite lentelę Rezultatai [Redaguoti], tada apačioje pasirinkite Transformuoti duomenis . Rodoma "Power Query" rengyklė.

3. Norėdami pakeisti numatytąjį užklausos pavadinimą, srities Užklausos parametrai dalyje Ypatybės panaikinkite "Rezultatai [Redaguoti]" ir įveskite "UEFA champs".

4. Norėdami pašalinti nepageidaujamus stulpelius, pažymėkite pirmąjį, ketvirtą ir penktą stulpelius, tada pasirinkite Pagrindinis > Šalinti stulpelį > Šalinti kitus stulpelius.

5. Norėdami pašalinti nepageidaujamas reikšmes, pasirinkite 1stulpelis, pasirinkite Pagrindinis > Pakeisti reikšmes, įveskite "details" lauke Rastinos reikšmės, tada pasirinkite Gerai.

6. Norėdami pašalinti eilutes, kuriose yra žodis "Year", pasirinkite filtro rodyklę 1 stulpelyje, išvalykite žymės langelį šalia "Year", tada pasirinkite Gerai.

7. Norėdami pervardyti stulpelių antraštes, dukart spustelėkite kiekvieną iš jų, tada pakeiskite "Stulpelis1" į "Metai", "Stulpelis4" į "Laimėtojas" ir "Stulpelis5" į "Galutinis balas".

8. Norėdami įrašyti užklausą, pasirinkite Pagrindinis > Uždaryti & Įkelti.

Rezultatas

Toliau pateiktoje lentelėje pateikiama kiekvieno pritaikyto veiksmo ir atitinkamos formulės suvestinė.

Užklausos veiksmas ir užduotis	Formulė
Šaltinis Prisijungimas prie žiniatinklio duomenų šaltinio	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Naršymas Pasirinkite lentelę, prie kurios norite prisijungti	=Source{2}[Data]
Pakeistas tipas Keisti duomenų tipus (kurie "Power Query" automatiškai)	= 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}})
Pašalinti kiti stulpeliai Kitų stulpelių pašalinimas, kad būtų rodomi tik dominantys stulpeliai	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Pakeista reikšmė Reikšmių keitimas pasirinkto stulpelio reikšmėms išvalyti	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filtruotos eilutės Reikšmių filtravimas stulpelyje	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Pervardyti stulpeliai Pakeistos stulpelių antraštės turi būti prasmingos	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Svarbu    Būkite atsargūs redaguodami šaltinio, naršymo  ir pakeistą tipą veiksmus, nes juos sukūrė "Power Query", kad apibrėžtų ir nustatytų duomenų šaltinį.

Formulės juostos rodymas arba slėpimas

Pagal numatytuosius parametrus rodoma formulės juosta, bet jei ji nematoma, galite ją vėl rodyti.

• Pasirinkite Peržiūrėti > maketą > formulės juostą.

Formulės edit formulė formulės juostoje

1. Norėdami atidaryti užklausą, raskite anksčiau iš "Power Query" rengyklė įkeltą užklausą, pažymėkite duomenų langelį, tada pasirinkite Užklausos > Redaguoti. Daugiau informacijos žr. Užklausos kūrimas, įkėlimas arba redagavimas programoje "Excel".

2. Srities Užklausos parametrai dalyje Pritaikyti veiksmai pasirinkite norimą redaguoti veiksmą.

3. Formulės juostoje raskite ir pakeiskite parametrų reikšmes, tada pasirinkite piktogramą Enter arba paspauskite Enter. Pavyzdžiui, pakeiskite šią formulę, kad 2stulpelis:
   
   Prieš: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Po:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Pasirinkite piktogramą Įvesti arba paspauskite Enter, kad pamatytumėte naujus rezultatus, rodomus duomenų peržiūroje.

5. Norėdami pamatyti rezultatą "Excel" darbalapyje, pasirinkite Pagrindinis > Uždaryti & Įkelti.

Formulės kūrimas formulės juostoje

Paprasto formulės pavyzdžio atveju galime konvertuoti teksto reikšmę į didžiąsias ir mažąsias raides naudodami funkciją Text.Proper.

1. Norėdami atidaryti tuščią užklausą, programoje "Excel" pasirinkite Duomenys > Gauti duomenis > iš kitų šaltinių > Tuščia užklausa. Daugiau informacijos žr. Užklausos kūrimas, įkėlimas arba redagavimas programoje "Excel".

2. Formulės juostoje įveskite=Text.Proper("text value"), tada pasirinkite piktogramą "Enter" arba paspauskite klavišą "Enter".
   
   Rezultatai rodomi duomenų peržiūroje .

3. Norėdami pamatyti rezultatą "Excel" darbalapyje, pasirinkite Pagrindinis > Uždaryti & Įkelti.

Rezultatas:

 Kai kuriate formulę, "Power Query" patikrina formulės sintaksę. Tačiau įterpus, pertvarkant ar naikinant tarpinį veiksmą užklausoje galimai nutraukti užklausą.  Visada patikrinkite rezultatus duomenų peržiūroje.

Svarbu    Būkite atsargūs redaguodami šaltinio, naršymo  ir pakeistą tipą veiksmus, nes juos sukūrė "Power Query", kad apibrėžtų ir nustatytų duomenų šaltinį.

Formulės redagavimas naudojant dialogo langą

Šis metodas naudoja dialogo langus, kurie skiriasi priklausomai nuo veiksmo. Jums nereikia žinoti formulės sintaksės.

1. Norėdami atidaryti užklausą, raskite anksčiau iš "Power Query" rengyklė įkeltą užklausą, pažymėkite duomenų langelį, tada pasirinkite Užklausos > Redaguoti. Daugiau informacijos žr. Užklausos kūrimas, įkėlimas arba redagavimas programoje "Excel".

2. Srities Užklausos parametrai dalyje Pritaikyti veiksmai pasirinkite norimo redaguoti veiksmo piktogramą Redaguoti parametrus arba dešiniuoju pelės mygtuku spustelėkite veiksmą, tada pasirinkite Redaguoti parametrus.

3. Dialogo lange atlikite keitimus ir pasirinkite Gerai.

Veiksmo įterpimas

Atlikus užklausos veiksmą, kuris reshapes jūsų duomenis, po dabartiniu užklausos veiksmu įtraukiamas užklausos veiksmas. bet kai įterpiate užklausos veiksmą veiksmų viduryje, klaida gali įvykti atliekant tolesnius veiksmus. "Power Query" rodomas įspėjimas Įterpti veiksmą, kai bandote įterpti naują veiksmą, o naujas veiksmas keičia laukus, pvz., stulpelių pavadinimus, kurie naudojami atliekant bet kurį iš veiksmų, atlikite įterptą veiksmą.

1. Srities Užklausos parametrai dalyje Taikomi veiksmai pasirinkite veiksmą, kurį norite atlikti prieš naują veiksmą, ir atitinkamą formulę.

2. Pasirinkite piktogramą Įtraukti veiksmą į kairę nuo formulės juostos. Arba dešiniuoju pelės mygtuku spustelėkite veiksmą ir pasirinkite Įterpti veiksmą po. Nauja formulė sukuriama formatu :
   
   = <nameOfTheStepToReference>, pvz., =Production.WorkOrder.

3. Įveskite naują formulę naudodami formatą:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Pavyzdžiui, tarkime, kad turite lentelę su stulpeliu Gender ir norite pridėti stulpelį su reikšme "Ms". arba "Mr.", priklausomai nuo asmens lyties. Formulė būtų:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Veiksmo pertvarkymas

• Srities Užklausų parametrai dalyje Taikomi veiksmai dešiniuoju pelės mygtuku spustelėkite veiksmą, tada pasirinkite Perkelti aukštyn arba Perkelti žemyn.

Naikinti veiksmą

• Pasirinkite kairėje veiksmo pusėje esančią piktogramą Naikinti arba dešiniuoju pelės mygtuku spustelėkite veiksmą, tada pasirinkite Naikinti arba Naikinti iki pabaigos. Piktograma Naikinti taip pat pasiekiama formulės juostos kairėje.

Šiame pavyzdyje konvertuokite tekstą stulpelyje į didžiąsias ir mažąsias raides naudodami formulių derinį patobulinta rengyklė. 

Pavyzdžiui, turite "Excel" lentelę, vadinamą Užsakymai, su stulpeliu ProductName, kurį norite konvertuoti į didžiąsias ir mažąsias raides. 

Prieš:

Po:

Kurdami išplėstinę užklausą sukuriate užklausų formulės veiksmų seką pagal reiškinio leidžiamą reiškinį. Naudokite let išraišką, kad priskirtumėte pavadinimus ir apskaičiuotumėte reikšmes, kurios vėliau nurodomos sąlygoje in , kuri apibrėžia veiksmą. Šiame pavyzdyje pateikiamas tas pats rezultatas kaip ir skyriuje "Formulės kūrimas formulės juostoje".

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

Matysite, kad kiekvienas veiksmas remiasi ankstesniu veiksmu, nurodydami po vieną veiksmą. Primename, kad "Power Query" Formulės kalba skiria didžiąsias ir mažąsias raides.

1 etapas: atidarykite patobulinta rengyklė

1. Programoje "Excel" pasirinkite Duomenys > Gauti duomenis > Kiti šaltiniai > tuščia užklausa. Daugiau informacijos žr. Užklausos kūrimas, įkėlimas arba redagavimas programoje "Excel".

2. "Power Query" rengyklė pasirinkite Pagrindinis > patobulinta rengyklė, kuris atidaromas su reiškinio let šablonu.

2 etapas: Duomenų šaltinio apibrėžimas

1. Sukurkite reiškinį naudodami funkciją Excel.CurrentWorkbook taip:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. Norėdami įkelti užklausą į darbalapį, pasirinkite Atlikta, tada pasirinkite Pagrindinis > Uždaryti & Įkelti > Uždaryti & Įkelti.

Rezultatas:

3 etapas: pirmos eilutės paaukštinimai į antraštes

1. Norėdami atidaryti užklausą, darbalapyje pasirinkite duomenų langelį, tada pasirinkite Užklausa > Redaguoti. Daugiau informacijos žr. Užklausos kūrimas, įkėlimas arba redagavimas programoje "Excel" ("Power Query").

2. "Power Query" rengyklė pasirinkite Pagrindinis > patobulinta rengyklė, kuris atidaromas su sakiniu, kurį sukūrėte 2 etapu: duomenų šaltinio apibrėžimas.

3. Let reiškinyje įtraukite #"First Row as Header" ir Table.PromoteHeaders funkciją taip:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
       #"First Row as Header"

4. Norėdami įkelti užklausą į darbalapį, pasirinkite Atlikta, tada pasirinkite Pagrindinis > Uždaryti & Įkelti > Uždaryti & Įkelti.

Rezultatas:

4 etapas: pakeiskite kiekvieną stulpelio reikšmę į didžiąsias ir mažąsias raides mažosiomis ir atvirkščiai

1. Norėdami atidaryti užklausą, darbalapyje pasirinkite duomenų langelį, tada pasirinkite Užklausa > Redaguoti. Daugiau informacijos žr. Užklausos kūrimas, įkėlimas arba redagavimas programoje "Excel".

2. "Power Query" rengyklė pasirinkite Pagrindinis > patobulinta rengyklė, kuris atidaromas su patvirtinimu, kurį sukūrėte 3 etapu: pirmąją eilutę paaukštinkite iki antraščių.

3. Reiškinio leisti konvertuoti kiekvieną ProductName stulpelio reikšmę į didžiąsias ir mažąsias raides naudodami funkciją Table.TransformColumns, nurodydami ankstesnį užklausos formulės veiksmą "Pirmoji eilutė kaip antraštė", įtraukdami #"Capitalized Each Word" į duomenų šaltinį, tada rezultatui priskirdami #"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. Norėdami įkelti užklausą į darbalapį, pasirinkite Atlikta, tada pasirinkite Pagrindinis > Uždaryti & Įkelti > Uždaryti & Įkelti.

Rezultatas:

Galite valdyti visų darbaknygių formulės juostos veikimą "Power Query" rengyklė.

Formulės juostos rodymas arba slėpimas

1. Pasirinkite Failo > parinktys ir parametrai > Užklausos parinktys.

2. Kairiojoje srityje, dalyje VISUOTINIS, pasirinkite "Power Query" rengyklė.

3. Dešiniojoje srityje, dalyje Maketas, pažymėkite arba išvalykite žymės langelį Rodyti formulės juostą.

"M IntelliSense" įjungimas arba išjungimas

1. Pasirinkite Failo > parinktys ir Parametrai > Užklausos parinktys .

2. Kairiojoje srityje, dalyje VISUOTINIS, pasirinkite "Power Query" rengyklė.

3. Dešiniosios srities dalyje Formulė pažymėkite arba išvalykite formulių juostoje, išplėstinės rengyklės ir pasirinktinių stulpelių dialogo lange esančią parinktį Įgalinti "M IntelliSense".

Pastaba    Šio parametro keitimas įsigalios, kai kitą kartą atidarysite "Power Query" rengyklė langą.