Power Query formulu izveide programmā Excel

Izmantojot Power Query redaktoru, jūs jau esat veidojis Power Query formulas. Uzzināsim, kā Power Query darbojas, aplūkojot šos rakstus. Uzziniet, kā atjaunināt vai pievienot formulas, vienkārši skatoties Power Query redaktoru darbībā.  Varat pat izveidot savas formulas, izmantojot detalizēto redaktoru.           

Power Query redaktors nodrošina datu vaicājumu un formu Excel ko var izmantot, lai pārveidotu datus no daudziem datu avotiem. Lai parādītu Power Query redaktora logu,importējiet datus no ārējiem datu avotiem Excel darblapā, atlasiet kādu datu šūnu un pēc tam atlasiet Vaicājuma >Rediģēt. Tālāk ir parādīts galveno komponentu kopsavilkums.

Vaicājumu redaktora daļas

  1. Power Query redaktora lente, ko izmantojat datu formas laukā

  2. Vaicājumu rūts, kuru izmantojat, lai atrastu datu avotus un tabulas

  3. Kontekstizvēlnes, kas ir ērtas saīsnes uz lentes komandām

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

  5. The Query Iestatījumi pane that lists properties and each step in the query

Aizkulisēs katra vaicājuma darbība ir pamatota uz formulu joslā redzamu formulu.

Vaicājumu redaktora formulas piemērs

Var būt reizes, kad vēlaties modificēt vai izveidot formulu. Formulās tiek izmantota Power Query formulu valoda, ko var izmantot, lai veidotu gan vienkāršas, gan sarežģītas izteiksmes. Papildinformāciju par sintaksi, argumentiem, piezīmēm, funkcijām un piemēriem skatiet rakstā Power Query M formuluvaloda.

Piemēram, izmantojot futbola čempionu sarakstu, izmantojiet Power Query, lai neapstrādātus datus, ko esat atradis tīmekļa vietnē, un pārvērtiet to par labi formatētu tabulu. Skatieties, kā vaicājuma soļi un atbilstošās formulas tiek izveidotas katram uzdevumam vaicājumu Iestatījumi rūtī Lietotās darbības un formulu joslā.

Jūsu pārlūkprogramma neatbalsta video.

Procedūra

  1. Lai importētu datus, URL lodziņā atlasiet datu> No tīmekļa ,URL lodziņā ievadiet "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" un pēc tam atlasiet Labi.

  2. Navigācijas dialoglodziņā atlasiet tabulu Rezultāti [Rediģēt] kreisajā pusē un pēc tam apakšā atlasiet Transformēt datus. Tiek parādīts Power Query redaktors.

  3. Lai mainītu noklusējuma vaicājuma nosaukumu, rūts Vaicājumu Iestatījumi sadaļā Rekvizīti izdzēsiet "Rezultāti [Rediģēt]" un pēc tam ievadiet "UEFA šauras vietas".

  4. Lai noņemtu nevajadzīgās kolonnas, atlasiet pirmo, ceturto un piekto kolonnu un pēc tam atlasiet Sākums > Noņemt kolonnu > Noņemt citas kolonnas.

  5. Lai noņemtu nevajadzīgās vērtības, atlasiet Kolonna1, > Aizstāt vērtības ,ievadiet "detalizēta informācija" lodziņā Atrodietamās vērtības un pēc tam atlasiet Labi.

  6. Lai noņemtu rindas, kurās ir vārds "Gads", atlasiet filtra bultiņu kolonnā Kolonna1, notīriet izvēles rūtiņu blakus "Gads" un pēc tam atlasiet Labi.

  7. Lai pārdēvētu kolonnu galvenes, veiciet dubultklikšķi uz tām un pēc tam mainiet "Kolonna1" uz "Gads", "Kolonna4" uz "Uzvarētājs" un "Kolonna5" uz "Gala rezultāts".

  8. Lai saglabātu vaicājumu, atlasiet Sākums un> aizvērt & Ielādēt.

Rezultāts

Atsāciena rezultāti — dažas pirmās rindas

Tālāk esošajā tabulā ir kopsavilkums par katru lietoto darbību un atbilstošā formula.

Vaicājuma solis un uzdevums

Formula

Avots

Savienošana ar tīmekļa datu avotu

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Navigācija

