Power Query: Ghidul definitiv pentru începători din 2021

bună, Bine ați venit la Ghidul pentru începători pentru Power Query by UpSlide.
v-ați întrebat vreodată ce este Power Query? Cum funcționează? Dacă da, sunteți în locul potrivit!
Pauline, expertul nostru Power Query De La Finance 3.1, ne-a oferit toate cele mai bune sfaturi și sfaturi pentru a scrie acest articol.
aici veți învăța elementele de bază ale software-ului și chiar veți putea practica un pic! Ești gata?

dacă nu aveți timp să o citiți acum, descărcați versiunea PDF și bucurați-vă mai târziu!

ce este Power Query? Când ar trebui să-l folosesc?

Power Query este un program de completare Excel care permite utilizatorilor să importe, să curețe și să consolideze datele. Puteți utiliza Power Query atunci când:

• procesarea unor cantități mari de date (în cazul în care Excel este limitat la un milion de rânduri)
• importul de date din diferite surse (de exemplu, Excel, CSV, SharePoint, SQL, și Salesforce)
• manipularea, curățarea și combinarea mai multor tabele, fișiere sau chiar foldere!

Power Query este un instrument ușor de utilizat și puternic chiar și pentru începători, deoarece nu aveți nevoie de experiență de codificare pentru a începe. Mai mult, nu modifică sursa de date originală. Rezultatul este ca un instantaneu al datelor remodelate aduse în Excel. Ce-i așa grozav la asta? Ei bine, deoarece datele sursă nu sunt importate direct, nu vă îngreunează fișierul Excel (adică nu încetinește computerul!).

pentru a rezuma, Power Query este un instrument de analiză a datelor care face procesele ETL (Extract-Transform-Load) atât mai simple, cât și mai eficiente.

care este diferența dintre Power Query și Power Pivot?

Power Pivot este adesea asociat cu Power Query, deoarece este un program de completare Microsoft Office care facilitează analiza datelor. Interesant, totuși, aceste două instrumente pot funcționa în tandem unul cu celălalt! Power Pivot este pur și simplu următorul pas al transformării datelor: permite utilizatorilor să creeze modele de date din tabele curățate anterior de Power Query.

în timp ce similare, Power Pivot este mai mult-atât de utilizat pentru analiza datelor, calcule legate de timp și generarea de generare link-uri. Pur și simplu a spus:

• Power Query vă permite să consolidați și să reprocesați automat fișierele (adică standardizarea proceselor de raportare sau consolidarea mai multor fișiere cu același format).
• Power Pivot vă permite să analizați baze de date de mai multe milioane de linii prin extragerea informațiilor agregate în conformitate cu regulile stabilite de afacerea dvs.

care este diferența dintre Power Query și Power BI?

tehnologia de interogare de bază a Power Query este, de asemenea, inclusă în Power BI.

interfața software-ului desktop Power BI vă permite să beneficiați de cele trei tehnologii (Power Query, Power Pivot și Power View). Cu Power BI, puteți face toți pașii lanțului de analiză a datelor, de la extragerea datelor până la crearea de imagini pe un tablou de bord interactiv pe care îl puteți partaja și analiza.

> citiți articolul nostru despre cum să exportați Power BI în Excel

de ce este Power Query O alternativă excelentă la Excel VBA?

Power Query poate înlocui cu ușurință VBA (Visual Basic pentru aplicații), deoarece vă permite să:

• procesați tabelele pur și simplu făcând clic pe butoane. Nu sunt necesare abilități de codificare!
• Vizualizați operațiile pas cu pas fără a rula o singură macrocomandă.
• editați cu ușurință ordinea pașilor diferiților pași prin glisare și fixare! De asemenea, puteți adăuga și șterge pași inutili printr-un simplu clic.

(a se vedea partea 2 – Secțiunea3: cum să începeți cu Power Query?)

cu toate acestea Power Query nu poate înlocui VBA atunci când vine vorba de interacțiunea cu interfața Excel, automatizarea proceselor, generarea de fișiere noi, trimiterea de e-mailuri etc.

este Microsoft Power Query gratuit? Există o interogare de putere pentru Mac?

da. Power Query este o extensie gratuită a Excel. Acesta este un alt beneficiu principal al suplimentului.
(citiți următoarea întrebare pentru a afla cum îl puteți obține pe desktopul Excel)

Power Query pentru Mac? Da, știri mari: Query editor este acum compatibil cu Excel pentru Mac! Dar integrarea este destul de limitată în acest moment, deoarece puteți schimba doar calea fișierului și reîmprospăta interogările existente.

Partea 2: Începeți cu Power Query!

cum se descarcă Power Query?

Power Query este disponibil pentru Excel din 2010.

Iată cum să adăugați Power Query în funcție de versiunea Excel*:

• vești bune, nu este necesar să descărcați Power Query pentru Excel 2019, 2016 sau în Office 365. O puteți găsi în panglica” date „sub butonul”Obțineți & transformare”.
• pentru Excel 2013 și 2010, trebuie să descărcați programul de completare Power Query. Acesta va apărea într-o nouă filă dedicată numită „Power Query” (în Excel 2010 trebuie să aveți un plus profesional cu licență Software Assurance).

