Custom Functions و الگوهای حرفه‌ای M؛ ساخت Transformationهای قابل استفاده مجدد

شروع جلسه؛ چرا Function مهم است؟

فرض کن در ۱۵ Query مختلف باید کد محصول را Trim کنی، حروف را استاندارد کنی و مقدار خالی را مدیریت کنی. اگر این منطق را ۱۵ بار Copy/Paste کنی، اولین تغییر کسب‌وکار تبدیل به کابوس می‌شود.

Custom Function یعنی منطق تکراری را یک‌بار تعریف کنیم و هر جا لازم بود با ورودی مناسب صدایش بزنیم.

۲۹.۱. شکل پایه Function

(x as number) as number => x * 1.1

سمت چپ پارامترهای ورودی هستند و سمت راست Expression خروجی.

۲۹.۲. Function با let/in

(qty as number, price as number) as number =>
let
    Sales = qty * price
in
    Sales

وقتی منطق بیشتر از یک Expression ساده است، داخل Function هم می‌توانیم let/in داشته باشیم.

۲۹.۳. Nullable Type

(txt as nullable text) as nullable text =>
    if txt = null then null else Text.Trim(txt)

اگر ورودی واقعاً می‌تواند Null باشد، Type را طوری تعریف کن که رفتار Function روشن باشد.

۲۹.۴. Function پاک‌سازی متن

(txt as nullable text) as nullable text =>
let
    Cleaned = if txt = null then null else Text.Clean(Text.Trim(txt)),
    Standard = if Cleaned = null then null else Text.Upper(Cleaned)
in
    Standard

۲۹.۵. نام‌گذاری Function

۲۹.۶. Invoke Custom Function

در Power Query می‌توان Function را روی یک ستون یا مجموعه فایل‌ها Invoke کرد. برای هر Row ورودی‌های Function از ستون‌های همان Row تأمین می‌شوند.

۲۹.۷. Function روی Table

Table.AddColumn(
    Source,
    "CleanCode",
    each fnNormalizeProductCode([ProductCode]),
    type text
)

۲۹.۸. Function پردازش فایل

(FileContent as binary) as table =>
let
    Source = Excel.Workbook(FileContent, null, true),
    Sales = Source{[Item="Sales",Kind="Table"]}[Data],
    Headers = Table.PromoteHeaders(Sales),
    Typed = Table.TransformColumnTypes(Headers, {{"Amount", type number}})
in
    Typed

۲۹.۹. Function و Combine Files

همان الگویی که Folder Connector می‌سازد در اصل یک Function تبدیل فایل است. حالا می‌توانی آن را بخوانی، Refactor کنی و Validation به آن اضافه کنی.

۲۹.۱۰. Optional Parameter

(amount as number, optional taxRate as number) as number =>
    amount * (1 + (if taxRate = null then 0 else taxRate))

پارامتر Optional برای حالتی خوب است که ورودی دوم همیشه لازم نیست.

۲۹.۱۱. Default منطقی

در M برای Optional Parameter باید رفتار نبود مقدار را خودت مدیریت کنی. Default باید از نظر کسب‌وکار معنی‌دار باشد، نه صرفاً عددی که Error را حذف کند.

۲۹.۱۲. Validation ورودی

(qty as number) as number =>
    if qty < 0 then error "Quantity cannot be negative"
    else qty

گاهی Error دادن بهتر از تولید خروجی ظاهراً سالم ولی غلط است.

۲۹.۱۳. Error Record حرفه‌ای

error [
    Reason = "InvalidQuantity",
    Message = "Quantity cannot be negative",
    Detail = qty
]

Error ساختاریافته Debug و Audit را ساده‌تر می‌کند.

۲۹.۱۴. try روی Function

Result = try fnSafeNumber([AmountText])

نتیجه try اطلاعاتی درباره موفقیت یا خطا می‌دهد و می‌توان بر اساس آن Exception Query ساخت.

۲۹.۱۵. پنهان نکردن Error

الگوی ضعیف این است که همه Errorها را null کنیم. الگوی بهتر این است که Row نامعتبر Flag شود و علتش قابل گزارش باشد.

۲۹.۱۶. Function خروجی Record

(txt as text) as record =>
let
    Parsed = try Number.FromText(txt)
in
    [
        IsValid = not Parsed[HasError],
        Value = if Parsed[HasError] then null else Parsed[Value]
    ]

Function مجبور نیست فقط Text یا Number برگرداند؛ Record خروجی برای Validation خیلی کاربردی است.

۲۹.۱۷. Expand خروجی Record

وقتی Function یک Record برگرداند، می‌توان Fieldهای IsValid و Value را Expand کرد و Rowهای خطادار را جدا دید.

۲۹.۱۸. Function خروجی Table

برای پردازش فایل، API response یا Subset داده، خروجی Table کاملاً طبیعی است.

۲۹.۱۹. Function و Business Rule

Function برای Rule پایدار و تکرارشونده عالی است؛ اما Ruleهایی که کاربران کسب‌وکار مرتب تغییر می‌دهند شاید بهتر باشد در Mapping/Configuration Table نگهداری شوند.

