Power BI Garage

Ce este PowerQuery

Power Query este un motor de transformare și pregătire a datelor, care include o interfață grafică pentru sursele de date și un Power Query Editor pentru implementarea transformărilor. Acesta este folosit pentru a efectua procesarea de tip extract, transform, and load (ETL) a datelor în Power BI Desktop.

Power Query nu este limitat doar la Power BI Desktop; este accesibil și în diverse alte produse și servicii Microsoft, inclusiv:

1. Excel 
2. Microsoft Azure Data Factory 
3. Microsoft Dataverse 
4. Power BI Service (Fabric) 
5. PowerAutomate 
6. Azure Data Factory 
7. SSIS 
8. Dynamics 

Principalele faze din Power Query

Conectarea la date

Ce sunt Conectorii de Date

Conectorii Power Query sunt componente care permit Power Query să se conecteze la surse externe de date și să le recupereze. Acești conectori acționează ca intermediari între Power Query și sursa de date, oferind instrucțiunile și protocoalele necesare pentru a accesa și importa datele.

În versiunea Power Query din Power BI Desktop, conectorii sunt grupați în următoarele categorii:

• Fișiere
• Baze de date
• Microsoft Fabric
• Power Platform
• Azure
• Servicii online
• Altele

Același conector poate apărea în mai multe categorii

Lista completa a conectorilor Power Query

Interacțiuni cu conectorii

Selecția conectorului: Power Query include o bibliotecă de conectori încorporați care acoperă o gamă largă de surse de date, inclusiv baze de date, fișiere, servicii online și altele. Atunci când inițiezi o conexiune de date, Power Query selectează conectorul corespunzător pe baza sursei la care dorești să accesezi.

Informații de conexiune: Când folosești Power Query pentru a te conecta la o sursă de date, furnizezi informații de conexiune, cum ar fi locația sursei (de exemplu, URL-ul sau calea fișierului), credentialele (dacă sunt necesare) și orice parametri sau opțiuni specifice necesare pentru a stabili o conexiune. Aceste informații depind de tipul de conector pe care îl folosești.

Interacțiunea cu conectorul / Previzualizarea datelor: Odată ce conectorul este selectat, Power Query interacționează cu acesta pentru a stabili o conexiune la sursa de date. Acest lucru poate implica trimiterea de interogari către sursă și gestionarea răspunsurilor pentru a stabili o sesiune de conexiune.

Extragerea datelor: După ce conexiunea este stabilită, Power Query preia datele din sursă folosind conectorul. Conectorul comunică cu API-ul, protocolul sau driverul sursei de date pentru a extrage datele solicitate. Aceasta poate include preluarea de tabele, înregistrări, fișiere sau orice altă structură de date relevantă.

Tutoriale pas cu pas despre cum să te conectezi la diferite surse de date sunt disponibile aici:

• Folder (in lucru)
• Fisier Excel (in lucru)
• .CSV si .TXT (in lucru)
• Sharepoint (in lucru)
• Baza de date SQL (in lucru)
• SAP (in lucru)

Transformarea Datelor

Transformările de date în Power Query sunt un aspect esențial al pregătirii și manipulării datelor pentru a asigura că datele sunt în formatul corect pentru analiză și raportare. Power Query oferă un set puternic de instrumente bazate pe interfața utilizatorului pentru a transforma și curăța datele din diverse surse. Iată o prezentare generală a transformărilor de date în Power Query:

Filtrarea și filtrarea rândurilor:

• Poți filtra datele pentru a include doar rândurile care îndeplinesc anumite criterii.
• Filtrele pot fi aplicate pe baza valorilor din coloane, modelelor de text, datelor și altor condiții.

Sortarea datelor:

• Poți sorta datele pe una sau mai multe coloane în ordine crescătoare sau descrescătoare pentru a le face mai organizate și mai ușor de lucrat cu ele.

Conversia tipurilor de date:

• Power Query permite schimbarea tipurilor de date ale coloanelor pentru a se potrivi nevoilor tale de analiză. De exemplu, conversia textului în numere sau date.

