Power Query formulu izveide programmā Excel

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

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

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. Vaicājuma iestatījumu rūts, kurā ir uzskaitīti rekvizīti un katra darbība vaicājumā

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

Var būt reizes, kad vēlaties modificēt vai izveidot formulu. Formulās tiek izmantota Power Query valodas formulas, 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 formulu valoda.

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

Procedūra

1. Lai importētu datus, 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, vaicājuma iestatījumu rūts 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, lodziņā Ko atrast vērtības ievadiet "detalizēti" un pēc tam atlasiet Labi.

6. Lai noņemtu rindas, kurās ir vārds "Gads", atlasiet filtra bultiņu Kolonnā1, 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 un & Ielādēt.

Rezultāts

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

Vaicājuma solis un uzdevums	Formula
Avots Savienojuma izveide 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 (Power Query 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ības Avots,  Navigācija un Mainīts tips, jo tās ir 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ā.

Formulas ievadīšana formulu joslā

1. Lai atvērtu vaicājumu, atrodiet vaicājumu, kas iepriekš ielādēts no Power Query redaktors, atlasiet 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ājuma iestatījumu rūts sadaļā Lietotās darbības atlasiet darbību, kuru vēlaties rediģēt.

3. Formulu joslā atrodiet un mainiet parametru vērtības un pēc tam atlasiet ikonu Enter nospiediet taustiņu Enter. Piemēram, mainiet šo formulu, lai saglabā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 nospiediet taustiņu Enter, lai jaunos rezultātus skatītu datu priekšskatījumā.

5. Lai skatītu rezultātu Excel darblapā, atlasiet Sākums un> Aizvērt, & 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, programmā Excel atlasiet Dati> Iegūt datus > no > tukša vaicājuma. Papildinformāciju skatiet rakstā Vaicājuma izveide, ielāde vai rediģēšana programmā Excel.

2. Formulu joslā ievadiet enter=Text.Proper("text value")un pēc tam atlasiet ikonu Enter nospiediet taustiņu Enter.
   
   Rezultāti tiek rādīti datu priekšskatījumā.

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

Rezultāts

 Izveidojot formulu, programma Power Query validē 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ības Avots,  Navigācija un Mainīts tips, jo tās ir 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 redaktors, atlasiet 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. Rūts Vaicājuma iestatījumi sadaļā Lietotās darbības atlasiet rediģējamās darbības vai ar peles labo pogu noklikšķiniet uz darbības ikonas un pēc tam atlasiet Rediģēt iestatījumus.

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 tiek parādīts brīdinājums Par ievietošanas darbību, mēģinot 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ājuma iestatījumu rūts sadaļā Lietotās darbības atlasiet darbību, kuru vēlaties veikt uzreiz pirms jaunās darbības un tai atbilstošās formulas.

2. Atlasiet ikonu darbību pa kreisi no formulu joslas. 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.")

Pārkārtot darbību

• Vaicājumu iestatījumu rūtī sadaļā Lietotās darbības ar peles labo pogu noklikšķiniet uz darbības un pēc tam atlasiet Pārvietot augšup vaiPārvietot lejup.

Dzēst soli

• Atlasiet ikonu 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 ir pieejama arī formulu joslas kreisajā pusē.

Šajā piemērā pārvērtīsim tekstu kolonnā īpašvārdu reģistru, izmantojot formulu kombināciju kolonnā Paplašinātais redaktors. 

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

Pirms:

Pēc:

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. Šis piemērs atgriež tādu pašu rezultātu 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: formulas Power Query ir reģistrjutīga.

1. posms Atveriet Paplašinātais redaktors

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

2. Dialoglodziņā Power Query redaktors atlasiet Sākums> Paplašinātais redaktors, kas tiek atvērta ar ļaujiet izteiksmes veidni.

2. posms Datu avota definēšana

1. Izveidojiet atļaušanas izteiksmi, izmantojot funkciju Excel.CurrentWorkbook, kā norādīts tālāklet
   
   
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]#x2
   
       
   
   Source#x4

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

Rezultāts

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. Izvēlnē Power Query redaktors Sākums> Paplašinātais redaktors, kas tiek atvērta ar priekšrakstu, kuru izveidojāt 2. fāzē. Definējiet datu avotu.

3. Let izteiksmē pievienojiet #"First Row as Header" un Table.PromoteHeaders šādilet
   :
   
       
   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 un pēc tam atlasiet Sākums> Aizvērt un & ielādēt >aizvērt & ielādēt.

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. Izvēlnē Power Query redaktors Sākums> Paplašinātais redaktors, kas tiek atvērts ar priekšrakstu, kuru izveidojāt 3. posmā: 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", datu avotam pievienojot #"Capitalized Each Word" un pēc tam rezultātā 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 un pēc tam atlasiet Sākums> Aizvērt un & ielādēt >aizvērt & ielādēt.

Rezultāts

Varat kontrolēt formulu joslas darbību darbību Power Query redaktors visās darbgrāmatās.

Parādīt vai paslēpt formulu joslu

1. Atlasiet Fails > Opcijas un iestatījumi >Vaicājuma opcijas.

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

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

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

1. Sadaļā Vaicājuma > atlasiet Opcijas > Iestatījumi .

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

3. Labās rūts sadaļā Formula atlasiet vai notīriet 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ā reizē, kad atvērsiet Power Query redaktors logu.