۲۹.۲۰. Function یا Mapping Table؟

۲۹.۲۱. Function یا Query Reference؟

اگر فقط یک Pipeline مرحله‌ای داری، Reference ممکن است کافی باشد. اگر یک Transformation باید با ورودی‌های متفاوت بارها اجرا شود، Function مناسب‌تر است.

۲۹.۲۲. Function پارامتریک برای تاریخ

(tbl as table, startDate as date) as table =>
    Table.SelectRows(tbl, each [OrderDate] >= startDate)

۲۹.۲۳. Higher-order thinking

در M توابع Value هستند؛ یعنی می‌توان آن‌ها را به توابع دیگر داد یا در List/Table Transformationها استفاده کرد. برای شروع کافی است بدانیم این قابلیت پایه بسیاری از الگوهای قدرتمند M است.

۲۹.۲۴. List.Transform با Function

List.Transform({" a "," b "," c "}, each fnCleanText(_))

۲۹.۲۵. Table.TransformColumns

Table.TransformColumns(
    Source,
    {{"ProductCode", fnNormalizeProductCode, type text}}
)

۲۹.۲۶. چند ستون با یک Function

اگر چند ستون منطق پاک‌سازی یکسان دارند، Table.TransformColumns می‌تواند کد را خیلی تمیزتر از AddColumnهای متعدد کند.

۲۹.۲۷. Function Composition

fnNormalize = (txt as nullable text) =>
    fnUpper(fnTrim(fnClean(txt)))

می‌توان Functionهای کوچک را ترکیب کرد؛ ولی زیاده‌روی باعث سخت شدن Debug می‌شود.

۲۹.۲۸. Function کوچک یا بزرگ؟

Function کوچک تست و استفاده مجدد راحت‌تری دارد. Function خیلی بزرگ که کل ETL را پنهان می‌کند ممکن است خوانایی را کم کند. مرز را بر اساس مسئولیت مشخص انتخاب کن.

۲۹.۲۹. Single Responsibility

fnCleanText فقط متن را پاک کند. fnCalculateSales فقط محاسبه فروش را انجام دهد. این جداسازی نگهداری را ساده می‌کند.

۲۹.۳۰. Function Documentation

۲۹.۳۱. Metadata روی Function

در M می‌توان برای Valueها Metadata تعریف کرد. در پروژه‌های پیشرفته‌تر می‌توان Documentation Function را بهتر کرد؛ ولی اصل مهم همان مستندسازی ورودی، خروجی و رفتار خطاست.

۲۹.۳۲. Performance

Function اگر روی میلیون‌ها Row اجرا شود ممکن است هزینه‌بر باشد. هر منطق قابل استفاده مجدد الزاماً باید Row-by-Row اجرا نشود؛ طراحی Source، Folding و نوع Transformation را بررسی کن.

۲۹.۳۳. Function و Query Folding

Custom Function می‌تواند در بعضی سناریوها Folding را پیچیده یا متوقف کند. برای Sourceهای دیتابیسی، View Native Query و Performance واقعی را بررسی کن.

۲۹.۳۴. Buffer؛ با احتیاط

Table.Buffer یا List.Buffer گاهی در سناریوهای خاص مفید است، ولی راه‌حل جادویی Performance نیست و حتی می‌تواند مصرف حافظه را بالا ببرد.

فقط بعد از اندازه‌گیری و فهم Evaluation از Buffer استفاده کن.

۲۹.۳۵. Recursive Function

توابع M می‌توانند Recursive باشند، اما برای آموزش پایه و بیشتر ETLهای روزمره معمولاً لازم نیست. اگر استفاده شد باید Performance و شرط پایان دقیق باشد.

۲۹.۳۶. مثال شرکت هانا؛ fnNormalizeProductCode

(code as nullable text) as nullable text =>
let
    T1 = if code = null then null else Text.Trim(code),
    T2 = if T1 = null then null else Text.Clean(T1),
    T3 = if T2 = null then null else Text.Upper(T2),
    Result = if T3 = "" then null else T3
in
    Result

۲۹.۳۷. استفاده در سه سیستم

همین Function روی فروش، انبار و خرید اجرا می‌شود تا ProductCode قبل از Mapping یک شکل مشترک داشته باشد.

۲۹.۳۸. fnValidateSalesRow

(qty as nullable number, price as nullable number) as record =>
let
    ValidQty = qty <> null and qty >= 0,
    ValidPrice = price <> null and price >= 0,
    Valid = ValidQty and ValidPrice,
    Reason = if not ValidQty then "Invalid quantity"
             else if not ValidPrice then "Invalid price"
             else null
in
    [IsValid=Valid, Reason=Reason]

۲۹.۳۹. Data Quality Pipeline

Validation Function می‌تواند خروجی Record بدهد؛ سپس IsValid و Reason را Expand می‌کنیم. Rowهای معتبر به Fact و نامعتبرها به Exception می‌روند.

۲۹.۴۰. Function برای Schema Check

می‌توان Functionای ساخت که نام ستون‌های واقعی Table را با Required Columns مقایسه کند و Missing Columns را برگرداند.

۲۹.۴۱. نمونه Schema Check

(tbl as table, required as list) as list =>
    List.Difference(required, Table.ColumnNames(tbl))

۲۹.۴۲. استفاده در فایل‌ها

برای هر فایل، Function Schema Check اجرا می‌شود. اگر List خروجی خالی نباشد، فایل قبل از ورود به Fact Reject می‌شود.

۲۹.۴۳. Function برای Audit

Function می‌تواند Row Count، Total Amount و Status فایل را در قالب Record برگرداند تا Audit Table ساخته شود.

۲۹.۴۴. تست Function

Function را فقط روی Dataset بزرگ تست نکن. چند ورودی مشخص بساز: مقدار عادی، Null، Empty، مقدار مرزی و مقدار خراب.

۲۹.۴۵. Test Cases

۲۹.۴۶. Regression Test ذهنی

وقتی Function را تغییر می‌دهی، بررسی کن Queryهای فروش، انبار و خرید که به آن وابسته‌اند چه اثری می‌گیرند.

۲۹.۴۷. Dependency خطرناک

Function مشترک قدرت زیادی دارد؛ تغییر کوچک در آن می‌تواند چندین Query را تغییر دهد. بنابراین Versioning و Review منطق مشترک مهم است.

۲۹.۴۸. Hard-code داخل Function

اگر fnTransformFile مسیر شخصی یا نام Server ثابت داشته باشد، Reusability کم می‌شود. Configuration را با Parameter جدا کن.

۲۹.۴۹. Function و Parameters

fnTransformSales(FileContent, pCulture, pCurrencyUnit)

Global Configuration و Function Input را قاطی نکن؛ مشخص کن کدام مقدار تنظیم محیط است و کدام برای هر Call فرق می‌کند.

۲۹.۵۰. خطای رایج؛ Function عظیم

Function صدخطی که Source، پاک‌سازی، Mapping، Business Rule و Audit را همه با هم انجام می‌دهد، نگهداری را سخت می‌کند.

۲۹.۵۱. خطای رایج؛ Copy/Paste

اگر یک منطق سه بار تکرار شد، حداقل بررسی کن آیا Function یا Mapping مشترک ارزش دارد.

۲۹.۵۲. خطای رایج؛ Null Behavior نامعلوم

Function باید مشخص کند با Null چه می‌کند. Error؟ Null؟ Default؟ این تصمیم بخشی از قرارداد Function است.

۲۹.۵۳. خطای رایج؛ Error Swallowing

otherwise null بدون Flag می‌تواند داده خراب را پنهان کند. برای ETL سازمانی، Exception Visibility مهم است.

۲۹.۵۴. الگوی پیشنهادی Function

Validate inputs
→ Normalize
→ Transform
→ Validate result
→ Return typed output

۲۹.۵۵. Code Review Checklist

۲۹.۵۶. تمرین پایه

fnCalculateSales بساز که Quantity و UnitPrice را بگیرد و Amount را برگرداند. برای Quantity منفی Error تولید کن.

۲۹.۵۷. تمرین Text

fnCleanText بساز که Null را حفظ کند، Text.Trim و Text.Clean را اجرا کند و خروجی را Uppercase کند.

۲۹.۵۸. تمرین Record Output

fnParseNumber بساز که Text بگیرد و Record شامل IsValid، Value و ErrorMessage برگرداند.

۲۹.۵۹. تمرین Schema

Functionای بساز که Table و List ستون‌های Required را بگیرد و لیست Missing Columns را برگرداند.

۲۹.۶۰. تمرین فایل

fnTransformSalesFile را طراحی کن که Binary فایل را بگیرد، Table فروش را استخراج کند، Header و Typeها را استاندارد کند و Table برگرداند.

۲۹.۶۱. تمرین چالشی

Pipelineای طراحی کن که هر فایل ابتدا Schema Validation شود؛ فایل نامعتبر به Reject و فایل معتبر به fnTransformSalesFile ارسال شود.

۲۹.۶۲. پروژه Session؛ Reusable ETL Library شرکت هانا

هدف این Session ساخت یک کتابخانه کوچک M است که Queryهای فروش، خرید و انبار بتوانند از منطق مشترک استفاده کنند؛ بدون Copy/Paste و با Error Handling قابل مشاهده.

جمع‌بندی Session 29

Custom Function فقط برای کوتاه کردن کد نیست؛ برای استاندارد کردن منطق، کاهش خطا و ساخت ETL قابل نگهداری است.

Reusable logic + clear contracts + visible errors = maintainable M

Session 30 را وارد Data Modeling حرفه‌ای می‌کنیم: Fact و Dimension، Star Schema، Grain، Cardinality و Relationshipها؛ جایی که Power BI از یک ابزار گزارش‌سازی به یک مدل تحلیلی واقعی تبدیل می‌شود.