Tabulas, ar kuru vēlaties izveidot savienojumu, atlase

=Source{2}[Data]

Mainīts tips

Datu tipu maiņa (kura Power Query nedarbojas automātiski)

= 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}})

Noņemtas citas kolonnas

Noņemt citas kolonnas, lai rādītu tikai vajadzīgās kolonnas

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

Aizstātā vērtība

Vērtību aizstāšana, lai iztīrītu vērtības atlasītajā kolonnā

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

Filtrētas rindas

Vērtību filtrēšana kolonnā

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

Pārdēvētās kolonnas

Mainītas kolonnu galvenes, lai tās būtu jēgpilnas

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Svarīgi!    Uzmanieties, rediģējot darbībasAvots,Navigācija un Mainīts tips, jo tās ir izveidotas, izmantojot Power Query, lai   definētu un iestatītu datu avotu.

Rādīt vai paslēpt formulu joslu

Formulu josla tiek rādīta pēc noklusējuma, bet, ja tā nav redzama, varat to parādīt no jauna.

  • Atlasiet Skatīt > izkārtojumu > joslā.

Formulasievadīšana formulu joslā

  1. Lai atvērtu vaicājumu, atrodiet vaicājumu, kas iepriekš ielādēts no Power Query redaktora, atlasiet kādu datu šūnu un pēc tam atlasiet Vaicājuma >Rediģēt. Papildinformāciju skatiet rakstā Vaicājuma izveide, ielāde vai rediģēšana programmā Excel.

  2. Vaicājumu rūts Iestatījumi sadaļā Lietotās darbībasatlasiet darbību, kuru vēlaties rediģēt.

  3. Formulu joslā atrodiet un mainiet parametru vērtības un pēc tam atlasiet ikonu Enter Ikona Ievadīt pa kreisi no formulu joslas līdzeklī Power Query nospiediet taustiņu Enter. Piemēram, mainiet šo formulu, lai paturētu arī Kolonna2:

    Pirms: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    Pēc:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Atlasiet ikonu Enter Ikona Ievadīt pa kreisi no formulu joslas līdzeklī Power Query nospiediet taustiņu Enter, lai jaunos rezultātus skatītu datu priekšskatījumā.

  5. Lai rezultātus skatītu darblapas Excel, atlasiet Sākums un> Aizvērt un & Ielādēt.

Formulas izveide formulu joslā

Vienkāršas formulas piemēram pārvērtīsim teksta vērtību uz īpašvārdu reģistru, izmantojot text.Properfunkciju.

  1. Lai atvērtu tukšu vaicājumu, sadaļā Excel Dati> Iegūt datus > no citiem avotiem > tukšs vaicājums. Papildinformāciju skatiet rakstā Vaicājuma izveide, ielāde vai rediģēšana programmā Excel.

  2. Formulu joslā ievadiet tekstu=Text.Proper("text value")pēc tam atlasiet ikonu Enter Ikona Ievadīt pa kreisi no formulu joslas līdzeklī Power Query vai nospiediet taustiņu Enter.

    Rezultāti tiek rādīti datu priekšskatījumā.

  3. Lai rezultātus skatītu darblapas Excel, atlasiet Sākums un> Aizvērt un & Ielādēt.

Rezultāts

Aktivizējamās animācijas atlase

 Izveidojot formulu, Power Query pārbauda formulas sintaksi. Tomēr, vaicājuma ievietojot, pārkārtojot vai izdzēšot starpsoļu soli, iespējams, vaicājums var tikt pārkārtots.  Vienmēr pārbaudiet rezultātus datu priekšskatījumā.

Svarīgi!    Uzmanieties, rediģējot darbībasAvots,Navigācija un Mainīts tips, jo tās ir izveidotas, izmantojot Power Query, lai   definētu un iestatītu datu avotu.

Formulas rediģēšana, izmantojot dialoglodziņu

