Iespējams, jūs labi pārzināt parametru vaicājumus un to izmantošanu SQL vai Microsoft Query. Tomēr Power Query parametriem ir galvenās atšķirības:
- Parametrus var izmantot jebkurā vaicājuma darbībā. Parametrus var izmantot ne tikai kā datu filtru, bet arī norādīt, piemēram, faila ceļu vai servera nosaukumu.
- Parametri neprasa ievadi. Tā vietā varat ātri mainīt to vērtību, izmantojot Power Query. Varat pat saglabāt un izgūt vērtības no šūnām programmā Excel.
- Parametri tiek saglabāti vienkāršā parametru vaicājumā, bet ir atdalīti no datu vaicājumiem, kuros tie tiek izmantoti. Pēc izveides vaicājumiem pēc vajadzības varat pievienot parametru.
Piezīme Ja vēlaties parametru vaicājumus izveidot citā veidā, skatiet sadaļu Parametru vaicājuma izveide programmā Microsoft Query.
Parametra izveide
Varat izmantot parametru, lai automātiski mainītu vērtību vaicājumā, un izvairīties no vaicājuma rediģēšanas katru reizi, kad mainīta vērtība. Jūs vienkārši mainiet parametra vērtību. Kad esat izveidojis parametru, tas tiek saglabāts īpašā parametru vaicājumā, kuru varat ērti mainīt tieši no Excel.
Datuatlasīšana>, datu > iegūšana, citi avoti>Palaidiet Power Query redaktoru.
Power Query redaktorā atlasiet Sākums>Pārvaldīt parametrus > Jauni parametri.
Dialoglodziņā Parametru pārvaldība atlasiet Jauns.
Iestatiet tālāk norādītos iestatījumus pēc nepieciešamības.
Nosaukums Tam jāatspoguļo parametra funkcija, taču tam jābūt pēc iespējas īsākam. Apraksts Tajā var būt jebkāda detalizēta informācija, kas lietotājiem palīdzēs pareizi izmantot parametru. Obligāts Veiciet vienu no šīm darbībām:
Jebkura vērtība Parametru vaicājumā varat ievadīt jebkādu jebkura datu tipa vērtību.
Vērtību saraksts Varat ierobežot vērtības noteiktā sarakstā, ievadot tās nelielā režģī. Tālāk ir jāatlasa arī noklusējuma vērtība un pašreizējā vērtība .
Vaicājums Atlasiet saraksta vaicājumu, kas līdzinās ar komatiem atdalītai un figūriekavās iekļautai saraksta kolonnai.
Piemēram, statusa laukā Problēmas var būt trīs vērtības: {"Jauns", "Notiek", "Slēgts"}. Saraksta vaicājums ir jāizveido iepriekš, atverot Paplašinātais redaktors (atlasiet HomePaplaš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.Tips Norāda parametra datu tipu. Ieteicamā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 Šis vienums tiek parādīts tikai tad, ja opcija Ieteicamās vērtības ir iestatīta pozīcijā Vērtību saraksts, un tiek norādīts, kurš saraksta elements ir noklusējuma elements. Šādā gadījumā ir jāizvēlas noklusējums. Pašreizējā vērtība Atkarībā no tā, kur izmantojat parametru, ja tas ir tukšs, vaicājums var neatgriezt rezultātus. Ja ir atlasīts Nepieciešams , pašreizējā vērtība nevar būt tukša. Lai izveidotu parametru, atlasiet Labi.
Parametra izmantošana, lai mainītu datu avotu
Tālāk ir aprakstīts veids, kā pārvaldīt izmaiņas datu avotu atrašanās vietās un palīdzēt novērst atsvaidzināšanas kļūdas. Piemēram, pieņemot, ka izmantota līdzīga shēma un datu avots, izveidojiet parametru, lai viegli mainītu datu avotu un palīdzētu novērst datu atsvaidzināšanas kļūdas. Dažreiz mainās serveris, datu bāze, mape, faila nosaukums vai atrašanās vieta. Iespējams, datu bāzes pārvaldnieks laiku pa laikam samaina serveri, katru mēnesi CSV faili tiek nomesti citā mapē vai jums ir nepieciešams ērti pārslēgties starp izstrādes/testēšanas/ražošanas vidi.
1. darbība. Parametru vaicājuma izveide
Šajā piemērā jums ir vairāki CSV faili, kurus importējat, izmantojot importēšanas mapes darbību (Select Data>Get Data>from FilesFrom>Folder) no mapes C:\DataFilesCSV1. Taču dažreiz kā atrašanās vieta failu nomešanai reizēm tiek izmantota cita mape (C:\DataFilesCSV2). Varat izmantot parametru vaicājumā kā citas mapes aizstājējvērtību.
Atlasiet Home>Manage Parameters>New parametr.
Ievadiet šādu informāciju dialoglodziņā Parametru pārvaldība :
Nosaukums CSVFileDrop Apraksts Alternatīva failu nomešanas vieta Obligāts Jā Tips Teksts Ieteicamās vērtības Jebkura vērtība Pašreizējā vērtība C:\DataFilesCSV1 Atlasiet Labi.
2. darbība. Parametra pievienošana datu vaicājumam
- Lai iestatītu mapes nosaukumu kā parametru, vaicājuma iestatījumu sadaļā Vaicājuma darbības atlasiet Avots un pēc tam atlasiet Rediģēt iestatījumus.
- Pārliecinieties, vai opcija Faila ceļš ir iestatīta uz Parametrs, un pēc tam nolaižamajā sarakstā atlasiet parametru, ko tikko izveidojāt.
- Atlasiet Labi.
3. darbība. Parametra vērtības atjaunināšana
Mapes atrašanās vieta tikko ir mainīta, tāpēc tagad varat vienkārši atjaunināt parametru vaicājumu.
- Atlasiet Datu>savienojumi & vaicājumi>Vaicājumi , ar peles labo pogu noklikšķiniet uz parametru vaicājuma un pēc tam atlasiet Rediģēt.
- Pašreizējās vērtības lodziņā ievadiet jauno atrašanās vietu, piemēram, C:\DataFilesCSV2.
- Atlasiet Sākums,>Aizvērt & Ielādēt.
- Lai apstiprinātu rezultātus, pievienojiet jaunus datus datu avotam un pēc tam atsvaidziniet datu vaicājumu ar atjaunināto parametru (Atlasiet Dati>Atsvaidzināt visu).
Parametra izmantošana datu filtrēšanai
Dažkārt ir nepieciešams ērti mainīt vaicājuma filtru, lai iegūtu atšķirīgus rezultātus, nerediģējot vaicājumu un neveidojot tā paša vaicājuma nedaudz atšķirīgas kopijas. Šajā piemērā tiek mainīts datums, lai ērti mainītu datu filtru.
Lai atvērtu vaicājumu, atrodiet kādu no iepriekš ielādētajiem vaicājumiem no Power Query redaktora, atlasiet šūnu datos un pēc tam atlasiet Vaicājuma>rediģēšana. Papildinformāciju skatiet sadaļā Vaicājuma izveide, ielāde vai rediģēšana programmā Excel.
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ņš Filtra rindas .
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 sarakstā, kas tiek parādīts labajā pusē, atlasiet vajadzīgo parametru.
- Lai izmantotu jaunu parametru, atlasiet Jauns parametrs un pēc tam izveidojiet parametru.
Ievadiet jauno datumu lodziņā Pašreizējā vērtība un pēc tam atlasiet Sākums>Aizvērt & Ielādēt.
Lai apstiprinātu rezultātus, pievienojiet jaunus datus datu avotam un pēc tam atsvaidziniet datu vaicājumu ar atjaunināto parametru (Atlasiet Dati>Atsvaidzināt visu). Piemēram, mainiet filtra vērtību uz citu datumu, lai skatītu jaunus rezultātus.
Lodziņā Pašreizējā vērtība ievadiet jauno datumu.
Atlasiet Sākums,>Aizvērt & Ielādēt.
Lai apstiprinātu rezultātus, pievienojiet jaunus datus datu avotam un pēc tam atsvaidziniet datu vaicājumu ar atjaunināto parametru (Atlasiet Dati>Atsvaidzināt visu).
Šūnas vērtības izmantošana datu filtrēšanai
Šajā piemērā vaicājuma parametra vērtība tiek nolasīta no šūnas jūsu darbgrāmatā. Parametru vaicājums nav jāmaina, vienkārši jāatjaunina šūnas vērtība. Piemēram, jūs vēlaties filtrēt kolonnu pēc pirmā burta, bet vienkārši mainīt vērtību uz jebkuru burtu no A līdz Z.
Darbgrāmatas darblapā, kurā ir 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 Atlasiet šūnu Excel tabulā un pēc tam atlasiet Dati>Iegūt datus>no tabulas/diapazona. Tiek parādīts Power Query redaktors.
Vaicājuma iestatījumu rūts lodziņā Nosaukums labajā pusē mainiet vaicājuma nosaukumu uz jēgpilnāku, piemēram, FilterCellValue.
Lai nodotu vērtību tabulā, nevis pašu tabulu, ar peles labo pogu noklikšķiniet uz vērtības datu priekšskatījumā un pēc tam atlasiet Detalizēt.
Ņemiet vērā, ka formula ir mainīta uz= #"Changed Type"{0}[MyFilter]
Kad izmantojat Excel tabulu kā filtru 10. darbībā, Power Query atsaucas uz tabulas vērtību kā filtra nosacījumu. Tieša atsauce uz Excel tabulu izraisītu kļūdu.Atlasiet Sākums>, Aizvērt & Ielādēt>,Aizvērt & Ielādēt. Jums tagad ir vaicājuma parametrs ar nosaukumu "FilterCellValue", ko izmantojat, veicot 12. darbību.
Dialoglodziņā Datu importēšana atlasiet Tikai izveidot savienojumu un pēc tam atlasiet Labi.
Atveriet vaicājumu, kuru vēlaties filtrēt ar vērtību tabulā FilterCellValue, kas iepriekš ielādēta no Power Query redaktora, atlasot šūnu datos un pēc tam atlasot Vaicājuma>rediģēšana. Papildinformāciju skatiet sadaļā Vaicājuma izveide, ielāde vai rediģēšana programmā Excel.
Atlasiet filtra bultiņu jebkurā kolonnas galvenē, lai filtrētu datus, un pēc tam atlasiet filtra komandu, piemēram, Teksta filtrs>sākas ar. Tiek atvērts dialoglodziņš Filtra rindas .
Ievadiet vērtību lodziņā, piemēram, "G", un pēc tam atlasiet Labi. Šajā gadījumā vērtība ir pagaidu vietturis vērtībai tabulā FilterCellValue, kuru ievadāt, veicot nākamo darbību.
Atlasiet bultiņu formulu joslas labajā pusē, lai parādītu visu formulu. Tālāk ir sniegts filtra nosacījuma piemērs formulā:
= Table.SelectRows(#"Mainīts tips", each Text.StartsWith([Name], "G"))
Atlasiet filtra vērtību. Formulā atlasiet "G".
Izmantojot M Intellisense, ievadiet dažus pirmos izveidotās tabulas FilterCellValue burtus un pēc tam atlasiet to parādītajā sarakstā.
Atlasiet Sākums>, Aizvērt,>Aizvērt & Ielādēt.
Rezultāts
Tagad vaicājums 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.
Parametru vaicājumu lietojuma kontrole
Varat kontrolēt, vai parametru vaicājumi ir atļauti.
- Power Query redaktorā atlasiet Faila>opcijas un Iestatījumi>Vaicājumu opcijasPower>Query redaktors.
- Kreisajā rūtī sadaļā GLOBAL atlasiet Power Query redaktors.
- Labās puses rūtī sadaļā Parametri atlasiet vai notīriet Vienmēr atļaut parametrizēšanu datu avota un transformācijas dialoglodziņos.