Parametrizarea căilor de date în Power Query

Dacă ați început deja să utilizați instrumentele programului de completare gratuit Power Query din Microsoft Excel, atunci foarte curând veți întâlni o problemă foarte specializată, dar foarte frecventă și enervantă, asociată cu întreruperea constantă a legăturilor către datele sursă. Esența problemei este că, dacă în interogarea dvs. vă referiți la fișiere sau foldere externe, atunci Power Query codifică calea absolută către acestea în textul interogării. Totul funcționează bine pe computerul tău, dar dacă te decizi să trimiți un fișier cu o cerere colegilor tăi, atunci aceștia vor fi dezamăgiți, pentru că. au o cale diferită către datele sursă de pe computerul lor, iar interogarea noastră nu va funcționa.

Ce să faci într-o astfel de situație? Să ne uităm la acest caz mai detaliat cu următorul exemplu.

Formularea problemei

Să presupunem că avem în folder E:Rapoarte de vânzări se află dosarul Top 100 de produse.xls, care este o încărcare din baza noastră de date corporativă sau din sistemul ERP (1C, SAP etc.) Acest fișier conține informații despre cele mai populare mărfuri și arată astfel în interior:

Probabil că este clar de la început că este aproape imposibil să lucrezi cu el în Excel în această formă: rândurile goale printr-unul cu date, celule îmbinate, coloane suplimentare, un antet cu mai multe niveluri etc. vor interfera.

Prin urmare, lângă acest fișier din același folder, creăm un alt fișier nou Handler.xlsx, în care vom crea o interogare Power Query care va încărca date urâte din fișierul de încărcare sursă Top 100 de produse.xls, și pune-le în ordine:

Efectuarea unei cereri către un fișier extern

Deschiderea fișierului Handler.xlsx, selectați din filă Date Comandă Obțineți date – Din fișier – Din registrul de lucru Excel (Date — Obține date — Din fișier — Din Excel), apoi specificați locația fișierului sursă și foaia de care avem nevoie. Datele selectate vor fi încărcate în editorul Power Query:

Să le readucem la normal:

1. Ștergeți liniile goale cu Acasă — Ștergeți linii — Ștergeți liniile goale (Acasă — Eliminați rândurile — Eliminați rândurile goale).
2. Ștergeți primele 4 linii inutile Acasă — Ștergeți rândurile — Ștergeți rândurile de sus (Acasă — Eliminați rândurile — Eliminați rândurile de sus).
3. Ridicați primul rând la antetul tabelului cu butonul Utilizați prima linie ca antete fila Acasă (Acasă — Folosiți primul rând ca antet).
4. Separați articolul din cinci cifre de numele produsului din a doua coloană folosind comanda coloană împărțită fila Transformare (Transformare — Divizare coloană).
5. Ștergeți coloanele inutile și redenumiți titlurile celor rămase pentru o mai bună vizibilitate.

Ca rezultat, ar trebui să obținem următoarea imagine, mult mai plăcută:

Rămâne să încărcăm această masă înnobilată înapoi în foaia din fișierul nostru Handler.xlsx echipa închideți și descărcați (Acasă — Închide&Încărcare) fila Acasă:

Găsirea căii către un fișier într-o solicitare

Acum să vedem cum arată interogarea noastră „sub capotă”, în limbajul intern încorporat în Power Query cu numele concis „M”. Pentru a face acest lucru, reveniți la interogarea noastră făcând dublu clic pe ea în panoul din dreapta Cereri și conexiuni iar pe filă Recenzie alege Editor avansat (Vizualizare — Editor avansat):

În fereastra care se deschide, a doua linie dezvăluie imediat o cale codificată pe hard către fișierul nostru original de încărcare. Dacă putem înlocui acest șir de text cu un parametru, variabilă sau un link către o celulă de foaie Excel unde această cale este pre-scrisă, atunci îl putem schimba cu ușurință mai târziu.

Adăugați un tabel inteligent cu o cale de fișier

Să închidem Power Query pentru moment și să revenim la fișierul nostru Handler.xlsx. Să adăugăm o nouă foaie goală și să facem pe ea un mic tabel „inteligent”, în singura celulă din care va fi scrisă calea completă către fișierul nostru de date sursă:

Pentru a crea un tabel inteligent dintr-un interval obișnuit, puteți utiliza comanda rapidă de la tastatură Ctrl+T sau butonul Formatați ca tabel fila Acasă (Acasă — Format ca tabel). Titlul coloanei (celula A1) poate fi absolut orice. De asemenea, rețineți că, pentru claritate, am dat un nume tabelului parametrii fila Constructor (Proiecta).

Copierea unei căi din Explorer sau chiar introducerea ei manuală nu este, desigur, deosebit de dificilă, dar cel mai bine este să minimizați factorul uman și să determinați calea, dacă este posibil, automat. Acest lucru poate fi implementat folosind funcția standard de foaie de lucru Excel CELULĂ (CELULA), care poate oferi o mulțime de informații utile despre celula specificată ca argument – ​​inclusiv calea către fișierul curent:

Dacă presupunem că fișierul de date sursă se află întotdeauna în același folder cu procesorul nostru, atunci calea de care avem nevoie poate fi formată prin următoarea formulă:

=LEFT(CELL(„nume fișier”);FIND(„[“;CELULA(„nume fișier”)-1)&”Top 100 de produse.xls”

sau în versiunea engleză:

=LEFT(CELL(«nume fișier»);FIND(«[«;CELL(«nume fișier»))-1)&»Топ-100 товаров.xls»

… unde este funcția LEVSIMV (STÂNGA) preia o bucată de text de la linkul complet până la paranteza pătrată de deschidere (adică calea către folderul curent), apoi numele și extensia fișierului nostru de date sursă sunt lipite de el.

Parametrizați calea în interogare

Ultima și cea mai importantă atingere rămâne – să scrieți calea către fișierul sursă în cerere Top 100 de produse.xls, referindu-se la celula A2 din tabelul „inteligent” creat de noi parametrii.

Pentru a face acest lucru, să revenim la interogarea Power Query și să o deschidem din nou Editor avansat fila Recenzie (Vizualizare — Editor avansat). În loc de o cale de șir de text între ghilimele „E:Rapoarte de vânzăriTop 100 products.xlsx” Să introducem următoarea structură:

Excel.CurrentWorkbook(){[Name="Settings"]}[Conținut]0 {}[Cale către datele sursă]

Să vedem în ce constă:

• Excel.CurrentWorkbook() este o funcție a limbajului M pentru accesarea conținutului fișierului curent
• {[Name="Settings"]}[Conținut] – acesta este un parametru de rafinare la funcția anterioară, indicând că dorim să obținem conținutul tabelului „inteligent” parametrii
• [Cale către datele sursă] este numele coloanei din tabel parametriila care ne referim
  
• 0 {} este numărul rândului din tabel parametriidin care vrem să luăm date. Capul nu contează și numerotarea începe de la zero, nu de la unu.

Asta e tot, de fapt.

Rămâne să dai clic pe finalizarea și verificați cum funcționează cererea noastră. Acum, atunci când trimiteți întregul folder cu ambele fișiere în interior către un alt PC, cererea va rămâne operațională și va determina calea către date în mod automat.

• Ce este Power Query și de ce este necesar atunci când lucrați în Microsoft Excel
• Cum să importați un fragment de text plutitor în Power Query
• Reproiectarea unui tabel încrucișat XNUMXD într-un tabel plat cu Power Query