Šī metode izmanto dialoglodziņus, kas atšķiras atkarībā no darbības. Nav jāzina formulas sintakse.

  1. Lai atvērtu vaicājumu, atrodiet vaicājumu, kas iepriekš ielādēts no Power Query redaktora, atlasiet kādu datu šūnu un pēc tam atlasiet Vaicājuma >Rediģēt. Papildinformāciju skatiet rakstā Vaicājuma izveide, ielāde vai rediģēšana programmā Excel.

  2. Vaicājumu Iestatījumi sadaļā Lietotās darbības atlasiet tās darbības ikonu Rediģēt Iestatījumi Izkārtojuma Procesa saraksts attēls , kuru vēlaties rediģēt, vai noklikšķiniet uz darbības ar peles labo pogu un pēc tam atlasiet Rediģēt Iestatījumi .

  3. Dialoglodziņā veiciet izmaiņas un pēc tam atlasiet Labi.

Darbības ievietošana

Kad ir pabeigta vaicājuma darbība, kas pārkārto jūsu datus, zem pašreizējā vaicājuma darbības tiek pievienota vaicājuma darbība. bet, kad ievietojat vaicājuma darbību darbību vidū, kļūda var rasties nākamajās darbībās. Power Query parāda ievietošanas darbības brīdinājumu, kad mēģināt ievietot jaunu darbību, un jaunā darbība maina laukus, piemēram, kolonnu nosaukumus, kas tiek izmantoti jebkurā darbībā, kas tiek izmantota pēc ievietotās darbības.

  1. Vaicājumu rūts Iestatījumi sadaļā Lietotās darbībasatlasiet darbību, kuru vēlaties veikt uzreiz pirms jaunās darbības un tai atbilstošās formulas.

  2. Atlasiet ikonu Pievienot Ikona Funkcija , kas atrodas formulu joslas kreisajā pusē. Vai arī ar peles labo pogu noklikšķiniet uz darbības un pēc tam atlasiet Ievietot darbību pēc.Tiek izveidota jauna formula šādā formātā:

    = <nameOfTheStepToReference>, piemēram, =Production.WorkOrder.

  3. Ierakstiet jauno formulu, izmantojot šādu formātu:

    =Class.Function(ReferenceStep[,otherparameters])

    Piemēram, pieņemsim, ka jums ir tabula ar kolonnu Dzimums un vēlaties pievienot kolonnu ar vērtību "Ms". vai "Mr." atkarībā no personas dzimuma. Formula būtu:

    =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Formulas piemērs

Pārkārtot darbību

  • Vaicājumu vai vaicājumu Iestatījumi sadaļā Lietotās darbības ar peleslabo pogu noklikšķiniet uz darbības un pēc tam atlasiet Pārvietot augšup vai Pārvietot lejup.

Dzēst soli

  • Atlasiet ikonu Dzēst soli pa kreisi no darbības vai ar peles labo pogu noklikšķiniet uz darbības un pēc tam atlasiet Dzēst vai Dzēst līdz beigām. Ikona Dzēst soli ir pieejama arī formulu joslas kreisajā pusē.

Šajā piemērā pārvērtsim tekstu kolonnā īpašvārdu reģistru, izmantojot formulu kombināciju detalizētajā redaktorā. 

Piemēram, jums ir tabula Excel ar nosaukumu Pasūtījumi, ar kolonnu ProductName, kuru vēlaties pārvērst par īpašvārdu reģistru. 

Pirms:

Pirms

Pēc:

4. darbība — rezultāts

Izveidojot paplašinātu vaicājumu, tiek izveidota vaicājuma formulas darbību sērija, kuras pamatā ir atļaušanas izteiksme. Izmantojiet atļaušanu izteiksmi, lai piešķirtu nosaukumus un aprēķinātu vērtības, uz kurām pēc tam atsaucas klauzulas klauzula, kas definē darbību. Šajā piemērā tiek atgriezts tāds pats rezultāts, kā sadaļā "Formulas izveide formulu joslā".

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

Redzēsit, ka katras darbības pamatā ir iepriekšējā darbība, atsaucoties uz darbību pēc nosaukuma. Atgādinājums: Power Query formulu valoda ir reģistrjutīga.

1. posms Atveriet detalizēto redaktoru

  1. Vaicājuma Excel atlasiet Datu> Datu >citi avoti > Tukšs vaicājums. Papildinformāciju skatiet rakstā Vaicājuma izveide, ielāde vai rediģēšana programmā Excel.

  2. Power Query redaktorā atlasiet Sākums> Papildu redaktors, kas tiek atvērts ar opcijas Ļaut izteiksmes veidni.

