Power BI Garage

Combinarea tabelelor

Power BI este un sistem OLAP, iar în timpul construirii unei scheme de tip Star Schema, este posibil să fie necesar să combinăm mai multe tabele într-o singură structură.

Pentru a combina orizontal tabelele, utilizăm funcționalitatea Merge, care seamănă foarte mult cu operațiunea JOIN din SQL.

Să vedem câteva exemple de operațiuni de îmbinare.

Avem următoarele două tabele în Excel:

• Transaction – tabelul din stânga
• Chart of Accounts – tabelul din dreapta

Observații înainte de îmbinare:

• Cheia de îmbinare este formată din două coloane: Account și Dept.
• În tabelul Transactions (stânga), există două combinații de chei care nu se regăsesc în tabelul Chart of Accounts (dreapta) – evidențiate cu roșu.
• În tabelul Chart of Accounts (dreapta), există două combinații de chei care nu se regăsesc în tabelul Transactions (stânga) – evidențiate cu galben.

Importul datelor în Power BI:

1. Mergem la Get Data → Excel Workbook.
2. Alegem fișierul și îl deschidem.
3. În fereastra de previzualizare, selectăm ambele tabele.
4. În Power Query, cu tabelul Transactions (stânga) selectat, mergem la Home → Merge as New Query.

5. În fereastra Merge, trebuie să selectăm următoarele:

a. Tabelul cu care dorim să facem îmbinarea (tabelul din dreapta – Chart of Accounts).
b. Cele două chei de îmbinare – deoarece avem două coloane pentru fiecare cheie, trebuie să le selectăm în aceeași ordine, ținând apăsată tasta CTRL.
c. Tipul de îmbinare – implicit este Left Outer (All from first, matching from second).
d. Fuzzy Matching (opțional) – se activează doar dacă dorim să permitem potriviri aproximative în loc de potriviri exacte.

După ce apăsăm OK, tabelul din care am inițiat îmbinarea (tabelul din stânga) va avea o coloană nouă cu valori de tip Table.

Pentru a vedea conținutul fiecărei tabele din această coloană, facem click pe o celulă (dar nu pe textul „Table”), iar Power Query va afișa un preview al datelor corespunzătoare.

În noua coloană, apasă pe butonul de extindere (📎) și selectează coloanele din tabelul din dreapta pe care dorești să le adaugi în tabelul din stânga.

Cele mai utilizate tipuri de Merge / Join în Power BI:

1. Left Outer Join (Implicit / Default) – Toate rândurile din tabelul din stânga și doar potrivirile din tabelul din dreapta. Dacă nu există potrivire, valorile vor fi NULL.
2. Right Outer Join – Toate rândurile din tabelul din dreapta și doar potrivirile din tabelul din stânga. Valorile fără potrivire vor fi NULL.
3. Full Outer Join – Unește toate rândurile din ambele tabele. Rândurile fără potrivire vor avea valori NULL în coloanele lipsă.
4. Inner Join – Păstrează doar rândurile care au potriviri în ambele tabele. Rândurile fără potrivire sunt excluse.
5. Anti Join (Left Anti Join) – Returnează doar rândurile din tabelul din stânga care nu au o potrivire în tabelul din dreapta.
6. Anti Join (Right Anti Join) – Returnează doar rândurile din tabelul din dreapta care nu au o potrivire în tabelul din stânga.

Să vedem cum funcționează aceste tipuri de îmbinări (joins) în Power Query.

Left Outer Join

Să revizuim tabelele noastre de intrare:

Combinațiile Account & Dept:

• 64015 & 150
• 64010 & 350

se regăsesc doar în tabelul din stânga.

În schimb, combinațiile:

• 64040 & 150
• 64020 & 150

se regăsesc doar în tabelul din dreapta.

La pasul 5 din instructiunile de mai sus, lasam Join Kind pe Left Outer (toate randurie din primul tabel, randurile cu potrivire dinal doilea tabel)

Dupa ce expandam coloana nou creata, vom putea observa urmatoarele rezultate

Îmbinarea externă stângă (Left Outer Join) a returnat toate rândurile din tabelul Transactions (stânga) și doar rândurile care se potrivesc din tabelul Charts of Accounts (dreapta).

