Cum să utilizați Power Query în Excel: Ghidul complet

Power Query este un instrument puternic de business intelligence în Excel care vă permite să importați, să curățați și să transformați date în funcție de nevoile dvs. Dacă ați petrecut vreodată ore întregi curățând date (avem) sau v-ați luptat să îmbinați date din diverse surse (avem și noi), această funcție vă va ușura viața.

Cel mai bun mod de a învăța cum să utilizați Power Query în Excel este să efectuați unele sarcini comune, cum ar fi importarea datelor și utilizarea Editorului Power Query pentru a transforma tabelele rezultate și a combina datele.

Acest articol vă ajută să începeți arătând exemple clare de Power Query cu ghiduri pas cu pas . Prin stăpânirea Power Query, vă puteți ridica munca la următorul nivel.

Sunteţi gata? Să o facem.

Cuprins

• Cum să știți dacă puteți accesa Power Query
• Cum să importați date cu Power Query
• Cum să utilizați Editorul Power Query
  • Previzualizarea datelor
  • Proprietăți
• Cum să lucrați cu mai multe surse de date
• Cum să adăugați date cu Power Query
• Cum să îmbinați datele cu Power Query
• 5 moduri de a transforma datele cu Power Query
  • 1. Extinderea și condensarea datelor
  • 2. Sortarea datelor
  • 3. Formatarea datelor
  • 4. Adăugarea și eliminarea coloanelor
  • 5. Modificarea tipurilor de date
• Lucrul cu formule și limbajul M
  • Barul de formule
  • Scrierea și modificarea codului M
• Ce este sintaxa limbajului M?
• Cum se reîmprospătează conexiunile de date
• Cum se integrează Power Query cu modelul de date
• Cum să automatizezi cu Power Query și VBA
• Funcții avansate Power Query
• Colaborare și partajare
• Să încheiem asta

Cum să știți dacă puteți accesa Power Query

Înainte de a începe să utilizați Power Query, trebuie să vă asigurați că aveți o versiune de Excel care are această caracteristică.

Iată o scurtă prezentare generală a Power Query în funcție de versiune:

• Excel 2010 și Excel 2013: disponibil ca un program de completare care trebuie descărcat și instalat.

• Excel 2016: integrat în Excel ca „Get & Transform”.

• Excel 2019: performanță și funcționalitate îmbunătățite.

• Excel 2021 și Excel pentru Microsoft 365: integrare cu sursele de date din cloud.

• Excel pentru Mac: disponibil numai de la Microsoft 365 versiunea 16.69 și o versiune ulterioară.

Pentru a verifica dacă instrumentul este integrat în registrul de lucru Excel (2016 sau mai recent), accesați fila Date și căutați secțiunea „Obțineți și transformați date” din această imagine:

Dacă aveți Excel 2010 sau 2013 pe Windows, puteți descărca , instala și activa programul de completare Power Query.

Cum să importați date cu Power Query

Instrumentul Power Query vă permite să importați și să vă conectați la o gamă largă de surse de date:

• Fișiere, inclusiv formatele CSV, XML și JSON

• Adrese URL web sau puncte finale API

• Baze de date, inclusiv Access, SQL Server, Oracle și cele mai moderne baze de date

• Servicii Azure, fișiere SharePoint și fluxuri OData

Cea mai ușoară sursă de date pentru a începe este un simplu fișier CSV cu mai multe rânduri de date. Creați un fișier text și adăugați aceste patru nume și prenume, separate prin virgulă:

• Joe, Bloguri

• Anne, Ryan

• James, Stewart

• Mary, Brown

Asigurați-vă că salvați fișierul cu extensia .CSV. Fișierul dvs. va arăta astfel:

Pentru a încărca datele cu Power Query, urmați acești pași:

1. Accesați fila Date.

2. Faceți clic pe butonul „Obțineți fișiere” din fila Obțineți și transformați date.

3. Selectați fișierul eșantion.

Poate fi necesar să așteptați câteva secunde pentru ca instrumentul să atragă datele în panoul de previzualizare. Power Query utilizează destul de multe resurse de mașină chiar și pentru un import mic, așa că vă rugăm să aveți răbdare.

