Power Query este integrat în versiunile Windows ale Office 365, Excel 2016, Excel 2019 și este disponibil ca descărcare gratuită în versiunile Windows ale Excel 2010 și Excel 2013. Instrumentul este conceput pentru a extrage, transforma și încărca date în Excel dintr-un varietate de surse. Cea mai bună parte: Power Query vă amintește pașii și îi va reda atunci când doriți să reîmprospătați datele. Aceasta înseamnă că puteți curăța datele în Ziua 1 în 80% din timpul normal și puteți curăța datele din Zilele 2 până la 400 făcând clic pe Reîmprospătare.
Spun asta despre o mulțime de noi caracteristici Excel, dar aceasta este într-adevăr cea mai bună caracteristică pentru a atinge Excel în 20 de ani.
Povestesc în seminariile mele live despre cum a fost inventată Power Query ca o cârjă pentru clienții SQL Server Analysis Services care au fost obligați să folosească Excel pentru a accesa Power Pivot. Dar Power Query a continuat să se îmbunătățească și fiecare persoană care folosește Excel ar trebui să-și aleagă timpul pentru a învăța Power Query.
Obțineți interogare Power
Este posibil să aveți deja Power Query. Se află în grupul Obțineți și transformați din fila Date.
Dar dacă vă aflați în Excel 2010 sau Excel 2013, accesați Internetul și căutați Descărcare Power Query. Comenzile dvs. Power Query vor apărea într-o filă dedicată Power Query din panglică.
Curățați datele prima dată în Power Query
Pentru a vă oferi un exemplu al unora dintre aspectele extraordinare ale Power Query, spuneți că primiți fișierul prezentat mai jos în fiecare zi. Coloana A nu este completată. Trimestrele trec peste loc în loc să treacă pe pagină.
Pentru a începe, salvați acel registru de lucru pe hard disk. Puneți-l într-un loc previzibil cu un nume pe care îl veți folosi în fiecare zi pentru fișierul respectiv.
În Excel, selectați Obțineți date, Din fișier, Din registru de lucru.
Navigați la registrul de lucru. În panoul Preview, faceți clic pe Sheet1. În loc să faceți clic pe Încărcați, faceți clic pe Editare. Acum vedeți registrul de lucru într-o rețea ușor diferită - rețeaua Power Query.
Acum trebuie să remediați toate celulele goale din coloana A. Dacă ar fi să faceți acest lucru în interfața cu utilizatorul Excel, secvența de comandă dificilă este Acasă, Găsiți și Selectați, Mergeți la Special, Blanks, Egal, Săgeată sus, Ctrl + Enter .
În Power Query, selectați Transform, Fill, Down.
Toate valorile nule sunt înlocuite cu valoarea de mai sus. Cu Power Query, sunt necesare trei clicuri în loc de șapte.
Următoarea problemă: sferturile trec în loc să scadă. În Excel, puteți remedia acest lucru cu un tabel pivot cu mai multe domenii de consolidare. Aceasta necesită 12 pași și peste 23 de clicuri.
În Power Query selectați cele două coloane care nu sunt sferturi. Deschideți meniul derulant Unpivot Columns din fila Transformare și alegeți Unpivot Other Columns, așa cum se arată mai jos.
Faceți clic dreapta pe coloana Atribut nou creată și redenumiți-o Trimestrial în loc de Atribut. Douăzeci de clicuri în Excel devin cinci clicuri în Power Query.
Acum, pentru a fi corect, nu fiecare etapă de curățare este mai scurtă în Power Query decât în Excel. Eliminarea unei coloane înseamnă în continuare clic dreapta pe o coloană și alegerea Elimină coloana. Dar, sincer să fiu, povestea de aici nu este despre economiile de timp din Ziua 1.
Dar așteptați: interogarea de putere îți amintește toți pașii tăi
Uită-te în partea dreaptă a ferestrei Power Query. Există o listă numită Etape aplicate. Este o pistă de audit instantanee a tuturor pașilor dvs. Faceți clic pe orice pictogramă roată pentru a vă schimba opțiunile în acel pas și pentru ca modificările să cadă în etapele viitoare. Faceți clic pe orice pas pentru a vedea cum arătau datele înainte de acel pas.
Când ați terminat de curățat datele, faceți clic pe Închidere și încărcare așa cum se arată mai jos.
Bacsis
Dacă datele dvs. depășesc 1.048.576 de rânduri, puteți utiliza meniul derulant Închidere și încărcare pentru a încărca datele direct în Power Pivot Data Model, care poate găzdui 995 de milioane de rânduri dacă aveți suficientă memorie instalată pe aparat.
În câteva secunde, datele dvs. transformate vor apărea în Excel. Minunat.
Răsplata: curățați datele mâine cu un singur clic
Dar, din nou, povestea Power Query nu se referă la economiile de timp din Ziua 1. Când selectați datele returnate de Power Query, apare un panou Interogări și conexiuni în partea dreaptă a Excelului, iar pe acesta este un buton Reîmprospătare. (Avem nevoie de un buton Editați aici, dar pentru că nu există unul, trebuie să faceți clic dreapta pe interogarea originală pentru a vizualiza sau a face modificări la interogarea inițială).
Este distractiv să curăț datele în ziua 1. Îmi place să fac ceva nou. Dar când managerul meu vede raportul rezultat și spune „Frumos. Poți face asta în fiecare zi? ” Devin rapid să urăsc plictiseala de a curăța același set de date în fiecare zi.
Deci, pentru a demonstra Ziua 400 de curățare a datelor, am schimbat complet fișierul original. Produse noi, clienți noi, numere mai mici, mai multe rânduri, așa cum se arată mai jos. Salvez această nouă versiune a fișierului în aceeași cale și cu același nume de fișier ca și fișierul original.
Dacă deschid registrul de lucru pentru interogări și fac clic pe Reîmprospătare, în câteva secunde, Power Query raportează 92 de rânduri în loc de 68 de rânduri.
Curățarea datelor în Ziua 2, Ziua 3, Ziua, 4, … Ziua 400, … Ziua Infinity necesită acum două clicuri.
Acest singur exemplu zgârie doar suprafața Power Query. Dacă petreceți două ore cu cartea, M este pentru (Data) Monkey de Ken Puls și Miguel Escobar, veți afla despre alte caracteristici, cum ar fi acestea:
- Combinarea tuturor fișierelor Excel sau CSV dintr-un folder într-o singură grilă Excel
- Conversia unei celule cu Apple; Banana; Cireș; Marar; Vinete la cinci rânduri în Excel
- Efectuarea unui VLOOKUP într-un registru de lucru de căutare pe măsură ce introduceți date în Power Query
- Efectuarea unei singure interogări într-o funcție care poate fi aplicată la fiecare rând din Excel
Pentru o descriere completă a Power Query, consultați M Is for (Data) Monkey de Ken Puls și Miguel Escobar. Până la sfârșitul anului 2019, a doua ediție retitolată, Master Your Data, va fi disponibilă.
Mulțumim lui Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser și Colin Michael pentru nominalizarea Power Query.