Au fost returnate 8 rânduri.

Combinațiile „64015 & 150” și „64010 & 350” sunt păstrate în tabelul rezultat deoarece există în tabelul din stânga, dar valorile din tabelul din dreapta sunt goale, deoarece nu există.

Îmbinarea externă dreaptă (Right Outer Join – toate din al doilea, potrivirile din primul).

Sa examinam rezultate inainte de a expanda noua coloana

7 rânduri sunt acum returnate de operația de merge.

Combinațiile „64015 & 150” și „64010 & 350” nu sunt returnate, deoarece nu există în tabelul din dreapta.

Combinațiile „64040 & 150” și „64020 & 150” nu sunt prezente în tabelul pre-expandat (nu există în tabelul din stânga), dar sunt grupate în tabel sub rândul nr. 5 (cu valori nule) și pot fi vizualizate în previzualizare.

Odată ce tabelul îmbinat este extins, tabelul returnat va avea acum 8 rânduri, cu 1 rând pentru combinațiile „64040 & 150” și „64020 & 150”.

Full Outer Join (toate randurile din ambele tabele)

De această dată, interogarea de îmbinare a returnat 9 rânduri.

Combinațiile „64015 & 150” și „64010 & 350” sunt prezente în tabelul pre-expandat, iar există un rând nul (#5) unde combinațiile „64040 & 150” și „64020 & 150” sunt stocate într-un obiect tabel.

Odată ce extindem coloanele îmbinate, observăm că tabelul are acum 10 rânduri, conținând toate combinațiile existente atât în tabelul din stânga, cât și în cel din dreapta.

Inner Join (doar randurile ce exista in amble tabele)

Cu îmbinarea internă (Inner Join), doar combinațiile care există în ambele tabele vor fi prezente în tabelul rezultat.

Combinațiile „64015 & 150” și „64010 & 350” din tabelul din stânga, precum și „64040 & 150” și „64020 & 150” din tabelul din dreapta nu vor fi prezente în rezultatul interogării.

Left Anti Join (randurile care exista in primul tabel si nu exista in al doilea)

Left Anti Join va returna doar rândurile care există exclusiv în tabelul Transactions (stânga): combinațiile „64015 & 150” și „64010 & 350”.

Right Anti Join (randurile care nu exista in primul tabel si exista in al doilea)

Right Anti Join va returna doar rândurile care există exclusiv în tabelul Chart of Accounts (dreapta): combinațiile „64040 & 150” și „64020 & 150”.

Chiar dacă nu poți selecta direct următoarele tipuri de îmbinări, le poți crea totuși în Power Query.

Full Anti Join (randurile care exista in unul din tabele, dar nu exista in celalalt)

În Power Query, este simplu să creezi îmbinări Right Anti sau Left Anti, dar nu există opțiunea de a crea direct o îmbinare Full Anti.

Dar putem totuși crea o îmbinare Full Anti în câțiva pași.

1. Creăm o interogare cu tipul de merge Right Anti.

2. Cream o a doua interogare de tipul Left Ani

3. Facem un Append intre interogarile de la pasii 1 si 2

Cross Join (produsul cartezian)

Nici Cross Join nu este un tip de join disponibil in interfata Power Query

Să presupunem că avem două coloane și vrem să generăm un tabel care să conțină toate combinațiile posibile de valori din aceste două liste.

Pentru a realiza acest lucru, urmați acești pași.

1. În ambele interogări, accesați Add Column -> Custom Column.
   Furnizați un nume pentru noua coloană și în bara de formulă introduceți pur și simplu =1.

2. Îmbină cele două tabele pe baza cheilor create mai sus.

3. tergeti cheia creata la pasul 2

Potrivirea Fuzzy in Power Query

In tipurile de join de mai sus a existat o conditie foarte importanta: cheile trebuie sa aiba o potrivire exacta pentru a functiona.

Totuși, atunci când lucrăm cu date introduse manual alături de date generate de calculator, pot apărea incoerențe din cauza greșelilor de ortografie, a diferențelor între majuscule și minuscule, a abrevierilor, a punctuației și a termenilor alternați.

Îmbinările implicite din Power Query iau în considerare doar potrivirile exacte, ceea ce poate limita capacitatea de a compara eficient două liste.

Îmbinare normală (strictă).
Să explorăm scenariul invers de la acest punct. În loc să utilizăm Contul și Departamentul pentru a obține Numele, să utilizăm Numele pentru a obține Contul și Departamentul.
Având în vedere că câmpul Nume este un șir de caractere și se bazează pe inputul uman din tabelul tranzacțiilor, pot exista incoerențe în modul în care tranzacțiile sunt etichetate.

După cum puteți vedea, există mici diferențe între 3 rânduri, iar într-un rând un întreg cuvânt este diferit (este în germană în loc de engleză).

Dacă încercăm să efectuăm o îmbinare Full Outer normală, acesta este rezultatul îmbinării.

Pentru acele combinații în care nu există o potrivire exactă, îmbinarea va returna null.

Potrivire fuzzy
Putem schimba acest comportament strict de potrivire selectând opțiunea „Use fuzzy matching to perform the merge” în fereastra de îmbinare.

Și apoi am putea primi următoarea eroare.

Acest lucru se datorează faptului că nu am declarat cheile ca text (potrivirea fuzzy funcționează doar pe șiruri de caractere). Odată ce schimbăm tipul de date al cheilor în text, îmbinarea va funcționa corect.

Power Query folosește algoritmul de similaritate Jaccard pentru a calcula asemănarea dintre perechi de instanțe. Identifică orice scor de similaritate de 80% sau mai mare ca fiind o potrivire. De exemplu, „Revenue – 9 Holes” este considerat suficient de similar cu „Revenue – 9 Hole”, în ciuda diferențelor minore, cum ar fi un caracter suplimentar sau utilizarea literelor mici. Potrivirea fuzzy ia în considerare și variații precum caractere transpuse (de exemplu, „friend” vs. „freind”) și discrepanțe minore de punctuație (de exemplu, „mrs” vs. „mrs.”). Cuvintele mai lungi, cu mai multe caractere similare, sunt mai susceptibile de a produce potriviri exacte atunci când se folosește Potrivirea Fuzzy.

Cu o excepție!
Potrivirea fuzzy nu a reușit să deducă că „besonder” în germană înseamnă „Special” în engleză, prin urmare ultimele 2 rânduri nu sunt îmbinate.

Dar putem ajuta potrivirea fuzzy în aceste cazuri oferind un tabel de mapare cu coloane predefinite: From -> To.

Odată ce încărcăm acest tabel în Power Query și îl folosim ca parametru în meniul de potrivire fuzzy (valorile din coloanele „From” și „To” trebuie să fie tot text).

Bune Practici:

Gestionarea unei soluții bazate pe potrivirea fuzzy poate părea descurajantă, mai ales atunci când întâmpinați probleme recurente în timpul actualizărilor.
Pentru a stabili un sistem durabil, luați în considerare implementarea următorilor pași:

Preprocesarea datelor:
Înainte de a îmbina datele, abordați termenii sau tiparele de caractere comune care necesită corecție. De exemplu, dacă tabelul dvs. de căutare nu include niciodată „#” în adrese, dar sursa dvs. o face, înlocuiți toate aparițiile caracterului „#” în coloana relevantă.

Utilizați modele Anti-Join:
Folosind unul dintre modelele Anti-Join, generați un tabel de excepții care evidențiază termenii necunoscuți. Revizuiți acest tabel după fiecare actualizare.

Urmăriți elementele necunoscute:
Dezvoltați o formulă Excel sau DAX pentru a număra elementele necunoscute din tabelul de excepții. Afișați acest număr pe o pagină de raport pentru monitorizare facilă. Dacă numărul nu este zero după actualizare, aceasta indică existența unor termeni suplimentari care trebuie incluși în lista de traduceri.

Actualizați tabelul de traduceri:
Introduceți termenii necunoscuți și mapările corespunzătoare în tabelul de traduceri. Asigurați-vă de acuratețe prin copierea și lipirea din tabelul de excepții.

Monitorizați progresul:
La fiecare actualizare, numărul de nepotriviri ar trebui să scadă pe măsură ce dicționarul dvs. de termeni devine mai mare. Perfecționați continuu tabelul de traduceri pentru a îmbunătăți acuratețea potrivirilor.