Sfat: închideți alte aplicații pentru a accelera performanța.

Power Query va afișa fereastra „Importare text/CSV” cu o previzualizare a datelor dvs.

Dacă datele par corecte, puteți continua să încărcați sau să transformați datele:

• butonul Încărcare va încărca datele direct în Excel.

• butonul Transform Data vă permite să modificați datele înainte de a le încărca.

Ați observat că fișierul eșantion nu avea antet? Dacă este încărcat direct în Excel, anteturile vor fi „Coloana 1” și „Coloana 2”.

În schimb, puteți extrage datele în editorul Power Query și puteți face unele modificări. Pentru a face acest lucru, faceți clic pe butonul „Transformare date”.

Se deschide acum Editorul Power Query, ceea ce ne duce la următoarea secțiune.

Cum să utilizați Editorul Power Query

În această secțiune, vom explora câteva aspecte esențiale ale lucrului cu Editorul Power Query.

Previzualizarea datelor

În zona de previzualizare a datelor, puteți vizualiza și interacționa cu datele dvs. importate. Puteți filtra rândurile, modifica tipurile de date coloane și remodela datele pentru a se potrivi nevoilor dvs.

Când ați terminat, Excel va încărca datele transformate într-o foaie de lucru.

Folosind fișierul exemplu, puteți edita numele coloanelor direct în acest panou.

Pentru a face acest lucru:

1. Faceți clic dreapta pe antetul coloanei.

2. Faceți clic pe „Redenumiți” în meniul derulant.

3. Introduceți numele preferate în casetele de antet.

Pe măsură ce faceți modificări, secțiunea „Pași aplicați” din partea dreaptă a panoului de previzualizare se actualizează pentru a reflecta fiecare transformare, permițându-vă să urmăriți și să modificați pașii de prelucrare a datelor după cum este necesar.

Proprietăți

Setarea proprietăților pentru interogările dvs. este esențială pentru organizarea și urmărirea muncii dvs.

Panoul Setări interogare este în partea dreaptă a ferestrei Power Query.

1. Faceți clic pe linkul „Toate proprietățile”.

2. Editați numele interogării pentru a fi descriptiv.

3. Adăugați o descriere pentru a oferi mai multe informații.

Este ușor să uiți pentru ce au fost configurate interogările de săptămâna trecută. În acest caz, numele interogării a fost schimbat și a fost adăugată o descriere care oferă scopul interogării.

Dacă doriți să aflați mai multe despre opțiunile disponibile, consultați această prezentare generală a Editorului Power Query .

Cum să lucrați cu mai multe surse de date

Editorul Power Query oferă două metode principale pentru combinarea datelor din mai multe surse: adăugarea și îmbinarea.

Adăugarea unește mesele pe verticală, stivuindu-le una peste alta. Îmbinarea vă permite să alăturați tabele pe baza coloanelor comune.

Pentru a vedea cum funcționează, creați un al doilea fișier CSV mic cu aceeași structură ca primul. De data aceasta, puneți două nume (primul și ultimul) în fișier:

• Joe, Bloguri

• Laura, Cane

Observați că unul dintre aceste nume este repetat din fișierul anterior.

Acum urmați acești pași pentru a trage datele în registrul de lucru Microsoft Excel.

1. Închideți fereastra Power Query Editor și salvați modificările.

2. Adăugați o nouă foaie de lucru.

3. Urmați pașii din secțiunea anterioară pentru a importa al doilea fișier.

4. Faceți clic pe butonul Transformare.

5. Redenumiți anteturile coloanei ca „Prenume” și „Nume”.

Panoul din dreapta din foaia de lucru Excel arată interogările și conexiunile curente. Ar trebui să vedeți acum două interogări în panou.

Cum să adăugați date cu Power Query

În această secțiune, veți adăuga cel de-al doilea fișier CSV exemplu la primul.

Urmați acești pași pentru a adăuga date:

1. Faceți clic dreapta pe primul tabel (Numele clienților) din panoul Interogări și conexiuni.

2. Alegeți „Editați” pentru a deschide tabelul în Editorul Power Query.

3. Faceți clic pe Adăugați interogări în secțiunea Combină.

