Explorând funcționalitatea Get & Transform din Excel

Rezumat

În această epocă a lacurilor de date și a bazelor de date la scară petabyte, este șocant cât de des primesc date sub formă de fișiere CSV, text și Excel. În timp ce analiza modernă se concentrează pe progresele de ultimă oră în algoritmii de învățare automată, munca de zi cu zi a analizei datelor este încă un proces manual de găsire, compilare și dispută de tipuri de date disparate.

Pentru analistul financiar, datele sosesc adesea ca o foaie de calcul Excel, dar la fel de des, este un dump de date într-un CSV sau o interogare într-o bază de date SQL. Uneori, datele sunt aranjate într-un aspect confuz sau nu au toate componentele necesare pentru analiză. Timpul petrecut cu curățarea acestor date este timp prețios pierdut pentru analist, dar uneori această sarcină este acceptată ca un rău necesar care trebuie tolerat.

Ce face Get & Transform?

O soluție la această problemă comună este de fapt destul de accesibilă: Excel și Power BI au un set întreg de instrumente de transformare a datelor de care puțini utilizatori sunt conștienți, numite Get & Transform (cunoscut anterior ca Power Query). Folosind funcționalitatea încorporată de extragere, transformare și încărcare (ETL) le permite analiștilor financiari să se conecteze fără probleme la sursele lor de date și să ajungă mai rapid la informații.

Pe măsură ce pregătim date pentru a le încărca în Excel sau Power BI, de obicei trebuie să efectuăm unele transformări ale datelor. Câteva exemple de manipulare a datelor ar include:

• Eliminarea coloanelor,
• Filtrarea datelor,
• Gruparea datelor,
• Pivotarea/dezactivarea datelor,
• Împărțirea șirurilor în subșiruri,
• Extragerea cuvintelor cheie din șiruri de caractere,
• Adăugarea rândurilor dintr-un alt tabel și
• Unirea tabelelor cu două dimensiuni.

În diagrama de mai jos, vedem că Get & Transform îndeplinește acest rol plictisitor de preprocesare a datelor înainte de a fi încărcate.

De ce ar trebui să folosiți Get & Transform?

De ce merită să înveți cum să folosești Get & Transform? Ei bine, când mă uit la ceea ce am folosit personal această funcționalitate, mi-a oferit un set maleabil de instrumente pentru:

• Încărcarea unui întreg folder de fișiere text într-un singur tabel de date
• Conversia fișierelor de contabilitate exportate într-un aspect digerabil
• Încărcarea directă a milioane de rânduri de vânzări în Power Pivot
• Gruparea datelor zilnice în rezultate lunare gestionabile înainte de a le importa în Excel
• Îmbinarea datelor dintr-un alt tabel prin alăturarea coloanelor care se potrivesc

În general, când primesc date noi, le voi explora folosind Get & Transform înainte de a le încărca în Power Pivot. Acest lucru îmi permite să văd ce transformări ar putea fi necesare și să efectuez rapid câteva pivotări și grupări asupra datelor pentru a formula un cadru pentru analiză. În multe cazuri, în această etapă, voi descoperi că am nevoie de mai multe date sau că există probleme de date. Folosind o platformă bazată pe Excel, pot repeta rapid cu sursa mea de date pentru a găsi aceste anomalii de date.

În cele din urmă, decizia de a rămâne în Excel sau de a muta analiza datelor pe o altă platformă va depinde de public și de repetabilitatea și distribuția analizei. Dacă clienții mei folosesc doar Excel, atunci aproape întotdeauna voi folosi Get & Transform pentru a încărca datele, Power Pivot pentru a efectua analiza și Excel pentru a produce tabelele pivot și diagramele. Pentru client, acest lucru se va simți perfect, deoarece totul este găzduit în Excel.

Totuși, dacă clientul meu:

1. Dorește să folosească un alt instrument de vizualizare,
2. Are mai mulți utilizatori care vor reîmprospăta datele sau
3. Trebuie să utilizeze modele de învățare automată,

Apoi voi folosi Get & Transform numai pentru explorarea inițială a datelor, apoi voi muta munca grea în R.

Cum să accesați Get & Transform în Excel sau Power BI

În versiunile anterioare de Excel, Power Query era un program de completare care putea fi instalat pentru a ajuta la funcțiile ETL. Cu toate acestea, în Excel 2016 și Power BI, aceste instrumente sunt mai strâns integrate. În Excel 2016, acestea pot fi accesate prin fila Date și apoi secțiunea Obținere și transformare date .

În Power BI, funcționalitatea există în fila Acasă , în secțiunea Date externe .

În acest articol, exemplele mele au loc în Power BI, dar interfața este aproape identică cu cea a lui Excel. Voi sublinia diferențele atunci când apar, astfel încât tutorialul ar trebui să aibă sens pentru ambele tipuri de utilizatori.

1. Încărcarea fișierelor CSV

Pentru a ajuta acest tutorial, am creat câteva exemple de date de vânzări pentru un retailer fictiv care vinde echipamente și îmbrăcăminte pentru exterior. În fiecare dintre aceste exemple, datele vor fi produse în moduri diferite pentru a demonstra metode realiste de depozitare a datelor.

Ca exemplu inițial, vom vedea datele prezentate ca un dump mare de date într-un fișier CSV. Factorul de complicare este că datele sunt prezentate cu mai multe coloane reprezentând diverse magazine. În mod ideal, am dori să importăm și să transformăm datele într-un aspect mai utilizabil.

Mai jos este o captură de ecran cu cum arată CSV-ul brut:

De ce am vrea să schimbăm asta? Pentru a profita de capabilitățile de relație care sunt posibile în aceste aplicații. Vom vedea această piesă mai departe în discuție.

Deocamdată, să presupunem că trebuie să vedem datele ca o structură „mai îngustă și mai înaltă”, mai degrabă decât una „mai largă și mai scurtă”. Primul pas este să încărcați CSV-ul; apoi, vom începe să „deplasăm” datele.

După cum puteți vedea, structura finală a datelor este mai restrânsă decât datele inițiale și mult mai lungă. Un alt aspect este că, pe măsură ce facem clic pe diferite acțiuni, instrumentul din partea dreaptă generează o listă de pași aplicați utilizați pentru a construi interogarea. Este important să înțelegeți că acest lucru se întâmplă în fundal, deoarece va fi revăzut mai târziu.

Get & Transform arată și se comportă în mod similar între Power BI și Excel în cea mai mare parte. Cu toate acestea, în Excel, după ce faceți clic pe Închidere și încărcare , există o solicitare suplimentară. În figura de mai jos, putem comuta între dacă dorim să încărcăm datele în:

1. Un tabel în Excel,
2. Un tabel pivot creat pe baza datelor,
3. Un PivotChart creat pe baza datelor sau
4. „Creați doar o conexiune.”

În plus, ni se oferă și opțiunea de a adăuga sau nu aceste date la modelul de date . Bifarea acestei casete încarcă datele într-un tabel Power Pivot. Dacă urmează să analizăm datele în Power Pivot, vă sfătuiesc să alegeți doar Creați o conexiune și apoi să vă asigurați că opțiunea Adăugați aceste date la modelul de date este selectată. Dacă datele se încadrează în limita de rânduri Excel și preferăm să ne efectuăm analiza în Excel, atunci alegeți doar Tabel .

În clipul următor, vom vedea că motivul pentru care am formatat datele să fie lungi și slabe este pentru a putea analiza vânzările nu doar pe magazin, ci și pe regiune și stat. Pentru a îndeplini această sarcină, vom importa un tabel care mapează fiecare magazin într-o regiune și stat. Vom vedea mai jos că putem crea rapid rapoarte care să arate vânzările de către aceste grupări diferite.

Vă puteți imagina cum acest tip de capacitate pentru transformarea datelor în Excel sau Power BI poate fi aplicat cu putere în orice caz în care avem grupări dinamice de date, cum ar fi:

• Cumularea datelor zilnice în săptămâni, luni și trimestre;
• Gruparea personalului de vânzări pe departamente și regiuni; sau
• Maparea SKU-urilor la tipurile de produse.

În timp ce acest articol abordează CSV și alte fișiere Excel, Get & Transform abordează o gamă largă de tipuri de date. Odată ce o interogare este creată, aceasta poate fi reîmprospătată în timp pe măsură ce datele se modifică.

2. Manipularea șirurilor de text

Pentru a demonstra capacitatea Get & Transform de a manipula șiruri, am creat un alt set de date care imită un fișier text care arată tranzacțiile contabile din registrul general (GL) al unei firme.

Observați cum apar numărul și numele contului în același șir? În Power BI, putem analiza fără efort numărul și numele contului în câmpuri separate.

În acest videoclip, puteți vedea că, după ce am împărțit coloana, instrumentul a ghicit că noua parte din stânga a câmpului Cont ar trebui să fie un număr și creează un pas „Tip modificat 1”. Deoarece în cele din urmă dorim acest câmp ca șir, putem continua și șterge pasul manual sub pașii aplicați.

Apoi, luăm aceleași date și creăm un plan de conturi cu mapări la categoriile de conturi.

De ce am parcurge toți acești pași pentru a mapa câteva numere de cont? Un adevărat registru general poate fi sute sau chiar mii de conturi. Această interogare de mapare rapidă, așa cum am arătat, s-ar scala la acel nivel fără nicio muncă suplimentară.

3. Lucrul cu diferite surse de date

Get & Transform acceptă multe surse de date diferite. Deși nu este o listă exhaustivă, mai jos sunt câteva exemple:

Personal, am încercat doar aproximativ jumătate din conexiunile din lista de mai sus. Fiecare dintre conectorii pe care i-am folosit a fost destul de robust; Am ajuns de la date brute la perspective fără o muncă împovărătoare. La fel de important, servește ca un validator între surse disparate de date, asigurând că rezultatele finale au un nivel normalizat de control al calității.

4. Personalizarea codului cu limbajul M

În fundal, Get & Transform generează cod de fiecare dată când facem clic pe un buton din instrument sau facem o selecție. Mai jos este un exemplu despre cum ați accesa codul pentru interogarea de mapare a contului creată de noi:

Codul folosește un limbaj funcțional numit M, care se generează automat pentru cazurile de utilizare de bază. Cu toate acestea, pentru o dispută mai complicată a datelor, putem edita și scrie propriul cod. În cele mai multe cazuri, voi face doar modificări minore acestui cod. În transformările mai complicate, pot scrie cea mai mare parte a codului de la zero pentru a pune în scenă tabelele temporare sau pentru a efectua îmbinări mai complicate.

Limitele Get & Transform

Excel tinde să-și atingă limitele atunci când încercați să exportați mai mult de un milion de rânduri. În cazurile în care am transformat milioane de rânduri cu Get & Transform, singura modalitate de a expedia rânduri negrupate este prin hack-uri sau soluții obositoare. De asemenea, am descoperit că interogările Get & Transform pot fi instabile pentru a fi implementate pentru mai mulți utilizatori, mai ales dacă utilizați mai multe surse de date și alăturari. În acele cazuri, voi folosi întotdeauna R pentru a implementa discuțiile de date duplicabile. În cele din urmă, Excel nu este creat pentru modelarea datelor mai avansate. Puteți efectua regresii liniare destul de repede, dar dincolo de aceasta, va trebui să utilizați o platformă mai riguroasă.

Acestea fiind spuse, constat că Excel este ceea ce majoritatea clienților mei se simt cel mai bine. Excel este încă cel mai important instrument din arsenalul unui analist financiar. Prin încorporarea funcționalității Get & Transform, Excel și Power BI devin și mai puternice prin gama de surse de date pe care le pot accepta.