Probabil că sunteți destul de familiarizat cu interogările cu parametri legate de utilizarea lor în SQL sau Microsoft Query. Cu toate acestea, parametrii Power Query prezintă diferențe cheie:
- Parametrii pot fi utilizați în orice pas de interogare. Pe lângă rolul de filtru de date, parametrii pot fi utilizați pentru a specifica lucruri cum ar fi calea unui fișier sau un nume de server.
- Parametrii nu solicită intrări. În schimb, puteți modifica rapid valoarea acestora utilizând Power Query. Puteți chiar să stocați și să regăsiți valorile din celule în Excel.
- Parametrii sunt salvați într-o interogare simplă cu parametri, dar sunt separați de interogările de date în care sunt utilizați. Odată creat, puteți adăuga un parametru la interogări, după cum este necesar.
Notă Dacă doriți un alt mod de a crea interogări cu parametri, consultați Crearea unei interogări cu parametri în Microsoft Query.
Crearea unui parametru
Puteți utiliza un parametru pentru a modifica automat o valoare dintr-o interogare și a evita editarea interogării de fiecare dată pentru a modifica valoarea. Pur și simplu modificați valoarea parametrului. După ce creați un parametru, acesta este salvat într-o interogare cu parametri speciale, pe care o puteți modifica în mod convenabil direct din Excel.
Selectare date>Obținere date>Alte surse>Lansați Editor Power Query.
În Editor Power Query, selectați Pornire>Gestionare parametri > Parametri noi.
În caseta de dialog Gestionare parametru , selectați Nou.
Setați următoarele după cum este necesar:
Nume Aceasta ar trebui să reflecte funcția parametrului, dar să fie cât mai scurtă posibil. Descriere Acesta poate conține orice detalii care îi vor ajuta pe utilizatori să utilizeze corect parametrul. Obligatorii Alegeți una dintre următoarele variante:
Orice valoare Puteți introduce orice valoare sau orice tip de date în interogarea cu parametri.
Listă de valori Puteți limita valorile la o anumită listă introducându-le în grila mică. De asemenea, trebuie să selectați o Valoare implicită și o Valoare curentă mai jos.
Interogare Selectați o interogare listă, care seamănă cu o coloană Listă structurată, separată prin virgulă și încadrată în acolade.
De exemplu, un câmp Stare probleme poate avea trei valori: {"Nou", "În curs", "Închis"}. Trebuie să creați în prealabil interogarea de listă deschizând Editor complex (selectați HomeEditor>avansat), eliminând șablonul de cod, introducând lista de valori în formatul de listă de interogare, apoi selectând Terminat.
După ce ați terminat de creat parametrul, se afișează interogarea listă în valorile parametrilor.Tip Acesta specifică tipul de date al parametrului. Valori sugerate Dacă doriți, adăugați o listă de valori sau specificați o interogare pentru a furniza sugestii de intrare. Valoare implicită Aceasta apare numai dacă Valori sugerate este setată la Lista de valori și specifică ce element de listă este setat implicit. În acest caz, trebuie să alegeți o valoare implicită. Valoarea curentă În funcție de locul în care utilizați parametrul, dacă acesta este necompletat, interogarea poate să nu returneze niciun rezultat. Dacă este selectată opțiunea Obligatoriu , Valoarea curentă nu poate fi necompletată. Pentru a crea parametrul, selectați OK.
Utilizarea unui parametru pentru a modifica o sursă de date
Iată o modalitate de a gestiona modificările locațiilor surselor de date și a ajuta la prevenirea erorilor de reîmprospătare. De exemplu, presupunând o schemă și o sursă de date similare, creați un parametru pentru a modifica cu ușurință o sursă de date și a contribui la prevenirea erorilor de reîmprospătare a datelor. Uneori, serverul, baza de date, folderul, numele de fișier sau locația se modifică. Poate că un manager de baze de date înlocuiește ocazional un server, o picătură lunară de fișiere CSV merge într-un alt folder sau trebuie să comutați cu ușurință între un mediu de dezvoltare/testare/producție.
Pasul 1: Crearea unei interogări cu parametri
În exemplul următor, aveți mai multe fișiere CSV pe care le importați utilizând operațiunea import folder (Select Data>Get Data>From FilesFrom>Folder) din folderul C:\DataFilesCSV1. Dar, uneori, un alt folder este utilizat ocazional ca locație pentru fixarea fișierelor, C:\DataFilesCSV2. Puteți utiliza un parametru într-o interogare ca valoare de substituție pentru folderul diferit.
Selectați Pornire>Gestionare parametri Parametru>nou.
Introduceți informațiile următoare în caseta de dialog Gestionare parametri :
Nume CSVFileDrop Descriere Locație alternativă de fixare a fișierelor Obligatorii Da Tip Text Valori sugerate Orice valoare Valoarea curentă C:\DataFilesCSV1 Selectați OK.
Pasul 2: adăugați parametrul la interogarea de date
- Pentru a seta numele de folder ca parametru, în Setări interogare, sub Pași de interogare, selectați Sursă, apoi selectați Editare setări.
- Asigurați-vă că opțiunea Cale fișier este setată la Parametru, apoi selectați parametrul pe care tocmai l-ați creat din lista verticală.
- Selectați OK.
Pasul 3: Actualizați valoarea parametrului
Locația folderului tocmai s-a modificat, așa că acum puteți actualiza pur și simplu interogarea cu parametri.
- Selectați Conexiuni de date>& Interogări>Fila Interogări , faceți clic dreapta pe interogarea cu parametri, apoi selectați Editare.
- Introduceți noua locație în caseta Valoare curentă , cum ar fi C:\FișierDateCSV2.
- Selectați Pornire>,Închidere & Încărcare.
- Pentru a confirma rezultatele, adăugați date noi la sursa de date, apoi reîmprospătați interogarea de date cu parametrul actualizat (Selectare date>Reîmprospătare totală).
Utilizarea unui parametru pentru a filtra datele
Uneori doriți o modalitate simplă de a modifica filtrul unei interogări pentru a obține rezultate diferite fără a edita interogarea sau a crea copii ușor diferite ale aceleiași interogări. În acest exemplu, schimbăm o dată pentru a modifica în mod convenabil un filtru de date.
Pentru a deschide o interogare, găsiți una încărcată anterior din Editor Power Query, selectați o celulă din date, apoi selectați Editare interogare>. Pentru mai multe informații , consultați Crearea, încărcarea sau editarea unei interogări în Excel.
Selectați săgeata de filtrare din orice antet de coloană pentru a filtra datele, apoi selectați o comandă de filtrare, cum ar fi Filtre >dată/orădupă. Apare caseta de dialog Filtrare rânduri .
Selectați butonul din stânga casetei Valoare , apoi alegeți una dintre următoarele:
- Pentru a utiliza un parametru existent, selectați Parametru, apoi selectați parametrul dorit din lista care apare în partea dreaptă.
- Pentru a utiliza un parametru nou, selectați Parametru nou, apoi creați un parametru.
Introduceți data nouă în caseta Valoare curentă, apoi selectați Pornire>,Închidere & Încărcare.
Pentru a confirma rezultatele, adăugați date noi la sursa de date, apoi reîmprospătați interogarea de date cu parametrul actualizat (Selectare date>Reîmprospătare totală). De exemplu, modificați valoarea filtrului la o altă dată pentru a vedea rezultatele noi.
Introduceți data nouă în caseta Valoare curentă .
Selectați Pornire>,Închidere & Încărcare.
Pentru a confirma rezultatele, adăugați date noi la sursa de date, apoi reîmprospătați interogarea de date cu parametrul actualizat (Selectare date>Reîmprospătare totală).
Utilizarea unei valori de celulă pentru a filtra datele
În acest exemplu, valoarea din parametrul de interogare este citită dintr-o celulă din registrul de lucru. Nu trebuie să modificați interogarea cu parametri, doar actualizați valoarea celulei. De exemplu, doriți să filtrați o coloană după prima literă, dar să modificați cu ușurință valoarea cu orice literă de la A la Z.
Pe foaia de lucru dintr-un registru de lucru unde este încărcată interogarea pe care doriți s-o filtrați, creați un tabel Excel cu două celule: un antet și o valoare.
FiltrulMeu G Selectați o celulă din tabelul Excel, apoi selectați Date>Preluate date>din tabel/zonă. Apare Editor Power Query.
În caseta Nume a panoului Setări interogare din partea dreaptă, modificați numele interogării pentru a fi mai semnificativ, cum ar fi FilterCellValue.
Pentru a transmite valoarea din tabel, nu tabelul în sine, faceți clic dreapta pe valoare în Examinare date, apoi selectați Detaliere.
Observați că formula s-a modificat în= #"Changed Type"{0}[MyFilter]
Când utilizați tabelul Excel ca filtru la pasul 10, Power Query face referire la valoarea tabelului ca condiție de filtru. O referință directă la tabelul Excel ar cauza o eroare.Selectați Pornire>, Închidere & Încărcare>, Închidere & Încărcare în. Acum aveți un parametru de interogare denumit "FilterCellValue" pe care îl utilizați la pasul 12.
În caseta de dialog Import date , selectați Se creează doar conexiunea, apoi OK.
Deschideți interogarea pe care doriți să o filtrați cu valoarea din tabelul FilterCellValue, una încărcată anterior din Editorul Power Query, selectând o celulă din date, apoi selectând Editare interogare>. Pentru mai multe informații , consultați Crearea, încărcarea sau editarea unei interogări în Excel.
Selectați săgeata de filtrare din orice antet de coloană pentru a filtra datele, apoi selectați o comandă de filtrare, cum ar fi Filtre >de textîncepe cu. Apare caseta de dialog Filtrare rânduri .
Introduceți orice valoare în caseta Valoare , cum ar fi "G", apoi selectați OK. În acest caz, valoarea este un substituent temporar pentru valoarea din tabelul FilterCellValue pe care îl introduceți în pasul următor.
Selectați săgeata din partea dreaptă a barei de formule pentru a afișa întreaga formulă. Iată un exemplu de condiție de filtrare într-o formulă:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Selectați valoarea filtrului. În formulă, selectați "G".
Utilizând M Intellisense, introduceți primele câteva litere ale tabelului FilterCellValue pe care l-ați creat, apoi selectați-l din lista care apare.
Selectați Pornire>, Închidere>,Închidere & Încărcare.
Rezultat
Interogarea utilizează acum valoarea din tabelul Excel pe care l-ați creat pentru a filtra rezultatele interogării. Pentru a utiliza o valoare nouă, editați conținutul celulei în tabelul Excel original la pasul 1, modificați "G" cu "V", apoi reîmprospătați interogarea.
Controlul utilizării interogărilor cu parametri
Puteți controla dacă interogările cu parametri sunt permise sau nu.
- În Editor Power Query, selectați Opțiuni fișier>și Setări> Opțiuni >interogareEditorPower Query.
- În panoul din stânga, sub GLOBAL, selectați Editor Power Query.
- În panoul din dreapta, sub Parametri, bifați sau debifați Permiteți întotdeauna parametrizarea în casetele de dialog surse de date și transformări.