Power Bi alapok: M nyelven naptár tábla létrehozás

Power Bi alapok: M nyelv naptár tábla létrehozás

Általános elv a tábla létrehozásra

Kezdeti adatok létrehozása
- Használhatunk listákat (List.Numbers, List.Dates) vagy közvetlen adatforrásokat (Excel, SQL, stb.).
- A fenti példában egy számlistát generálunk (List.Numbers), amely a dátumokat számként reprezentálja.
Lista transzformálása táblává
- List.Transform segítségével az egyes elemeket átalakíthatjuk (pl. számokat dátummá).
- Table.FromList függvénnyel a lista táblává alakítható.
Oszlopok hozzáadása
- A Table.AddColumn függvény iterál a táblán, és minden egyes sorhoz egy új számított értéket rendel.
- Ilyenek például az év (Date.Year), hónap (Date.Month), hét napja (Date.DayOfWeek).
Dátumformázások és kombinációk
- A hónap és nap szövegként történő megjelenítése (Date.ToText).
- Az év és hónap egyedi azonosító formában (YYYY-MM), ami az időalapú jelentésekhez kiváló.

Műveleti összefüggések és sorrend

Az egymásra épülő műveletek miatt a függvények sorrendje kulcsfontosságú. Például:

✅ Helyes sorrend:

Adatok generálása (List.Numbers)
Lista átalakítása dátumokká (List.Transform)
Táblázattá konvertálás (Table.FromList)
Oszlopok hozzáadása (év, hónap, hét napja)
Szöveges és numerikus oszlopok kombinációja

❌ Helytelen sorrend:

Ha a Date.Year függvényt a lista formázása előtt alkalmazzuk, az hibát eredményezhet.
Ha egy szövegformázási lépés korábban történik, adatvesztés vagy konverziós hiba fordulhat elő.

Power Query specialitásai és a legjobb megoldás elvei