4. Alegeți al doilea tabel din lista derulantă de tabele de atașat.

Rezultatul va fi două rânduri suplimentare în primul tabel.

Observați că se repetă un rând - așa funcționează operația de adăugare. Acesta adaugă datele dintr-un tabel în altul, indiferent dacă datele există deja.

Puteți elimina datele duplicate din Editorul Power Query urmând acești pași:

1. Faceți clic pe antetul coloanei „Prenumele”.

2. Țineți apăsată tasta „Ctrl” (sau tasta „Cmd” pe Mac).

3. Faceți clic pe ambele anteturi de coloană.

4. Faceți clic pe „Eliminați rândurile” în fila Acasă a panglicii Editor Power Query.

5. Selectați „Eliminați duplicatele” din meniul derulant.

Cum să îmbinați datele cu Power Query

Când îmbinați datele, trebuie să alegeți coloanele care se potrivesc între interogări.

Să presupunem că aveți două fișiere delimitate prin virgulă, în care unul conține numere de telefon ale clienților, iar celălalt conține adrese. Cele două fișiere arată astfel:

Urmați instrucțiunile din secțiunile anterioare pentru a încărca fiecare fișier în propria interogare de putere. Apoi puteți exersa îmbinarea tabelelor.

În cazul datelor noastre eșantion, ambele coloane (Prenumele și Numele) ar trebui să fie alese ca coloane potrivite. Țineți apăsată tasta Ctrl pentru a alege mai multe coloane.

Urmați acești pași:

1. Faceți clic dreapta pe primul tabel din panoul Interogări și conexiuni.

2. Alegeți „Editați” pentru a deschide tabelul în Editorul Power Query.

3. Selectați „Imbinare interogări” din grupul „Combinare”.

4. Alegeți al doilea tabel din lista derulantă de tabele.

5. Faceți clic pe prima coloană din primul tabel.

6. Țineți apăsată tasta Ctrl și faceți clic pe a doua coloană din primul tabel.

7. Țineți apăsată tasta Ctrl și faceți clic pe ambele coloane din al doilea tabel.

8. Păstrați operatorul de alăturare implicit ca „Left Outer”.

9. Faceți clic pe „OK” pentru a aplica modificările.

Operatorul de conectare „Left Outer” asigură eliminarea datelor duplicate.

În Editorul Power Query, veți vedea o nouă coloană numită „Tabel” sau „Tabel imbricat” care conține datele îmbinate din a doua interogare.

1. Faceți clic pe pictograma de extindere (două săgeți) din antetul acestei coloane.

2. Debifați caseta „Utilizați coloana originală ca prefix” (acest lucru evită numele lungi ale coloanelor).

3. Faceți clic pe „OK”.

4. Faceți clic pe „Închideți și încărcați”.

Tabelul îmbinat avea patru coloane, cu detaliile de telefon și adresa fuzionate. S-ar putea să mai aveți ceva de făcut.

De exemplu, poate doriți să editați numele tabelului (Merge1) și coloana de adresă din imaginea de mai jos.

5 moduri de a transforma datele cu Power Query

Transformarea datelor din fișierul dvs. Excel este ușoară cu Power Query, permițându-vă să curățați, să organizați și să manipulați datele în mod eficient.

Unele dintre cele mai comune moduri de a transforma datele sunt:

• Extinderea și condensarea datelor

• Sortarea datelor

• Formatarea datelor

• Adăugarea și eliminarea coloanelor

• Schimbarea tipurilor de date

1. Extinderea și condensarea datelor

Extinderea și condensarea datelor este importantă pentru a face seturile de date mai ușor de gestionat. Power Query vă permite să extindeți sau să restrângeți tabelele pentru o vizualizare mai organizată.

Secțiunea anterioară vă arată cum să vă extindeți prin includerea datelor din tabelele asociate.

De asemenea, îl puteți condensa pentru a include numai date rezumate bazate pe o agregare la alegere, cum ar fi suma, numărul sau procentajul.

2. Sortarea datelor

Pentru a sorta datele, puteți schimba rapid ordinea prin valori crescătoare sau descrescătoare.