Agregarea datelor:

• Poți efectua operațiuni de agregare a datelor, cum ar fi gruparea rândurilor și calcularea agregărilor, precum suma, media, numărul de elemente etc.

Pivotarea și depivotarea datelor:

• Poți pivotiza datele pentru a transforma valorile randurilor în coloane noi (de exemplu, transformarea lunilor în coloane pentru un set de date pe termen lung).
• Depivotarea datelor transformă mai multe coloane în rânduri pentru o analiză mai bună.

Împărțirea și combinarea datelor:

• Poți împărți o coloană în mai multe coloane pe baza delimitatorilor sau modelelor, sau poți combina mai multe coloane într-o singură coloană.

Adăugarea de coloane calculate:

• Power Query susține crearea de coloane calculate utilizând limbajul M.

Transformări condiționale:

• Transformările pot fi condiționale, adică poți aplica acțiuni diferite pe baza unor condiții specifice în datele tale.

Eliminarea duplicatelor:

• Poți elimina rândurile duplicate din datele tale pentru a asigura acuratețea datelor.

Gestionarea erorilor și a datelor lipsă:

• Power Query oferă opțiuni pentru gestionarea erorilor și a datelor lipsă, permițându-ți să controlezi modul în care sunt gestionate erorile în timpul transformărilor de date.

Profilarea datelor:

• Power Query oferă instrumente de profilare a datelor pentru a evalua calitatea acestora și pentru a identifica probleme, cum ar fi valorile lipsă sau valorile aberante.

Transformări personalizate:

• Poți crea transformări personalizate utilizând limbajul de formulă Power Query (M) pentru manipulări complexe ale datelor.

Transformările de date în Power Query se realizează de obicei pas cu pas, iar fiecare pas de transformare este înregistrat în interogare. Această abordare pas cu pas face ușor de repetat și automatizat procesele de pregătire a datelor, asigurându-se că datele sunt consistente și exacte în analizele și rapoartele tale.

Combinarea Datelor

Prezentare generală

Power Query este un instrument versatil de integrare a datelor în diverse produse Microsoft, precum Excel, Power BI și altele. Scopul său principal este de a conecta surse de date disparate, oferind o conectivitate usor de realizat. Acesta poate stabili conexiuni cu o gamă largă de surse de date, inclusiv baze de date (precum SQL Server, MySQL), fișiere de tip spreadsheet (cum ar fi Excel), servicii bazate pe cloud (precum Azure sau AWS), API-uri web și multe altele.

Organizațiile moderne se confruntă cu o gamă variată de surse de date. De exemplu, informațiile despre clienți pot fi stocate într-o bază de date CRM, datele despre vânzări într-un fișier Excel, informațiile despre produse pe un site web și analizele de marketing într-un serviciu cloud. Aceste surse de date folosesc adesea formate, structuri și locații diferite.

Power Query le permite utilizatorilor să se conecteze nu doar la aceste surse de date variate, dar și să efectueze combinații și integrarea datelor într-un mod facil. Acest lucru implică combinarea datelor din surse distincte într-un singur set unificat de date. Combinarea datelor este esențială deoarece permite utilizatorilor să creeze o viziune completă a datelor lor, agregând informații din multiple surse într-un format unificat. Aceasta simplifică procesul de analiză a datelor, raportare și luare a deciziilor.

Adăugarea de interogări (Append Queries)

În Power Query, „Append” se referă la operațiunea de stivuire sau combinare a două sau mai multe tabele sau seturi de date pe verticală pentru a crea un tabel mai mare. Această operațiune este utilizată pentru a consolida datele din mai multe tabele sau interogări cu aceeași structură (adică aceleași coloane) într-un set de date unificat.