Egymásra épülő műveletek (Step-by-Step logika)
- Minden lépés egy előző állapotot módosít, így az adattranszformáció olvasható marad.
- Az in utasítás a végső értéket adja vissza.
Listák hatékony kezelése
- A Power Query M nyelv listaalapú, így az egyes elemeket közvetlenül átalakíthatjuk.
- Példa: List.Transform(DaysInRange, each #date(StartYear, 1, 1) + _)
Elkerülendő hibák
- Ha egy oszlophoz helytelen adattípust adunk (type text helyett Int64.Type), az összes művelet hibás lehet.
- A dátumoknál ügyelni kell arra, hogy időzónákat ne változtassunk meg szükségtelenül.
Optimalizációs elvek
- Késői konverzió: a számokat érdemes az utolsó pillanatban dátummá alakítani, hogy a számítási teljesítmény jobb maradjon.
- Minimalista adatkészlet: csak a szükséges oszlopokat hozzuk létre (pl. YearMonth kombinálja az évet és hónapot, így nem szükséges külön mezők létrehozása).

Összegzés és ajánlott gyakorlatok

📌 Használj listaalapú számításokat, mert gyorsabbak és rugalmasabbak.
📌 Kövess logikus lépésenkénti adattranszformációt, az összefüggések átláthatósága érdekében.
📌 Tartsd az adattípusokat konzisztensen, és ügyelj a dátumformátumokra.
📌 Optimalizálj: csak azokat az oszlopokat használd, amelyek szükségesek az elemzéshez.

Naptár generálás

Ez a Power Query M kód egy dátumtáblát hoz létre 2010. január 1. és 2030. december 31. között.

A következő oszlopokat tartalmazza:

Date (dátum)
Year (év)
Month (hónap száma)
Day (nap száma)
Weekday (hét napja szám szerint, hétfő = 1)
WeekdayName (hét napja név szerint)
MonthName (hónap neve)
Quarter (negyedév száma)
YearMonth (ÉÉÉÉ-HH formátumú azonosító)

A Power Query M kód, amely egy naptár táblát generál 2010-től 2030-ig Power BI-ban:

---- 1. dátum lista alapján

let
StartDate = #date(2010, 1, 1),
EndDate = #date(2030, 12, 31),
DateList = List.Dates(StartDate, Number.From(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
AddYear = Table.AddColumn(TableFromList, "Year", each Date.Year([Date]), Int64.Type),
AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date]), Int64.Type),
AddDay = Table.AddColumn(AddMonth, "Day", each Date.Day([Date]), Int64.Type),
AddWeekday = Table.AddColumn(AddDay, "Weekday", each Date.DayOfWeek([Date]) + 1, Int64.Type),
AddWeekdayName = Table.AddColumn(AddWeekday, "WeekdayName", each Date.ToText([Date], "dddd"), type text),
AddMonthName = Table.AddColumn(AddWeekdayName, "MonthName", each Date.ToText([Date], "MMMM"), type text),
AddQuarter = Table.AddColumn(AddMonthName, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
AddYearMonth = Table.AddColumn(AddQuarter, "YearMonth", each Text.PadStart(Text.From([Year]), 4, "0") & "-" & Text.PadStart(Text.From([Month]), 2, "0"), type text)
in
AddYearMonth

---- 2. számlista alapján

let

StartYear = 2010,

EndYear = 2030,

DaysInRange = List.Numbers(0, Number.From(#date(EndYear, 12, 31) - #date(StartYear, 1, 1)) + 1, 1),

DateList = List.Transform(DaysInRange, each #date(StartYear, 1, 1) + _),

TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

AddYear = Table.AddColumn(TableFromList, "Year", each Date.Year([Date]), Int64.Type),

AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date]), Int64.Type),

AddDay = Table.AddColumn(AddMonth, "Day", each Date.Day([Date]), Int64.Type),

AddWeekday = Table.AddColumn(AddDay, "Weekday", each Date.DayOfWeek([Date]) + 1, Int64.Type),

AddWeekdayName = Table.AddColumn(AddWeekday, "WeekdayName", each Date.ToText([Date], "dddd"), type text),

AddMonthName = Table.AddColumn(AddWeekdayName, "MonthName", each Date.ToText([Date], "MMMM"), type text),

AddQuarter = Table.AddColumn(AddMonthName, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),

AddYearMonth = Table.AddColumn(AddQuarter, "YearMonth", each Text.PadStart(Text.From([Year]), 4, "0") & "-" & Text.PadStart(Text.From([Month]), 2, "0"), type text)

AddYearMonth

Ez a verzió egy számlistát hoz létre, amelyet az #date(2010,1,1) dátumtól kezdve számol. Minden egyes számhoz egy napot adunk hozzá, így generálva a teljes dátumtartományt.

Speciális változó használat

A _ (underscore vagy aláhúzás) egy speciális változó Power Query M nyelvben, amely a jelenlegi iterált elemre utal egy függvényen vagy lekérdezésen belül. Főként listák és táblázatok egyes sorainak feldolgozásakor használjuk.

Hogyan működik az `_` változó?

Amikor egy iteratív függvényt hívunk meg, például List.Transform vagy Table.AddColumn, a _ változó az aktuális elemként viselkedik, amelyen a művelet végrehajtásra kerül.

🔹 Egyszerű példa List.Transform esetén:

---m

let
    Numbers = {1, 2, 3, 4, 5},
    SquaredNumbers = List.Transform(Numbers, each _ * _)  
in  
    SquaredNumbers

Eredmény: {1, 4, 9, 16, 25}
➡ Az _ változó itt az aktuális listaelemre utal, és minden elem négyzetét veszi.

Példa dátumgenerálásra `_` használatával

A következő kód List.Numbers segítségével számlistát generál, majd _ változót használ a dátumok létrehozására:

---m

let
    StartDate = #date(2020, 1, 1),
    DaysList = List.Numbers(0, 10),  
    DateList = List.Transform(DaysList, each StartDate + _)
in  
    DateList

Eredmény:
{#date(2020,1,1), #date(2020,1,2), ..., #date(2020,1,10)}

➡ Az _ változó itt az aktuális számot jelöli a listában, amelyet hozzáadunk a kezdődátumhoz.

Használat `Table.AddColumn` esetén

A táblázatkezelésnél _ segít egy adott sor oszlopértékeit manipulálni.

---m

let
    SourceTable = #table({"Number"}, {{1}, {2}, {3}, {4}}),
    TableWithSquares = Table.AddColumn(SourceTable, "Squared", each _[Number] * _[Number])
in  
    TableWithSquares

Eredmény:

Number	Squared
1	1
2	4
3	9
4	16

➡ _ az aktuális sort képviseli, és [Number] segítségével elérjük az adott oszlop értékét.

Mikor NEM használható az `_`?

Összetett függvényekben, ahol az aktuális elem egy táblázat vagy rekord.

Ha egy táblázatot kell módosítanod, inkább explicit változónevet használj.

Rossz példa:

---m

Table.AddColumn(MyTable, "NewCol", each Table.AddColumn(_, "Extra", each [Value] * 2))

Jó példa:

---m

Table.AddColumn(MyTable, "NewCol", each Table.AddColumn(_[TableColumn], "Extra", each [Value] * 2))

Amikor egy függvényen belül több szintű műveletet végzünk.
- Ebben az esetben nevezzünk meg egy lokális változót (CurrentRow).

Összegzés és best practice

✅ Használható egyszerű transzformációkhoz, mint List.Transform vagy Table.AddColumn.
✅ Olvashatóbbá teszi az egyszerű függvényeket, ahol csak egy aktuális elem van.
✅ Kombinálható más függvényekkel (Date, Text, Number műveletekkel).

⚠ Kerüld összetett adatszerkezetek esetén (pl. táblázatoknál), ahol explicitebb változónévre van szükség.

Minta

A DateTime.FromText(_) függvényhívás a Power Query M nyelvben az _ változót (az aktuális elemet) alakítja át egy dátum-idő (DateTime) formátummá.

Hogyan működik?

Ha ezt egy iterációs függvényen belül használjuk (pl. List.Transform vagy Table.AddColumn), akkor _ az aktuális elemet képviseli, és a DateTime.FromText(_) megpróbálja azt átalakítani dátummá.

🔹 Példa listával:

---m

let
    TextDates = {"2024-03-31 12:00:00", "2025-01-01 08:30:15"},
    DateTimeList = List.Transform(TextDates, each DateTime.FromText(_))
in
    DateTimeList

Eredmény:
{#datetime(2024,3,31,12,0,0), #datetime(2025,1,1,8,30,15)}

➡ A List.Transform végigmegy a TextDates listán, és minden elemnél _ az aktuális szöveges dátum. Ezután a DateTime.FromText(_) alakítja át valódi datetime formátummá.

🔹 Példa táblázattal:

Ha egy oszlop szöveges dátumokat tartalmaz ("2024-03-31 12:00:00" formátumban), akkor egy új oszlopot így hozhatunk létre:

---m

let
    SourceTable = #table({"TextDate"}, {{"2024-03-31 12:00:00"}, {"2025-01-01 08:30:15"}}),
    ConvertedTable = Table.AddColumn(SourceTable, "ConvertedDateTime", each DateTime.FromText(_[TextDate]), type datetime)
in
    ConvertedTable

Eredmény:

TextDate	ConvertedDateTime
"2024-03-31 12:00:00"	2024-03-31 12:00:00
"2025-01-01 08:30:15"	2025-01-01 08:30:15

➡ _ itt az aktuális sor, ezért [TextDate]-et kell használni az oszlop értékének eléréséhez!

Hibalehetőségek

⚠ Hibát dob, ha az érték nem megfelelő dátumformátumú.

Pl. "31-03-2024 12:00:00" hibás lehet, ha nincs megfelelő lokalizációs beállítás.

Érdemes használni az ellenőrzést:

---m

try DateTime.FromText(_) otherwise null

⚠ Ha _ egy teljes sor, akkor [OszlopNév] szükséges.

Rossz: Table.AddColumn(MyTable, "NewCol", each DateTime.FromText(_))
Jó: Table.AddColumn(MyTable, "NewCol", each DateTime.FromText(_[TextDate]))

Összegzés

✅ A DateTime.FromText(_) egy szöveges dátumot alakít át DateTime típusúvá.
✅ Iterációs függvényekben _ az aktuális elemként viselkedik.
✅ Táblázatok esetén _ egy teljes sorra mutathat, ezért oszlopnév kell (_[ColumnName]).
✅ Hibakezelést érdemes beépíteni, ha az adatok nem biztosan megfelelő formátumúak.

IT, BI, DWH, DM, AI

Keresés ebben a blogban