Darbinieku atskaites drukas priekšskatījums

2. posms Datu avota definēšana

  1. Izveidojiet atļaušanu izteiksmi, izmantojot Excel. Funkcija CurrentWorkbook ir šāda:

    let#x1

    in
        Source

    Dialoglodziņš Tālummaiņa

  2. Lai vaicājumu ielādētu darblapā, atlasiet Gatavs unpēc tam atlasiet Sākums> Aizvērt un & Ielādēt > aizvērt & Ielādēt.

Rezultāts

Matemātiskais simbols

3. posms Paaugstināt pirmo rindu par galvenēm

  1. Lai atvērtu vaicājumu, darblapā atlasiet datu šūnu un pēc tam atlasiet Vaicājums un >Rediģēt. Papildinformāciju skatiet rakstā Vaicājuma izveide, ielāde vai rediģēšana programmā Excel (Power Query).

  2. Power Query redaktorā atlasiet Sākums un >redaktors, kas tiek atvērts ar 2. fāzē izveidoto priekšrakstu. Definējiet datu avotu.

  3. Let izteiksmē pievienojiet #"First Row as Header" un Table.PromoteHeaders šādi

    let:
        
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   #"First Row as Header" = Table.PromoteHeaders(Source)#x3

        #"First Row as Header"

  4. Lai vaicājumu ielādētu darblapā, atlasiet Gatavs unpēc tam atlasiet Sākums> Aizvērt un & Ielādēt > aizvērt & Ielādēt.

Rezultāts

3. darbība — rezultāts

4. posms Mainiet katru kolonnas vērtību uz īpašvārdu reģistru

  1. Lai atvērtu vaicājumu, darblapā atlasiet datu šūnu un pēc tam atlasiet Vaicājums un >Rediģēt. Papildinformāciju skatiet rakstā Vaicājuma izveide, ielāde vai rediģēšana programmā Excel.

  2. Power Query redaktorā atlasiet Sākums un > redaktors, kas tiek atvērts ar 3.fāzē izveidoto priekšrakstu: izmantojiet pirmo rindu kā galvenes.

  3. Ļaujiet izteiksmei pārvērst katru kolonnas ProductName vērtību par īpašvārdu tekstu, izmantojot funkciju Table.TransformColumns, atsaucoties uz iepriekšējo vaicājuma formulas darbību "First Row as Header", pievienojot datu avotam #"Capitalized Each Word" un pēc tam rezultātu piešķirot #"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. Lai vaicājumu ielādētu darblapā, atlasiet Gatavs unpēc tam atlasiet Sākums> Aizvērt un & Ielādēt > aizvērt & Ielādēt.

Rezultāts

4. darbība — rezultāts

Varat kontrolēt formulu joslas darbību Power Query redaktorā visām savām darbgrāmatām.

Parādīt vai paslēpt formulu joslu

  1. Atlasiet Faila> Opcijas un pēc Iestatījumi > Vaicājuma opcijas.

  2. Kreisās rūts sadaļā GLOBĀLIatlasiet Power Query redaktors.

  3. Labās rūts sadaļā Izkārtojums atzīmējietvai notīriet izvēles rūtiņu Parādīt formulu joslu.

M IntelliSense ieslēgšana vai izslēgšana

  1. Atlasiet Faila> Opcijas un pēc Iestatījumi > Vaicājuma opcijas.

  2. Kreisās rūts sadaļā GLOBĀLIatlasiet Power Query redaktors.

  3. Labās rūts sadaļā Formulaatlasiet vai notīriet opciju Iespējot M IntelliSense formulu joslā, papildu redaktorā un pielāgotas kolonnas dialoglodziņā.

Piezīme    Šī iestatījuma maiņa stāsies spēkā nākamajā Power Query redaktora loga atvēršanas reizē.

Papildinformācija

Palīdzība par Power Query Excel lietojumprogrammām

Pielāgotas funkcijas izveide un izsaukšanu

Lietoto darbību saraksta izmantošana (docs.com)

Pielāgotu funkciju izmantošana (docs.com)

Power Query M formulas (docs.com)

Darbs ar kļūdām (docs.com)

Vai nepieciešama papildu palīdzība?

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×