Parametru vaicājuma izveide (Power Query)

Parametrus var izmantot, lai vaicājumā automātiski mainītu vērtību un izvairītos no vaicājuma rediģēšanas katru reizi, lai mainītu vērtību. Vienkārši mainiet parametra vērtību. Kad izveidojat parametru, tas tiek saglabāts īpašā parametru vaicājumā, kuru var ērti mainīt tieši no programmas Excel.

1. Atlasiet Dati> iegūt datus >citu avotu >palaist Power Query redaktors.

2. Dialoglodziņā Power Query redaktors Sākums un >Pārvaldīt parametrus > Jauni parametri.

3. Dialoglodziņā Parametra pārvaldība atlasiet Jauns.

4. Ja nepieciešams, iestatiet šādas iespējas:

   Nosaukums	Tam ir jāatspoguļo parametra funkcija, bet tā ir pēc iespējas īsumā.
   Apraksts	Tas var ietvert jebkādu detalizētu informāciju, kas lietotājiem palīdz pareizi izmantot parametru.
   Obligāts	Veiciet vienu no šīm darbībām: Jebkura vērtība Parametru vaicājumā var ievadīt jebkuru vērtību no jebkura datu tipa. Vērtību saraksts    Varat ierobežot vērtības līdz konkrētam sarakstam, ievadot tās mazajā režģī. Tālāk ir jāatlasa arī vērtība Noklusējuma vērtība un Pašreizējā vērtība. Vaicājums Atlasiet saraksta vaicājumu, kas līdzinās saraksta strukturētām kolonnām, kas atdalītas ar komatiem un iekļautas figūriekaavās. Piemēram, statusa laukā Problēmas var būt trīs vērtības: {"Jauns", "Notiek", "Slēgta"}. Vispirms izveidojiet saraksta vaicājumu, atverot Paplašinātais redaktors (atlasiet Sākums> Paplašinātais redaktors), noņemot koda veidni, ievadot vērtību sarakstu vaicājumu saraksta formātā un pēc tam atlasot Gatavs. Kad esat pabeidzis parametra izveidi, saraksta vaicājums tiek parādīts jūsu parametru vērtībās.
   Veids	Tas norāda parametra datu tipu.
   Ieteiktās vērtības	Ja nepieciešams, pievienojiet vērtību sarakstu vai norādiet vaicājumu, lai sniegtu ievades ieteikumus.
   Noklusējuma vērtība	Tas tiek rādīts tikai tad , ja ieteiktās vērtības ir iestatītas kā Vērtību saraksts, un norāda, kurš saraksta vienums ir noklusējuma elements. Šādā gadījumā ir jāizvēlas noklusējuma iestatījums.
   Pašreizējā vērtība	Atkarībā no tā, kur izmantojat parametru, ja šis ir tukšs, vaicājums var atgriezt nekādus rezultātus. Ja atlasīta opcija Obligāts, pašreizējā vērtība nedrīkst būt tukša.

5. Lai izveidotu parametru, atlasiet Labi.

Tālāk ir norādīts veids, kā pārvaldīt datu avotu atrašanās vietu izmaiņas un novērst atsvaidzināšanas kļūdas. Piemēram, pieņemot līdzīgu shēmu un datu avotu, izveidojiet parametru, lai ērti mainītu datu avotu un palīdzētu novērst datu atsvaidzināšanas kļūdas. Dažreiz tiek mainīts serveris, datu bāze, mape, faila nosaukums vai atrašanās vieta. Iespējams, datu bāzu pārvaldnieks laiku pa laikam samaina serveri, CSV failu mēneša nomešana nonāk citā mapē vai ir vienkārši jāpārslēdzas starp izstrādes/testa/ražošanas vidi.

1. darbība. Parametra vaicājuma izveide

Šajā piemērā jums ir vairāki CSV faili, kurus importējat, izmantojot importēšanas mapes darbību (Datu atlase> Iegūt datus> No failiem > No mapes) no mapes C:\DataFilesCSV1. Tomēr dažreiz cita mape dažreiz tiek izmantota kā failu nomešanas vieta C:\DataFilesCSV2. Varat izmantot parametru vaicājumā kā citas mapes aizstājēja vērtību.