*Iată cum să verificați versiunea Excel:

accesați fișierul > cont > despre Excel. Apoi va apărea o fereastră. Citiți primele două numere din paranteze. În exemplul de mai jos, versiunea Excel este 2016.

pentru a afla mai multe, iată un alt articol detaliat despre cum să instalați și să activați Power Query în Excel.

Cum activez Power Query în Excel?

în funcție de versiunea Excel (a se vedea întrebarea de mai sus: unde este Power Query în Excel?) trebuie doar să faceți clic pe butonul numit „obțineți date” în secțiunea „Obțineți & transformare” din fila „Date” pentru Excel 2016 și nu numai. Pentru versiunile Excel înainte de 2013 incluse, pur și simplu accesați panglica Power Query.

rețineți că atunci când introduceți interfața Power Query, nu veți mai putea interacționa cu Excel. Dacă trebuie să reveniți la Excel, va trebui să închideți editorul Power Query.

cum funcționează Power Query?

în Excel, aveți acces la următoarele funcționalități numai atunci când faceți clic pe „interogări & conexiuni”:

panglică de date: pentru a căuta surse de date (panglica”Power Query” pentru Excel 2010 și 2013).

interogări & Panoul conexiuni: pentru a arunca o privire rapidă asupra tuturor conexiunilor (interogărilor) pe care le-ați făcut.

odată ce ați lansat Power Query, aterizați pe editorul Power Query, care este alcătuit din 4 elemente distincte:

panglică principală: vă permite să creați interogări de la zero datorită butoanelor sale (dacă sunteți familiarizat cu limba M, puteți accesa și editorul avansat pentru a le modifica manual).

interogări: pentru a urmări toate conexiunile la sursele de date și fluxuri.

previzualizare tabel de date: pentru a vizualiza rezultatul datelor după un anumit pas.

Setări interogare (pași aplicați): pentru a naviga prin toți pașii pe care i-ați făcut. Power Query înregistrează automat toate transformările efectuate.

• în secțiunea 4, veți afla cum funcționează practic Power Query transformând un tabel de date.
• în secțiunea 5, devine și mai interesant, deoarece veți descoperi cum să procedați date din diverse surse și să îmbinați interogările.

cum să începeți cu Power Query?

Imaginați-vă următoarea situație:

sunteți controlorul financiar al unei firme de tehnologie care vinde computere și smartphone-uri. În prezent, operați în 3 țări: Franța, Marea Britanie și China. În fiecare lună, elaborați un raport consolidat pentru managerul dvs., cu indicatori de performanță, cum ar fi veniturile totale, veniturile pe țară sau veniturile pe produs. Pentru a face acest lucru, fiecare manager de țară – Dennis, Douglas și Karen – vă trimite un fișier.

problemă: fiecare fișier listează vânzările în propria denumire a țării. Este prea mult timp să le convertiți în euro, apoi să le consolidați în raportul final.

ce se întâmplă dacă ați putea automatiza procesul și calcula raportul în câteva minute folosind doar Power Query?

nu ar fi minunat? Ei bine, cu Power Query, poți! Să începem prin a pregăti primul dvs. tabel

în primul rând, aveți nevoie de date. Descărcați exemplul nostru Power Query și salvați-l pe serverul local.

apoi, urmați acești pași simpli:

• dezarhivați fișierul și salvați-l pe serverul local.
• acum deschideți o nouă foaie de lucru în Excel și accesați fila Date > obțineți date > din fișier > din registrul de lucru, așa cum se arată mai jos.

(dacă aveți versiunea Excel 2013 sau 2010, pur și simplu accesați fila Power Query)

• accesați calea în care ați salvat fișierele dezarhivate pe care tocmai le-ați descărcat și selectați fișierul Excel numit „rate de schimb”.

• Faceți clic pe Transformare date.

este posibil să fi observat, de asemenea, că unii pași aplicați au apărut deja în Panoul de setări de interogare. Acestea se fac automat.

acum să exersăm un pic, începând prin curățarea tabelului de date:

• eliminați primele 3 rânduri, mergeți la Eliminați rândurile > eliminați rândurile de sus și tastați „3”. Veți vedea instantaneu transformarea în previzualizarea tabelului de date, precum și în pașii aplicați (Setări de interogare).

• scapa de rânduri goale: eliminați rândurile > eliminați rândurile goale (așa cum se arată mai jos).

• promovați primul rând ca antet: faceți clic pe „Utilizați primul rând ca anteturi”. După cum probabil ați observat, Power Query a adăugat automat un pas pentru a schimba tipul unor date (valorile din prima coloană sunt acum formatate ca date).

• eliminați coloanele goale: faceți clic pe „Alegeți coloanele” și debifați coloanele 7, 8, 9 și 10.

te descurci bine! Încă trei pași și veți fi pregătiți!