• Sursa de date: Începi cu două sau mai multe tabele sau interogări pe care dorești să le combini. Aceste tabele pot proveni din diverse surse, cum ar fi fișiere Excel diferite, tabele de baze de date sau alte conexiuni de date.
• Coloane corespunzătoare: Pentru a efectua o operațiune de adăugare, este esențial ca tabelele care urmează să fie adăugate să aibă aceeași structură, adică aceleași nume și tipuri de date ale coloanelor. Power Query va potrivi automat coloanele pe baza numelor acestora atunci când se face append-ul.
• Operațiunea de Append: Când efectuezi operațiunea de Append, Power Query ia rândurile din al doilea tabel și le adaugă sub rândurile primului tabel. Dacă există mai multe tabele, aceasta continuă să le adauge unul câte unul.
• Tabelul rezultat: Rezultatul va fi un tabel unic care conține toate rândurile din fiecare dintre tabelele originale stivuite unul peste altul. Coloanele din tabelul rezultat vor fi aceleași ca și cele din tabelele originale.

Consultați aici (in lucru) pentru mai multe detalii despre adăugarea de interogări în Power Query.

Combinarea interogărilor (Merge Queries)

În Power Query, „Merge” se referă la operațiunea de combinare a datelor din două sau mai multe tabele sau interogări pe orizontală, pe baza uneia sau mai multor coloane comune (chei). Această operațiune este cunoscută și sub denumirea de „join” în terminologia bazelor de date. Unirea tabelelor permite crearea unui tabel nou care combină coloane din mai multe tabele pe baza valorilor corespunzătoare din coloanele specificate.

• Surse de date: De obicei, începi cu două sau mai multe tabele sau interogări pe care vrei să le combini. Aceste tabele pot proveni din surse de date diferite, cum ar fi Excel, baze de date sau servicii web.
• Coloane comune (chei): Pentru a efectua o operațiune de merge/join, trebuie să identifici una sau mai multe coloane comune între tabelele pe care dorești să le combini. Aceste coloane comune servesc drept bază pentru potrivirea rândurilor din tabelele diferite.
• Tabelul rezultat: După operațiunea de merge/join, obții un tabel nou care combină coloanele din tabelele originale pe baza coloanelor comune. Tabelul rezultat va conține coloane din ambele tabele, iar tu poți alege ce coloane să incluzi în tabelul rezultat.

Power Query suportă 6 tipuri diferite de joinuri, inclusiv:

• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Left Anti
• Right Anti

Consultați aici (in lucru) pentru mai multe detalii despre combinarea interogărilor în Power Query.

Căutare fuzzy (aproximativă)

Aditional față de tipurile de join menționate mai sus, Power Query oferă și o opțiune de căutare fuzzy (aproximativă).

Căutarea fuzzy în Power Query este o tehnică utilizată pentru a efectua potrivirea aproximativă sau „fuzzy” a valorilor între două tabele sau seturi de date pe baza similitudinii acestora, mai degrabă decât pe potriviri exacte. Aceasta este deosebit de utilă atunci când lucrezi cu date care pot conține greșeli de tipar, greșeli de scriere, variații în formatare sau discrepanțe minore.

Consultați aici (in lucru) pentru mai multe detalii despre potrivirea fuzzy în Power Query.

Ingestia datelor si Query Folding

Managementul Încărcării Datelor

Ingestia datelor se referă la etapa în care structurile de date transformate sunt incarcate în modelul de date. Dezvoltatorii au flexibilitatea de a decide, pe baza fiecărei interogări, dacă rezultatul acesteia ar trebui încărcat în baza de date columară sau utilizat doar în timpul procesării datelor (servind drept sursă pentru alte interogări) și apoi eliminat la finalizarea procesului de actualizare a datelor.

Aceasta se poate realiza făcând clic dreapta pe o interogare și activând/dezactivând opțiunea „Enable Load” (Permite Încărcarea).

De asemenea, dezvoltatorii pot alege să includă actualizarea fiecărei interogări în procesul de actualizare globală a datasetului sau pot lăsa interogările statice, având posibilitatea de a le actualiza individual utilizând Power BI Desktop sau prin intermediul endpoint-ului XMLA.

Query Folding (Plierea Interogărilor)

Prezentare generală