1. Atlasiet Sākums > Pārvaldīt parametrus > jauns parametrs.

2. Dialoglodziņā Parametra pārvaldība ievadiet šādu informāciju:

   Nosaukums	CSVFileDrop
   Apraksts	Alternatīvā failu nomešanas vieta
   Obligāts	Jā
   Veids	Teksts
   Ieteiktās vērtības	Jebkura vērtība
   Pašreizējā vērtība	C:\DataFilesCSV1

3. Atlasiet Labi.

2. darbība. Parametra pievienošana datu vaicājumam

1. Lai mapes nosaukumu iestatītu kā parametru, sadaļas Vaicājuma iestatījumi sadaļā Vaicājuma darbības atlasiet Avots un pēc tam atlasiet Rediģēt iestatījumus.

2. Pārliecinieties , vai opcija Faila ceļš ir iestatīta kā Parametrs, un pēc tam atlasiet parametru, ko tikko izveidojāt nolaižamajā sarakstā.

3. Atlasiet Labi.

3. darbība. Atjauniniet parametra vērtību

Tikko mainīta mapes atrašanās vieta, tāpēc tagad varat vienkārši atjaunināt parametru vaicājumu.

1. Cilnē Vaicājumi > & Datu savienojumi > ar peles labo pogu noklikšķiniet uz parametru vaicājuma un pēc tam atlasiet Rediģēt.

2. Ievadiet jauno atrašanās vietu lodziņā Pašreizējā vērtība , piemēram, C:\DataFilesCSV2.

3. Atlasiet Sākums > Aizvērt un & Ielādēt.

4. Lai apstiprinātu rezultātus, pievienojiet jaunus datus datu avotam un pēc tam atsvaidziniet datu vaicājumu ar atjaunināto parametru (atlasiet Dati un> Atsvaidzināt visu).

Dažkārt ir nepieciešams vienkāršs veids, kā mainīt vaicājuma filtru, lai iegūtu atšķirīgus rezultātus bez vaicājuma rediģēšanas vai tā paša vaicājuma kopiju izveidošanas. Šajā piemērā tiek mainīts datums, lai ērti mainītu datu filtru.

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. Atlasiet filtra bultiņu jebkurā kolonnas galvenē, lai filtrētu datus, un pēc tam atlasiet filtra komandu, piemēram, Datuma /laika filtri> Pēc. Tiek atvērts dialoglodziņš Rindu filtrēšana.
   
   

3. Atlasiet pogu pa kreisi no lodziņa Vērtība un pēc tam veiciet kādu no šīm darbībām:

   • Lai izmantotu esošu parametru, atlasiet Parametrs un pēc tam labajā pusē redzamajā sarakstā atlasiet parametru.

   • Lai izmantotu jaunu parametru, atlasiet Jauns parametrs un pēc tam izveidojiet parametru.

4. Ievadiet jauno datumu lodziņā Pašreizējā vērtība un pēc tam atlasiet Sākums un> Aizvērt, & Ielādēt.

5. Lai apstiprinātu rezultātus, pievienojiet jaunus datus datu avotam un pēc tam atsvaidziniet datu vaicājumu ar atjaunināto parametru (atlasiet Dati un> Atsvaidzināt visu). Piemēram, lai skatītu jaunus rezultātus, mainiet filtra vērtību uz citu datumu.

6. Ievadiet jauno datumu lodziņā Pašreizējā vērtība.

7. Atlasiet Sākums > Aizvērt un & Ielādēt.

8. Lai apstiprinātu rezultātus, pievienojiet jaunus datus datu avotam un pēc tam atsvaidziniet datu vaicājumu ar atjaunināto parametru (atlasiet Dati un> Atsvaidzināt visu).

