Introducere: Ce înseamnă tabele „stricate”?

Foarte des ni se întâmplă să primim tabele Excel în care datele sunt inadecvat completate sau formatate.

De pildă, în acest tabel coloana Nume Prenume stochează mai multe tipuri de litere, atât majuscule cât și minuscule. Tot pe coloana Nume avem o serie de spații inutile, care apar la începutul sau la sfârșitul textelor. Va trebui să eliminăm aceste spații în mod automat.

Coloana CNP conține valorile ca NUMERE și astfel, din păcate, nu mai putem să facem diferite transformari pe această coloană, cum ar fi să extragem anul, luna, ziua din CNP…

Aici, pe coloana ANUL, apar celule unite, adică MERGE, și știți foarte bine că niciodată nu trebuie să avem în interiorul unui tabel astfel de date.

De asemenea, coloana Adresa trebuie spartă în două coloane separate, una cu Județul și cealaltă cu Orașul, pentru că altfel nu o să putem lucra serios în Excel cu Județele sau Orașele.

Cum facem toate aceste modificări în Excel? Vă spun eu cum: pentru a formata corect întreg acest tabel în Excel, am avea nevoie de minim o oră și de folosirea a cel puțin 10 formule și funcții. Mai mult, în momentul în care o să primim alte date la fel de prost formatate și completate, va trebuie să o luăm iar și iar de la capăt.

Vă prezint instrumentul POWER QUERY, ce ne permite să facem toate aceste transformări și curățări de date doar cu ajutorul mouse-ului și câteva click-uri. Mai mult, le facem o singură dată, pentru că, odată facute, ele se țin minte și putem să le reaplicăm de câte ori dorim.

1. Transformarea literelor în majuscule, minuscule

Prima operație pe care ne-am propus-o este de a transforma tipurile de litere de pe coloana Nume Prenume. În primul rând, instrumentul Power Query (care știe să facă toate aceste transformări), îl găsim în panglica Data, apoi din Get Data alegem Launch Power Query Editor.

Nu o să apăs acest buton acum. Pentru că doresc să împușc doi iepuri dintr-un foc și anume: să și lansez editorul Power Query și, în același timp, să și import în el datele din tabelul pe care doresc să-l transform.

Așa încât apăs butonul From Table/Range.

După ce apăsăm OK la fereastra Create Table, se va deschide interfața Power Query în care avem deja importate datele din tabelul nostru.

Am promis un singur click pentru transformarea literelor. Selectăm coloana Nume Prenume și apoi, din Transform, alegem Format apoi Capitalize Each Word. Iată cum avem acum pe coloana Nume Prenume toate textele cu prima literă mare, restul mici, în fiecare cuvânt.

Cum o să ducem toate aceste transformări din nou în Excel, am să vă arăt spre finalul acestui video după ce realizez toate transformările. Deocamdată m-am ținut de cuvânt și am transformat coloane Nume Prenume cu un singur click în Power Query. Dacă, în Excel, aș fi fost nevoit să fac această transformare, trebuia să folosesc o funcție (funcția Proper), apoi o formulă scrisă într-o coloană calculată, ca, într-un final, să fac un Copy/Paste special as values… În Power Query am făcut în 3 secunde cu un singur click ceea ce aș fi făcut în cateva minute cu formule în Excel.

2. Eliminarea spațiilor inutile

Acum ne dorim să scoatem toate spațiile adiționale de la începutul și de la sfârșitul textelor de pe coloana Nume Prenume. Iată cât de simplu, selectăm coloana ce conține spațiile inutile, în cazul nostru coloana Nume Prenume și tot in Transform/Format există opțiunea Trim. Și gata!

Observați că cele două transformări făcute până acum au fost înregistrate de Power Query în acești doi pași: Capitalize Each Word și Trimmed Text.

3. Conversia coloanelor la alte tipuri de date

De multe ori, dorim să schimbăm felul în care sunt stocate valorile pe coloane. De pildă, pentru coloana CNP nu ne convine că datele sunt stocate ca și numere. Regula este să stocăm ca numere doar acele valori cu care FACEM calcule matematice. Când am făcut ultima dată adunări și împărțiri cu CNP-urile? Niciodată! Cnp-urile, la fel ca Numerele de Telefon sau Codurile Postale trebuie stocate întotdeauna ca TEXTE, tocmai pentru a putea realiza operații de tip TEXT pe ele (și nu matematice).

Pentru a schimba tipurile de date în Power Query, facem acest lucru foarte ușor. Fiecare coloană are sus, în dreptul său, o iconiță mică unde putem să-i schimbăm tipul de date. De exemplu, în cazul nostru, CNP dorim să fie stocată ca TEXT. Iată cum, din tipul de stocare Stiintific s-a schimbat în Text.

Iată și coloana Ziua 1, la care trebuie să facem invers. Ea acum este stocată ca și TEXT pentru că valorile din ea sunt aliniate la stânga. O să selectăm iconița de la Ziua 1 și o să-i spunem că dorim să vadă de acum încolo valorile ca NUMERE (Whole Number).

