Cum se folosește Power Query în Excel?
Excel Power Query este utilizat pentru căutarea surselor de date, stabilirea conexiunilor cu sursele de date și apoi modelarea datelor în conformitate cu cerința noastră de analiză. Odată ce am terminat cu modelarea datelor conform nevoilor noastre, putem, de asemenea, să împărtășim constatările noastre și să creăm diverse rapoarte folosind mai multe interogări.
Pași
În principiu, există 4 pași, iar ordinea acestor 4 pași în Power Query este următoarea:
- Conectare: mai întâi ne conectăm la date, care pot fi undeva, în cloud, în serviciu sau local.
- Transformare: Al doilea pas ar fi modificarea formei datelor conform cerințelor utilizatorului.
- Combinație: în acest pas, efectuăm niște pași de transformare și agregare și combinăm date din ambele surse pentru a produce un raport combinat.
- Gestionați: aceasta combină și adaugă coloane într-o interogare cu coloane potrivite în alte interogări din registrul de lucru.
Există multe caracteristici super-puternice ale Excel Power Query.
Să presupunem că avem date de cumpărare pentru ultimii 15 ani în 180 de fișiere. Acum, managementul unei organizații ar necesita consolidarea numerelor înainte de a le analiza. Managementul poate lua oricare dintre următoarele metode:
- Ar deschide toate fișierele și le-ar copia și lipi într-un singur fișier.
- Pe de altă parte, ei pot folosi o soluție înțeleaptă, care este de a aplica formule, deoarece este predispus la erori.
Indiferent de metoda pe care o aleg, aceasta conține o mulțime de lucrări manuale și, după câteva luni, ar exista date noi privind vânzările pentru durata adăugată. Va trebui să facă din nou același exercițiu.
Cu toate acestea, Power Query îi poate ajuta să nu facă această muncă plictisitoare și repetitivă. Să înțelegem această interogare de excelență cu un exemplu.
Exemplu
Să presupunem că avem fișiere text într-un folder cu date despre vânzări și că dorim să obținem aceste date în fișierul nostru excel.
După cum putem vedea în imaginea de mai jos, avem două tipuri de fișiere în dosar, dar dorim să obținem doar fișierele text din fișierul Excel.
Pentru a face același lucru, pașii ar fi:
Pasul 1: Mai întâi, trebuie să obținem datele din Power Query, astfel încât să putem face modificările necesare în date pentru a le importa într-un fișier Excel.
Pentru a face același lucru, vom alege opțiunea „Din folder” din meniul „Din fișier” după ce vom face clic pe comanda „Obțineți date” din grupul „Obțineți și transformați” din fila „Date” .
Pasul 2: Selectați locația folderului navigând.
Faceți clic pe „OK”
Pasul 3: Se va deschide o casetă de dialog care conține lista pentru toate fișierele din folderul selectat cu anteturile coloanei ca „Conținut”, „Nume”, „Extensie”, „Data accesării”, „Data modificării”, „Data creării” „Atribute” și „Calea folderului”.
Există 3 opțiuni, și anume, Combină , Încarcă și Transformă date .
- Combinați : această opțiune este utilizată pentru a merge la un ecran unde putem alege ce date să combinăm. Pasul de editare este omis pentru această opțiune și nu ne oferă niciun control asupra fișierelor de combinat. Funcția de combinare necesită consolidarea fișierelor din folder, ceea ce poate duce la erori.
- Încărcare: Această opțiune va încărca tabelul așa cum este afișat mai sus în imagine în foaia de lucru Excel în loc de datele reale din fișiere.
- Transform Data: spre deosebire de comanda „Combine” , dacă folosim această comandă, atunci putem alege ce fișiere să combinăm, adică putem combina doar un singur tip de fișier (aceeași extensie).
Ca și în cazul nostru, dorim să combinăm numai fișiere text (.txt); vom alege comanda „Transform Data” .
Putem vedea „Pași aplicați” în partea dreaptă a ferestrei. Deocamdată, se face doar un singur pas, adică preluarea detaliilor fișierelor din folder.
Pasul 4: Există o coloană numită „Extensie”, unde putem vedea că valorile din coloană sunt scrise în ambele cazuri, adică majuscule și minuscule.
Cu toate acestea, trebuie să convertim toate valorile cu litere mici, deoarece filtrul diferențiază ambele. Pentru a face același lucru, trebuie să selectăm coloana și apoi să alegem „Minuscule” din meniul comenzii „Format” .
Pasul 5: Vom filtra datele folosind coloana „Extensie” pentru fișierele text.
Pasul 6: trebuie să combinăm acum datele pentru ambele fișiere text folosind prima coloană „Conținut”. Vom face clic pe pictograma plasată în partea dreaptă a numelui coloanei.
Pasul 7: O casetă de dialog cu titlul „Combină fișiere” se va deschide în cazul în care trebuie să selectăm delimitatorul ca „Tab” pentru fișierele text (fișiere cu „.txt” extensie) și putem alege baza pentru detectarea tipului de date. Și faceți clic pe „OK”.
După ce faceți clic pe „OK”, vom obține datele combinate ale fișierelor text în fereastra „Power Query” .
Putem modifica tipul de date al coloanelor după cum este necesar. Pentru coloana „Venituri” , vom schimba tipul de date în „Monedă”.
Putem vedea pașii aplicați datelor folosind o interogare de alimentare în partea dreaptă a ferestrei.
După efectuarea tuturor modificărilor necesare în date, putem încărca datele într-o foaie de lucru Excel folosind comanda „Închidere și încărcare în ” din grupul „Închidere” din fila „Acasă” .
Trebuie să alegem dacă dorim să încărcăm datele ca tabel sau conexiune. Apoi faceți clic pe „OK”.
Acum putem vedea datele ca un tabel în foaia de lucru.
Și panoul „Interogări de registru de lucru” din partea dreaptă, pe care îl putem utiliza pentru editarea, duplicarea, fuzionarea, adăugarea interogărilor și în multe alte scopuri.
Excel Power Query este foarte util deoarece putem vedea că 601.612 rânduri au fost încărcate în câteva minute.
Lucruri de amintit
- Power Query nu modifică datele sursă originale. În loc să schimbe datele sursă originale, înregistrează fiecare pas care este făcut de utilizator în timp ce conectează sau transformă datele și, odată ce utilizatorul finalizează modelarea datelor, preia setul de date rafinate și îl aduce în registrul de lucru.
- Power Query este sensibil la majuscule și minuscule.
- În timp ce consolidăm fișierele în folderul specificat, trebuie să ne asigurăm că folosind coloana „Extensie” și trebuie să excludem fișierele temporare (având extensia „.tmp” și numele acestor fișiere începe cu semnul „~”) ca Power Query poate importa și aceste fișiere.