Šajā piemērā vērtība vaicājuma parametrā tiek lasīta no šūnas jūsu darbgrāmatā. Jums nav jāmaina parametru vaicājums, vienkārši atjauniniet šūnas vērtību. Piemēram, vēlaties filtrēt kolonnu pēc pirmā burta, bet viegli mainīt vērtību uz jebkuru burtu no A līdz Z.

1. Darbgrāmatas darblapā, kurā tiek ielādēts vaicājums, kuru vēlaties filtrēt, izveidojiet Excel tabulu ar divām šūnām: galveni un vērtību.
    

   Mans filtrs
   G

2. Atlasiet šūnu Excel tabulā, pēc tam atlasiet Dati un >Iegūt datus > No tabulas/diapazona. Tiek Power Query redaktors teksts.

3. Rūts Vaicājuma iestatījumi lodziņā Nosaukums labajā pusē mainiet vaicājuma nosaukumu, lai tas būtu jēgpilnāk, piemēram, FilterCellValue. 

4. Lai nodotu vērtību tabulā, nevis pašā tabulā, ar peles labo pogu noklikšķiniet uz vērtības datu priekšskatījumā un pēc tam atlasiet Rādīt detalizēti.

   Ņemiet vērā, ka formula ir mainīta uz = #"Changed Type"{0}[MyFilter]

   Ja izmantojat Excel tabulu kā filtru 10. darbībā, Power Query tabulas vērtību kā filtra nosacījumu. Tieša atsauce uz Excel tabulu var izraisīt kļūdu.

5. Atlasiet Sākums > Aizvērt un & ielādēt > Aizvērt& Ielādēt. Tagad jums ir vaicājuma parametrs ar nosaukumu "FilterCellValue", ko izmantojat 12. darbībā.

6. Dialoglodziņā Datu importēšana atlasiet Tikai izveidot savienojumu un pēc tam atlasiet Labi.

7. Atveriet vaicājumu, kuru vēlaties filtrēt ar vērtību tabulā FilterCellValue, kas iepriekš tika ielādēta no kolonnas Power Query redaktors, atlasot kādu datu šūnu un pēc tam atlasot Vaicājums> Rediģēt. Papildinformāciju skatiet rakstā Vaicājuma izveide, ielāde vai rediģēšana programmā Excel.

8. Atlasiet filtra bultiņu jebkurā kolonnas galvenē, lai filtrētu datus, un pēc tam atlasiet filtra komandu, piemēram, Teksta filtri > Sākt ar. Tiek atvērts dialoglodziņš Rindu filtrēšana. 

9. Ievadiet lodziņā Vērtība jebkuru vērtību , piemēram, "G", un pēc tam atlasiet Labi. Šajā gadījumā vērtība ir tabulas FilterCellValue vērtības pagaidu vietturis, kuru ievadāt nākamajā darbībā.

10. Atlasiet bultiņu formulu joslas labajā pusē, lai parādītu visu formulu. Lūk, filtra nosacījuma piemērs formulā:
    
    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

11. Atlasiet filtra vērtību. Formulā atlasiet "G".

12. Izmantojot M IntelliSense, ievadiet izveidotās tabulas FilterCellValue pirmos burtus un pēc tam atlasiet to parādītajā sarakstā.

13. Atlasiet Sākums > Aizvērt un > aizvērt& Ielādēt.

Rezultāts

Vaicājums tagad izmanto vērtību Excel tabulā, kuru izveidojāt, lai filtrētu vaicājuma rezultātus. Lai izmantotu jaunu vērtību, rediģējiet šūnas saturu sākotnējā Excel tabulā 1. darbībā, mainiet "G" uz "V" un pēc tam atsvaidziniet vaicājumu.

Varat kontrolēt, vai parametru vaicājumi ir atļauti vai nav atļauti.

1. Dialoglodziņā Power Query redaktors Atlasiet Fails un> opcijas > Vaicājuma opcijas >Power Query redaktors.

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

3. Rūts labajā pusē sadaļā Parametri atzīmējiet vai notīriet izvēles rūtiņu Vienmēr atļaut parametrus datu avota un transformācijas dialogos.