Faceți clic pe săgeata în jos de lângă numele unei coloane și alegeți „Sort Ascendent” sau „Sort Descendent”.

3. Formatarea datelor

Fila Transformare din Editorul Power Query oferă o mulțime de opțiuni.

De exemplu, meniul derulant Format vă permite să schimbați toate datele dintr-o coloană sau întregul tabel în litere mari sau mici.

4. Adăugarea și eliminarea coloanelor

Power Query simplifică procesul de adăugare, ștergere și modificare a coloanelor.

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

De exemplu, poate fi necesar să convertiți punctele zecimale în procente sau să convertiți procentele bazate pe text în procente numerice pentru a calcula și compara mai ușor.

Lucrul cu formule și limbajul M

Când utilizați Power Query în Excel, veți întâlni limbajul M, care este inima creării de formule avansate, manipulării datelor și producerii de interogări puternice. În această secțiune, veți afla despre:

• barul de formule

• cod M

• elementele esenţiale ale sintaxei M Language

Barul de formule

Bara de formule din editorul Power Query din Excel este zona în care veți introduce formulele și codul M.

Pentru a-l folosi, accesați editorul Power Query selectând o interogare din panoul Interogări și conexiuni. Bara de formule este situată deasupra panoului de previzualizare a datelor.

Scrierea și modificarea codului M

Pentru a crea interogări versatile și puternice în Excel, va trebui să scrieți și să modificați limbajul Power Query cunoscut sub numele de cod M. Iată câteva sfaturi despre cum să începeți:

• Utilizați panoul Pași aplicați pentru a scrie codul pas cu pas, facilitând urmărirea și modificarea logicii interogării.

• Combinați codul M cu un tabel Excel, tabele pivot și surse de date externe, cum ar fi Azure și fișiere text, pentru a crea rapoarte dinamice și automate.

• Ajustați opțiunile de interogare pentru a vă rafina preferința de încărcare a datelor și pentru a îmbunătăți performanța.

Pe măsură ce vă familiarizați cu codul M, veți descoperi că puteți automatiza și optimiza multe procese, făcând analiza datelor în Excel mai eficientă și mai puternică.

Pentru a vedea codul din spatele unei interogări, urmați acești pași:

1. Deschideți interogarea în Editorul Power Query.

2. Faceți clic pe „Editor avansat” în fila Acasă.

Acesta este codul din spatele exemplului nostru de tabel îmbinat (al tău poate arăta diferit):

Ce este sintaxa limbajului M?

Înțelegerea sintaxei M Language este crucială pentru crearea formulelor Power Query eficiente. Țineți cont de următoarele puncte:

• Limba M este sensibilă la majuscule, așa că aveți grijă la scrierea cu majuscule.

• Utilizați paranteze drepte [] pentru a face referire la coloane, înregistrări și liste.

• Pentru a face referire la un pas anterior din interogarea dvs., utilizați numele variabilei acestuia (de exemplu, #”Numele pasului”).

• Ieșiți un pas de formulă de interogare cu instrucțiunea „in”.

Pe măsură ce câștigați experiență cu fluxul de limbaj Power Query , veți descoperi noi modalități de a vă transforma și analiza datele, făcând ca registrele de lucru Excel un bun și mai valoros.

Urmăriți videoclipul de mai jos, este timpul să scoateți peria de curățat și să vă curățați datele dezordonate.

Cum se reîmprospătează conexiunile de date

Una dintre caracteristicile cheie ale Power Query este capacitatea de a vă reîmprospăta cu ușurință o conexiune Power Query.

Când sursele de date au fost actualizate și aveți nevoie de cele mai recente informații din registrul de lucru, puteți reîmprospăta conexiunile de date în panoul Interogări și conexiuni.

Dacă nu vedeți panoul din dreapta foii de lucru, îl puteți deschide accesând tabelul de date și făcând clic pe „Interogări și conexiuni”.

Când panoul Interogări și conexiuni este deschis, listează toate interogările. Urmați acești pași:

1. Faceți clic dreapta pe interogarea pe care doriți să o reîmprospătați.

2. Selectați Reîmprospătare din meniul contextual.

Aceasta vă va actualiza registrul de lucru cu cele mai recente date din sursele de date conectate.

Cum se integrează Power Query cu modelul de date

Modelul de date Excel este o caracteristică puternică care vă permite să creați instrumente de business intelligence, cum ar fi tabele pivot, pe baza relațiilor de date.

Modelul de date este practic o bază de date ascunsă care deține datele importate și relațiile dintre tabele.

Face parte din caracteristica Power Pivot, care este încorporată în versiunile actuale de Excel. Este posibil să fie necesar să instalați Power Pivot ca supliment pentru a-l accesa în versiunile anterioare).

Puteți integra conexiunile de date Power Query cu modelul de date urmând acești pași:

1. Încărcați datele folosind Power Query, așa cum este descris în secțiunile anterioare.

2. În loc să îl încărcați direct în registrul de lucru, alegeți Încărcare la din meniul Încărcare.

3. Selectați caseta de selectare Adăugați aceste date la modelul de date din caseta de dialog Import date.

Cum să automatizezi cu Power Query și VBA

Cu integrarea VBA (Visual Basic for Applications), puteți crea conexiuni de date personalizate și puteți automatiza pașii de transformare a datelor în Power Query.

Vă puteți valorifica abilitățile VBA existente pentru a construi modele avansate de date prin încorporarea datelor de pe platforme precum Microsoft Access, SharePoint și Analysis Services.

Deși Power Query nu are suport nativ pentru VBA, puteți utiliza codul VBA pentru a deschide, reîmprospăta și manipula interogările Power Query în mod indirect.

Acest lucru vă permite să creați fluxuri de lucru puternice și automate de date și să vă simplificați procesul de analiză a datelor.

Iată un exemplu de cod care reîmprospătează o interogare de putere din registrul de lucru curent.

:

Dim wb ca registru de lucru, pq ca obiect

Set wb = ThisWorkbook

Set pq = wb.Queries(„Numele clienților”)

 pq.Reîmprospătare

Iată codul dintr-o macrocomandă Excel VBA:

Funcții avansate Power Query

Power Query din Excel oferă o multitudine de funcții avansate pentru a simplifica și îmbunătăți procesul de analiză a datelor. Învățând cum să utilizați eficient aceste funcții, vă puteți duce abilitățile de analiză a datelor la un nivel cu totul nou.

Instrumentele încorporate de analiză a datelor includ:

• Coloane condiționate pentru aplicarea logicii complexe transformărilor datelor dvs.

• Funcții de grupare și agregare pentru a rezuma datele dvs. într-un mod semnificativ.

• Operațiuni de pivotare și dezactivare pentru a vă remodela datele pentru o analiză mai ușoară.

Veți găsi multe dintre aceste caracteristici în fila Transformare din Editorul Power Query.

În plus, puteți conecta Power Query la instrumente de Business Intelligence, cum ar fi LuckyTemplates, pentru analize de date și opțiuni de vizualizare mai avansate .

Colaborare și partajare

Partajarea conexiunilor de date, a interogărilor și a transformărilor cu membrii echipei este esențială pentru o colaborare eficientă.

Puteți partaja un registru de lucru cu interogări salvându-l pe o unitate de rețea, SharePoint sau într-o altă locație de stocare partajată.

De asemenea, puteți exporta codul M în spatele unei singure interogări, urmând acești pași:

1. Editați interogarea în Editorul Power Query.

2. Faceți clic pe „Editor avansat” în fila Acasă.

3. Copiați întregul cod M în fereastra Editor avansat.

4. Deschideți un editor de text (de exemplu, Notepad) și inserați codul M copiat în editorul de text.

5. Salvați fișierul text și trimiteți-l colegului dvs.

Să încheiem asta

Cu o gamă largă de funcții și interfața sa ușor de utilizat, Power Query este un instrument esențial pentru utilizatorii Excel care doresc să lucreze eficient cu date externe.

Această caracteristică valoroasă ajută la eficientizarea procesului de creare a rapoartelor, de curățare a datelor și de efectuare de analize complexe pe tabelele de date. Folosind

În cele din urmă, Power Query vă economisește timp și crește eficiența atunci când lucrați cu fișiere Excel.