• Unpivot coloanele: Selectați coloana” curs de schimb”, apoi în fila transformare, accesați Unpivot columns > Unpivot Other columns (puteți selecta și celelalte cinci coloane și selectați”Unpivot columns”).

• redenumiți coloanele: faceți clic pe celulele antetului, introduceți „data” în prima, „moneda” în a doua și „rata” în a treia.

• aplicați un filtru: la fel ca în Excel, faceți clic pe meniul derulant al coloanei „data” și selectați „Dec-18”.

• în fila Acasă, faceți clic pe închidere și încărcare pentru a încărca tabelul final într-o foaie nouă.

Bravo! Ai modelat primul tabel de date.

să mergem mai departe cu Power Query

(re)imaginați-vă următoarea situație:

datorită abilităților dvs. de interogare a puterii, sunteți acum un controlor financiar SENIOR al firmei de tehnologie. Acum, trebuie să consolidați datele din decembrie 2018 pe care vi le-au trimis managerii de țară.

problemă: fiecare fișier listează vânzările în propria denumire a țării. Este nevoie de timp pentru a le converti în euro, chiar mai mult pentru a le consolida apoi în raportul final.

ce se întâmplă dacă ați putea automatiza procesul și calcula raportul în câteva minute numai folosind Power Query?

acum vom vedea cum să consolidăm diferite surse de date și să le îmbinăm cu tabelul pe care l-am pregătit în secțiunea anterioară.

în primul rând, să importăm întregul dosar, inclusiv cele trei fișiere sursă diferite:

• dacă sunteți încă în Power Query, accesați folderul sursă nouă > fișier >.

(Dacă înapoi pe Excel, du – te pentru a obține date > din dosar)

• selectați folderul” Sales_201812 ” pe care l-ați descărcat și salvat anterior. După aceea, faceți clic pe OK.

ar trebui să apară o fereastră nouă.

apoi, trebuie să îmbinați interogările:

• selectați” combinați & Editare ” din meniul derulant (partea de jos a ferestrei noi).

• selectați tabelul de exemplu „Dec-18” (rețineți că toate foile trebuie denumite în același mod).

• puteți verifica în prima coloană că toate sursele sunt incluse (faceți clic pe meniul derulant), apoi faceți clic pe OK.

acum sunteți în editorul Power Query.

• mergeți la fila transformare > înlocuiți valorile.

• acum, înlocuiți ” XV „cu”EUR”.

• accesați fila Acasă > combinați interogările.

apare o fereastră nouă:

1. selectați coloana „valută”.
2. selectați fișierul „rate de schimb” din meniul derulant.
3. selectați coloana „valută”.
4. Faceți clic pe OK.

• acum, trebuie să afișați datele cursului de schimb pe care tocmai le-ați recuperat:
  1. selectați coloana „curs de schimb”.
  2. Faceți clic pe butonul Expand.
  3. verificați numai „Rata”.
  4. debifați „Utilizați originalul …”.
  5. Faceți clic pe OK.

Felicitări! Tocmai ați încărcat o altă sursă de date, ați fuzionat interogări și ați recuperat cursul de schimb corespunzător. Nu mai VLOOKUP sau INDEX + meci!

pentru a termina, vom crea o coloană „preț unitar EUR” pentru a converti toate prețurile în euro:

• accesați fila Adăugare coloană > coloană personalizată.

• apare o fereastră nouă. Să scriem formula prețului în euro:
  1. denumiți-l „preț unitar EUR”.
  2. faceți dublu clic pe „preț unitar moneda locală” (sau selectați-l și apoi faceți clic pe Inserare).
  3. Tip „/”.
  4. faceți dublu clic pe „rata”.
  5. Faceți clic pe OK.

• odată ce coloana apare, faceți clic pe simbolul tipului său (chiar înainte de nume) și selectați „număr zecimal”.

trebuie să facem același lucru pentru a genera coloana ” Venituri:

• accesați fila Adăugare coloană > coloană personalizată.
• apare o fereastră nouă. Să tastăm Formula veniturilor:
  1. denumiți-o „Venituri”.
  2. faceți dublu clic pe „preț unitar EUR” (sau selectați-l și apoi faceți clic pe Inserare).
  3. Tip „*”.
  4. faceți dublu clic pe „cantitate”.
  5. Faceți clic pe OK.

• din nou, schimbați tipul coloanei la „număr zecimal”.

pentru a termina, să-l exportăm într-un nou tabel de date în Excel!

• mergeți la fila Acasă > închideți & încărcați > închideți & încărcați.

acum Puteți beneficia pe deplin de caracteristicile Excel pentru a analiza datele, a face un tabel pivot etc.

Uau! Dacă ați ajuns până la capăt, Felicitări! Acum știți cum funcționează Power Query.

acum e rândul tău!

vreau să vi-l predau: ați înțeles toate întrebările de mai sus despre Power Query?
ați descărcat fișierele noastre de tutorial și ați urmat instrucțiunile de mai sus pentru a le încerca pe cont propriu?
anunțați-ne lăsând un comentariu rapid mai jos chiar acum!

pentru a afla mai multe despre Power Query: