Power Bi alapok: M nyelv táblakapcsolat beállítása

M nyelv a Power Query Formula Language informális neve, amely a forrás adatok átalakításában van fő szerepe. Egyesek szerint az M a Data Mashup, mások szerint a Data Modeling rövidítése. Az M egy funkcionális nyelv, és fontos, hogy ismerjük a funkcióit. Maga a nyelv jóval több a Power Query grafikus felületén elérhető funkcióknál.

Az M nyelv a két programozási blokkból ál (speciális szerkesztőnél jelenik meg) :

let: az összes változó definíciója
in: output! Igen, az valójában azt jelenti, hogy ki! Minden, amit ebbe a blokkba tesz, a lekérdezés kimenete lesz

Az M (Power Query Formula Language) megkülönbözteti a kis- és nagybetűket. Különbség van a és A között.

A Power BI Power Query M nyelvében a JoinKind az összekapcsolási módokat határozza meg, amikor két táblát egyesítesz (merge). Ezek az SQL JOIN műveleteknek felelnek meg, és meghatározzák, hogy mely rekordok kerülnek be az eredményhalmazba.

1. JoinKind.Inner = 0 (Belső JOIN)

Csak azok a sorok kerülnek be az eredménybe, amelyek mindkét táblában szerepelnek a kapcsolati feltétel alapján.
Ekvivalens az SQL INNER JOIN-nal.

🔹 Példa:
Ha van egy Vevők és egy Rendelések tábla, akkor az INNER JOIN csak azokat a vevőket fogja megmutatni, akiknek van rendelésük.

2. JoinKind.LeftOuter = 1 (Bal külső JOIN)

A bal oldali tábla összes sora bekerül az eredménybe.
A jobb oldali tábla adatai csak akkor, ha van megfelelő kapcsolat.
Ha nincs egyezés, akkor a jobb oldali tábla értékei NULL lesznek.

🔹 Példa:
Minden vevő megjelenik, még akkor is, ha nincs rendelése, de azok a rendelések, amelyekhez nincs megfelelő vevő, nem jelennek meg.

Ekvivalens az SQL LEFT OUTER JOIN-nal.

3. JoinKind.RightOuter = 2 (Jobb külső JOIN)

A jobb oldali tábla összes sora bekerül az eredménybe.
A bal oldali tábla adatai csak akkor, ha van megfelelő kapcsolat.
Ha nincs egyezés, akkor a bal oldali tábla értékei NULL lesznek.

🔹 Példa:
Minden rendelés megjelenik, akkor is, ha nincs hozzá tartozó vevő.

Ekvivalens az SQL RIGHT OUTER JOIN-nal.

4. JoinKind.FullOuter = 3 (Teljes külső JOIN)

Mindkét tábla összes sora bekerül az eredménybe.
Ha van kapcsolat, az adatok összeolvadnak.
Ha nincs egyezés, akkor a hiányzó értékek NULL lesznek.

🔹 Példa:

Minden vevő és rendelés megjelenik, még akkor is, ha nincs egyezés.

Ekvivalens az SQL FULL OUTER JOIN-nal.

5. JoinKind.LeftAnti = 4 (Bal Anti JOIN)

Csak a bal oldali tábla azon sorai kerülnek be az eredménybe, amelyeknek nincs megfelelő párja a jobb oldalon.
Az SQL LEFT ANTI JOIN-nak felel meg.

🔹 Példa:

Azok a vevők jelennek meg, akiknek nincs rendelésük.

6. JoinKind.RightAnti = 5 (Jobb Anti JOIN)

Csak a jobb oldali tábla azon sorai kerülnek be az eredménybe, amelyeknek nincs megfelelő párja a bal oldalon.
Az SQL RIGHT ANTI JOIN-nak felel meg.

🔹 Példa:

Azok a rendelések jelennek meg, amelyekhez nincs kapcsolódó vevő.

Összefoglalás egy táblázatban:

JoinKind	Eredmény
`JoinKind.Inner` (0)	Csak a mindkét táblában létező sorok
`JoinKind.LeftOuter` (1)	Minden bal oldali + egyező jobb oldali
`JoinKind.RightOuter` (2)	Minden jobb oldali + egyező bal oldali
`JoinKind.FullOuter` (3)	Minden sor mindkét táblából (NULL értékekkel)
`JoinKind.LeftAnti` (4)	Csak a bal oldali tábla azon sorai, amiknek nincs párja a jobb oldalon
`JoinKind.RightAnti` (5)	Csak a jobb oldali tábla azon sorai, amiknek nincs párja a bal oldalon

1. Power BI Model Dokumentálásának Módszerei

1.1. Power BI External Tools (pl. DAX Studio, Tabular Editor)

Ha egy Power BI Desktop (PBIX) fájlt szeretnél dokumentálni, akkor a következő külső eszközök segíthetnek:

DAX Studio – DAX lekérdezések futtatására és az adatmodell szerkezetének vizsgálatára.
Tabular Editor – Az egész Power BI modell exportálható JSON/XML formátumban.
Power BI REST API – Ha a modellt Power BI Service-ben tárolod, API hívásokkal lekérhető az adatmodell struktúrája.

1.1.1. DAX Studio használata

Ha a Power BI fájl meg van nyitva, a DAX Studio segítségével futtathatod az alábbi lekérdezést a tábla-kapcsolatok listázásához:

---sql

SELECT * FROM $SYSTEM.TMSCHEMA_RELATIONSHIPS

🔹 Mit kapsz ezzel?

Kapcsolatok neve, oszlopok nevei
Egyirányú vagy kétirányú kapcsolat
Kardinalitás (1:1, 1:N, N:N)
Aktív vagy inaktív kapcsolatok

1.1.2. Tabular Editor használata

A Tabular Editor segítségével az egész Power BI adatmodell exportálható egy JSON vagy XML fájlba:

Nyisd meg a Tabular Editor 2/3-at.
Nyisd meg a Power BI modellt (File -> Open from DB).
Exportálj egy JSON/XML fájlt (File -> Save to Folder).

1.2. Power BI Script alapú dokumentáció (PowerShell + REST API)

Ha Power BI Service-ben tárolt modellekről szeretnél dokumentációt készíteni, akkor a Power BI REST API segítségével kinyerheted a kapcsolati információkat.

🔹 Példa PowerShell szkript a modell dokumentálására:

---powershell

$tenantId = "YOUR_TENANT_ID"
$clientId = "YOUR_CLIENT_ID"
$clientSecret = "YOUR_SECRET"
$groupId = "YOUR_WORKSPACE_ID"
$datasetId = "YOUR_DATASET_ID"

$token = Get-PowerBIAccessToken -TenantId $tenantId -ClientId $clientId -ClientSecret $clientSecret
$headers = @{ Authorization = "Bearer $token" }

# Lekérdezzük a dataset kapcsolatokat
$response = Invoke-RestMethod -Uri "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId" -Headers $headers -Method Get

Write-Output $response

🔹 Ezzel lekérheted:

Táblák és oszlopok listáját
Kapcsolatokat és azok típusait
DAX mértékeket és számított oszlopokat

2. Power BI Modell Automatikus Dokumentálása Excel-be

Ha egy automatizált, jól strukturált dokumentációt szeretnél Excel-be exportálni, akkor Power BI Report Builder vagy Power Automate segítségével egy CSV/Excel jelentést generálhatsz.

📌 Lépések:

Power BI Desktop megnyitása

DAX Studio-ban futtasd az alábbi lekérdezést:

---sql

SELECT * FROM $SYSTEM.TMSCHEMA_COLUMNS

Az eredményt exportáld CSV vagy Excel formátumba.
Készíts egy Power BI riportot ebből az adathalmazból.

3. Tábla Kapcsolatok és JoinKind Dokumentálása

Ha egy táblák közötti kapcsolati dokumentációt szeretnél készíteni, akkor érdemes egy Power BI jelentést készíteni az adatmodell struktúrájáról.

🔹 Lépések Power BI riporthoz:

Készíts egy adatforrást a kapcsolatok listázására
- DAX Studio vagy Tabular Editor segítségével exportáld a $SYSTEM.TMSCHEMA_RELATIONSHIPS tartalmát.
Hozz létre egy Power BI vizualizációt
- Használj egy matrix vizualizációt a kapcsolatok ábrázolására.
Jelöld a JOIN típusokat
- Egy új számított oszlopot (Calculated Column) adhatsz hozzá, amely a JoinKind értékeket tartalmazza.

🔹 DAX Példa a JoinKind meghatározására:

---DAX

JoinType = SWITCH(
    TRUE(),
    RELATED('Relationships'[JoinKind]) = 0, "Inner Join",
    RELATED('Relationships'[JoinKind]) = 1, "Left Outer Join",
    RELATED('Relationships'[JoinKind]) = 2, "Right Outer Join",
    RELATED('Relationships'[JoinKind]) = 3, "Full Outer Join",
    RELATED('Relationships'[JoinKind]) = 4, "Left Anti Join",
    RELATED('Relationships'[JoinKind]) = 5, "Right Anti Join",
    "Unknown"
)

🔹 Ezzel egy Power BI vizualizációban láthatod a kapcsolatok típusát.

4. Generált UML Diagram Készítése a Modellről

Ha vizuálisan szeretnéd látni a modell kapcsolatait, akkor:

Használd a VertiPaq Analyzer-t (Tabular Editor kiegészítő)
Használd a DB Diagram Generator-t, amely UML szerű modellt készít az adatkapcsolatokról
Exportáld a kapcsolati struktúrát egy Graphviz (.dot) fájlba, amit aztán egy UML generátorral lehet megjeleníteni.

🔹 Graphviz fájl generálásához SQL alapján:

---sql

SELECT 
    'Table' AS Node1, 
    ColumnName AS Node2, 
    ' -> ' AS Relation
FROM $SYSTEM.TMSCHEMA_COLUMNS

Ezt a fájlt egy UML diagram szoftverben vizualizálhatod.

Összegzés

✅ Ha a teljes Power BI modellt szeretnéd dokumentálni:

DAX Studio vagy Tabular Editor az adatok exportálására.
Power BI REST API vagy PowerShell a kapcsolatok listázására.
Power BI jelentés készítése a modell struktúrájáról.
Automatikus UML diagram generálása a kapcsolatok vizualizálására.

Link:

https://github.com/TabularEditor/TabularEditor/releases/tag/2.26.0

https://www.youtube.com/watch?v=O4ATwdzCvWc

IT, BI, DWH, DM, AI

Keresés ebben a blogban