Din acest moment o să putem să facem calcule matematice cu valorile de pe coloana Ziua 1.

4. Spargerea coloanelor prin … Exemple

Următoarea transformare importantă pe care v-o propun se numește Text to Columns.

De exemplu, coloana Adresa dorim să o împărțim în două coloane: Județul și Orașul.

Pentru aceasta, selectăm coloana Adresa, iar din Add Column de la Columns from Examples alegem From Selection…

Observați că este adăugată o coloană nouă și facem dublu click pe primul rând și doar scriem județul care apare pe acest prim rând. În cazul nostru, observați că trebuie să scriem Prahova și apăsăm Enter.

Iată că Power Query a înțeles că trebuie extrase toate județele aferente fiecărui rând în parte.

După aceea, din nou selectăm coloana Adresa și repetăm pașii, dublu click pe primul rând și, de data aceasta, scriem Ploiești și apăsăm Enter și apoi confirmăm cu OK.

Iată ce ușor, frumos și interesant avem spartă coloana Adresa în cele două coloane dorite. Acum nu mai avem de făcut decât să ștergem coloana Adresa, deoarece, din acest moment, ea a devenit inutilă.

Celor două coloane noi le modificăm numele, prima se numește Județul, iar a doua se cheamă Orașul.

Le selectăm cu tasta Ctrl, apoi le mutăm în stânga, acolo unde era vechea coloană Adresa.

Și gata! Atât de simplu!

5.  Split Columns

Așa cum am spart mai devreme coloana Adresa în coloanele Județul și Orașul, folosind opțiunea Columns from Examples, iată că acum a venit timpul să spargem și coloane în funcție de poziția anumitor caractere.

De exemplu, coloana CNP dorim să o spargem în coloanele SEX, AN, LUNA, ZI.

Pentru aceasta, trebuie să știm de la al câtelea caracter începem să realizăm Split-ul respectiv. Vă spun acum că în Power Query numărătoarea caracterelor începe de la numarul 0.

Astfel, de la poziția 0 începe cifra GENULUI, apoi de la caracterul 1 începe ANUL, de la al 3-lea începe LUNA, de la al cincilea caracter avem ZIUA, iar ce mai rămâne începând cu poziția 7.

Dacă apăsăm în acest moment OK, observați SEX, ANUL, LUNA, ZIUA și Restul ramas din CNP (care acum este inutil și pe care il ștergem).

Acum nu mai avem de făcut decât să schimbăm etichetele pentru cele 4 noi coloane: SEX, AN, LUNA, ZI

Vă spuneam că, din rațiuni legate de GDPR, am pus același CNP peste tot (și vă asigur că este vorba despre un CNP inexistent).

Observați că toate aceste noi coloane sunt unele numerice. Eventual ne asigurăm de acest lucru selectând Whole Number pentru fiecare.

6. Coloane Calculate în Power Query

Cu operația Split Columns de mai devreme, ați văzut cum din coloana CNP care era de tip TEXT, am extras patru coloane numerice: GEN, AN, LUNA, ZI

Dorim acum să generăm o coloană calculată din coloanele AN, LUNA, ZI și anume să generăm DATA NAȘTERII. Cum coloana An conține doar două cifre, o să adăugăm 1900 la acel An, pentru a avea anul din patru cifre.

De sus din Add Column alegem Custom Column, îi dăm un nume Data Nasterii, iar formula este una singură:

= #date(1900 + [AN] , [LUNA], [ZIUA])

Observați funcția Power Query numită #date care primește trei numere (un an, o luna si o zi) și returnează data calendaristică formată din cele trei componente. Am adaugat la an 1900 deoarece noi initial aveam anul doar din 2 cifre în tabelul nostru.

Apăsăm OK și iată noua coloană Data Nasterii, ea fiind de tip dată calendaristică. Putem să și forțăm tipul Date ca să fim siguri de acest lucru.

O să ștergem acum coloanele initiale: An, Luna, Zi, deoarece nu mai avem acum nevoie de ele. Le selectăm cu tasta Shift pe toate trei și apăsăm click dreapta și Remove.

7. Renunțarea la celulele unite (Unmerge Cells)

Vă aduceți aminte că, în tabelul original din Excel, valorile de pe coloana Anul erau unite la nivel de An?

Observați că, odată aduse valorile de pe coloana Anul în Power Query, acestea nu mai sunt unite, ci conțin valoarea NULL acolo unde nu există valori.

Întrebarea este cum reușim să replicăm valoarea anului peste valorile NULL de sub fiecare AN

De exemplu, aici să apară 2018, 2018, aici 2019, apoi 2020, 2020. Ați prins ideea.

Acest lucru se face foarte simplu în Power Query: selectăm coloana Anul apoi, din Transform alegem Fill și îi spunem Down. Toate valorile NULL sunt înlocuite cu ANUL de deasupra lor.

Incredibil!

8. Popularea celulelor BLANK cu o valoare neutră

În tabelul inițial cu date în Excel, vă aduceți aminte că existau multe celule lăsate necompletate, numitele BLANK. Este recomandat ca, în locul acestor valori lasate necompletate, care aici, în Power Query apar semnalate cu valoarea NULL, să introducem o valoare DEFAULT sau mai bine spus o valoare Neutra (adică cifra 0).

Selectăm cu Ctrl+A întreg tabelul din Power Query și din Transform alegem Replace Values.

Căutăm astfel valorile NULL și le înlocuim cu valoarea 0. Apăsăm OK și iată ce interesant, avem acum 0 scris automat în toate celulele care inițial erau lasate libere.

9. Unpivot

Din punctul de vedere al normalizării datelor este greșit că avem câte o coloană diferită pentru fiecare zi. Să ne gândim că poate aveam 365 de coloane pentru toate zilele dintr-un an. Este absurd să lucram cu atât de multe coloane. Este preferabil ca un tabel să conțină cât mai multe rânduri și cât mai puține coloane. Din multe rațiuni tehnice pe care nu am timp să le amintesc acum, însă pe care am să vi le explic cu siguranță în videoclipurile viitoare.

De pildă, UNPIVOT în cazul nostru ar însemna ca în locul unei singure linii ce are patru coloane (câte una pentru fiecare zi), să avem patru rânduri și doar două coloane derivate din cele 4 ale zilelor. O coloană s-ar numi Ziua și cealaltă coloană Valoarea. Cu alte cuvinte, rândul lui Ionescu Vasile va fi replicat pe patru randuri diferite, iar în locul celor patru coloane de zile să apară doar două coloane. Și dacă aveam 365 de zile tot două coloane aveam: Ziua și Valoarea.

Pentru a face acest lucru: selectăm cele patru coloane pe care dorim să le unpivotăm și alegem din Transform – Unpivot Columns, apăsăm pe această săgeată mică și apăsăm click pe Unpivot only selected columns.

Iată ce interesant, acum nu mai facem decât să modificăm etichetele celor două coloane: Ziua și Valoarea.

Observați că, pentru primul dintre angajați (Ionescu Vasile) avem acum patru rânduri, câte unul pentru fiecare coloană de zile.

10. Exportarea în Excel

Ca o mică recapitulare a ceea ce am facut până acum:

– pentru coloana Nume Prenume, am transformat toate textele sale (prima literă majusculă, restul miniscule din fiecare cuvânt)

– tot pentru această coloană, am eliminat spațiile inutile (cu optiunea TRIM)

– am convertit tipurile de date: coloana CNP la tipul TEXT, iar coloana Ziua 1 am transformat-o la tipul numeric

– coloana Adresa am spart-o în două coloane By Examples (coloana Județ care conține acum doar județele și coloana Oraș ce conține orașele)

– coloana CNP am spart-o în coloanele SEX, AN, LUNA, ZI

– din coloanele AN, LUNA, ZI am creat o singură coloană calculată numită Data Nașterii

– coloana Anul am completat-o cu toti anii, deoarece aceasta continea celule MERGE

– toate celulele BLANK au fost populate cu valoarea 0

– nu în ultimul rând am facut o UNPIVOTARE, în speță, din toate cele patru coloane cu zilele, am creat doar două coloane, una cu numele zilei și cealaltă cu valoarea sa.

Suntem pregătiți să exportăm tot acest tabel transformat în Excel, iată cum: din Home alegem Close and Load, apoi Close and Load To … și în fereastra aceasta numită Import Data îi spunem unde anume să copieze tabelul transformat. Apăsăm OK și iată minunea: în acest moment motorul Power Query aplică toate acele zece transformări.

Iată cum avem repetat anul 2018 (datorită unmerge-ului făcut de noi), apoi fiecare angajat apare de 4 ori pentru că am facut UNPIVOT, Numele oamenilor sunt scrise cu prima literă majusculă și restul minuscule pentru fiecare cuvânt, județul și orașul le avem extrase din vechea coloana Adresa, iar coloana Data Nașterii este calculată automat din vechiul CNP și așa mai departe.

Marele avantaj al lui Power Query este nu numai că aceste operații se fac vizual, cu mouse-ul, după cum ați văzut, dar în cazul în care modificăm datele în tabelul original, de exemplu aici scriu Județul Brasov și Orașul Brasov, OBSERVAȚI CĂ MODIFICĂRILE ÎNCĂ NU SE VĂD ÎN TABELUL REZULTAT.

Pentru a se putea vedea tabelul destinație actualizat, de aici, de sus, apăsăm butonul Refresh. Power Query reaplică automat toți acei pași pe care i-am făcut inițial și iată cum în tabelul destinație, fără să mai facem nimic altceva, avem absolut toate transformările făcute!

Concluzia: Power Query este cel mai în vogă intrument ce ne permite să facem operațiile de IMPORT/CLEAN/TRANSFORM cu doar câteva click-uri de mouse.

Acest instrument nou Power Query, împreună cu fratele său, Power Pivot, vor fi în curând de NEIGNORAT! Nu vă veți mai putea numi utilizatori de Excel daca nu știți să le folosiți. Și încă la nivel avansat!

Rămâneți conectați pentru că urmează noi și noi videoclipuri din acest minunat capitol legat de analiza datelor, numit Business Intelligence. Pe curând!