Query folding în Power Query este un concept esențial care se referă la optimizarea proceselor de extragere și transformare a datelor. Este un proces prin care Power Query transferă cât mai mult din efortul de procesare a datelor înapoi către sursa de date (de exemplu, o bază de date), mai degrabă decât să proceseze datele în memorie în cadrul Power Query. Această optimizare are scopul de a îmbunătăți performanța interogărilor, de a reduce utilizarea memoriei și de a minimiza transferul de date între sursa de date și Power Query.

Query folding este procesul prin care Power Query traduce pașii săi de interogare de nivel înalt în interogări native sau comenzi pe care sursa de date le poate executa direct. Aceasta implică generarea de interogări SQL, apeluri REST API sau alte comenzi specifice sursei care sunt trimise sursei de date pentru procesare.

Surse care suportă query folding

Majoritatea surselor de date care dispun de un limbaj de interogare suporta query folding. Aceste surse pot include baze de date relaționale, fluxuri OData (inclusiv liste SharePoint), Exchange și Active Directory. Totuși, sursele de date precum fișierele de tip spreadshit, fișierele blob și cele web nu suportă query folding.

Transformări care pot fi foldate / pliate

Transformările într-o sursă de date relațională care sunt eligibile pentru query folding sunt acelea care pot fi exprimate ca o singură interogare SELECT. Această interogare SELECT poate include clauze WHERE, GROUP BY și JOIN și poate conține expresii M folosind funcții în mod obișnuit suportate în bazele de date SQL.

În general, următoarele transformări pot fi pliate:

• Eliminarea coloanelor: Eliminarea unor coloane din tabele.
• Redenumirea coloanelor: Modificarea denumirilor coloanelor utilizând aliasuri de coloană SELECT.
• Filtrarea rândurilor: Filtrarea rândurilor pe baza unor criterii specifice, fie prin valori statice, fie prin parametrii Power Query (utilizând predicatele clauzei WHERE).
• Gruparea și sumarizarea: Agregarea datelor aplicând clauza GROUP BY.
• Extinderea coloanelor de tip Record: Utilizarea coloanelor de cheie externă pentru a realiza îmbinări între două tabele sursă, prin intermediul clauzei JOIN.
• Îmbinarea non-fuzzy: Combinarea interogărilor pliate pe baza aceleași surse, de asemenea realizată prin clauza JOIN.
• Appendul de interogări: Appendul interogărilor pliate din aceeași sursă, utilizând operatorul UNION ALL.
• Adăugarea de coloane personalizate cu logică simplă: Încorporarea unor coloane personalizate cu logică simplă, incluzând operații de bază (de exemplu, funcții matematice sau de manipulare a textului care au contrapartide în sursa de date).
• Pivotarea și Unpivotarea: Utilizarea operatorilor PIVOT și UNPIVOT pentru restructurarea datelor.

În esență, query folding în Power Query permite ca aceste transformări să fie trimise înapoi către sursa de date ca interogări SQL eficiente, optimizând procesul de recuperare și procesare a datelor de câte ori este posibil.

Transformări care nu pot fi pliate

• Îmbinarea interogărilor din surse diferite: Combinarea interogărilor care provin din surse de date distincte.
• Appendul de interogări din surse diferite: Unirea sau concatenarea interogărilor provenite din diferite origini de date.
• Adăugarea de coloane personalizate cu logică complexă: Utilizarea funcțiilor M care nu au contrapartide în sursa de date, rezultând o logică complexă.
• Adăugarea coloanelor de tip Index.

Cum să înțelegi dacă o interogare poate fi pliată

În panoul Setările Interogării, când faci clic dreapta pe ultimul pas aplicat, dacă opțiunea „View Native Query” (Vizualizează interogarea nativă) este activată (nu este estompată), înseamnă că întreaga interogare poate fi pliată.

Dupa selectarea optiunii, interogarea nativa va fi afisata.

Opțiunea „View Native Query” este disponibilă doar pentru anumite surse de date relaționale / conectoare care generează SQL. Aceasta nu funcționează pentru conectoarele bazate pe OData, de exemplu, chiar dacă folding-ul se întâmplă în spate.