زبان M در Power Query؛ از کلیک‌کردن تا کنترل واقعی Query

شروع جلسه؛ M قرار نیست بترساندت

تا اینجا خیلی از کارهای Power Query را با دکمه‌ها انجام دادیم. نکته جالب این است که پشت تقریباً همه این کلیک‌ها، Power Query برای ما کد M می‌نویسد. پس تو از قبل با M کار کرده‌ای؛ فقط مستقیم با کدش حرف نزده‌ای.

هدف این Session این نیست که یک‌شبه برنامه‌نویس M شوی. هدف این است که کد Query را بخوانی، تغییر بدهی، Debug کنی و وقتی رابط گرافیکی کم آورد، خودت کنترل را دست بگیری.

۲۸.۱. زبان M چیست؟

M زبان فرمول‌نویسی و Transformation در Power Query است. Source را می‌گیرد، Stepهای تبدیل را اجرا می‌کند و در نهایت یک Value مثل Table تحویل می‌دهد.

۲۸.۲. Advanced Editor

از Advanced Editor می‌توانی کل کد M یک Query را ببینی. اولین بار ممکن است شلوغ به نظر برسد، ولی معمولاً همان Applied Steps است که به شکل متن نوشته شده.

۲۸.۳. let و in

let
    Source = ...,
    Cleaned = ...,
    Filtered = ...
in
    Filtered

بین let و in Stepها تعریف می‌شوند. بعد از in مشخص می‌کنیم خروجی نهایی Query کدام Step باشد.

۲۸.۴. Step یعنی متغیر نام‌دار

هر Step یک اسم دارد و نتیجه یک Expression را نگه می‌دارد. Step بعدی معمولاً از نتیجه Step قبلی استفاده می‌کند.

Source = Excel.Workbook(...),
Sales = Source{[Item="Sales",Kind="Table"]}[Data]

۲۸.۵. چرا بین Stepها ویرگول داریم؟

تعریف‌های داخل let با کاما از هم جدا می‌شوند. آخرین تعریف قبل از in معمولاً کامای انتهایی ندارد.

۲۸.۶. نام Step با فاصله

#"Changed Type" = Table.TransformColumnTypes(Source, {{"Amount", type number}})

اگر نام Step فاصله یا کاراکتر خاص داشته باشد، معمولاً با #"..." نوشته می‌شود.

۲۸.۷. Expression چیست؟

سمت راست مساوی یک Expression است؛ یعنی چیزی که ارزیابی می‌شود و Value تولید می‌کند. این Value می‌تواند Text، Number، Date، List، Record، Table یا Function باشد.

۲۸.۸. Valueها

۲۸.۹. List

{10, 20, 30, 40}

List مجموعه‌ای مرتب از Valueهاست. خیلی از عملیات M در پشت صحنه با List کار می‌کنند.

۲۸.۱۰. دسترسی به عضو List

MyList = {"A","B","C"},
First = MyList{0}

Index در M از صفر شروع می‌شود؛ پس عضو اول Index صفر دارد.

۲۸.۱۱. Record

Customer = [Name="Hana", City="Tehran", Active=true]

Record شبیه یک مجموعه Field نام‌دار است.

۲۸.۱۲. دسترسی به Field

Customer[City]

این Expression مقدار Field شهر را از Record می‌گیرد.

۲۸.۱۳. Table

Table مجموعه‌ای از ستون‌ها و ردیف‌هاست و بیشتر Transformationهای Power Query با توابع خانواده Table.* انجام می‌شوند.

۲۸.۱۴. Table.FromRecords

Table.FromRecords({
    [Product="A", Qty=10],
    [Product="B", Qty=20]
})

۲۸.۱۵. توابع Table.*

۲۸.۱۶. مثال Filter

Filtered = Table.SelectRows(Source, each [SalesAmount] > 1000000)

each یک شکل کوتاه برای نوشتن Function روی هر Row است.

۲۸.۱۷. each یعنی چه؟

each [Quantity] * [UnitPrice]

می‌توانی ذهنی بخوانی: «برای هر Row، Quantity ضربدر UnitPrice را حساب کن».

۲۸.۱۸. Table.AddColumn

AddedSales = Table.AddColumn(
    Source,
    "Sales",
    each [Quantity] * [UnitPrice],
    type number
)

۲۸.۱۹. Type را صریح بنویس

وقتی ستون جدید می‌سازی، اگر Type را می‌دانی بهتر است مشخصش کنی. این کار Query را خواناتر و قابل پیش‌بینی‌تر می‌کند.

۲۸.۲۰. Text Functions

Text.Trim([CustomerName])
Text.Upper([Code])
Text.Start([ProductCode], 3)
Text.Combine({[City],[Branch]}, " - ")

۲۸.۲۱. Number Functions

Number.Round([Amount], 0)
Number.Abs([Variance])

۲۸.۲۲. Date Functions

Date.Year([OrderDate])
Date.Month([OrderDate])
Date.StartOfMonth([OrderDate])

۲۸.۲۳. if then else

if [SalesAmount] >= 10000000 then "High"
else if [SalesAmount] >= 5000000 then "Medium"
else "Low"

۲۸.۲۴. شرط Null

if [Discount] = null then 0 else [Discount]

ولی یادت باشد Null را فقط وقتی صفر کن که معنی کسب‌وکاری واقعاً همین باشد.

۲۸.۲۵. try ... otherwise

try Number.FromText([AmountText]) otherwise null

برای کنترل خطا مفید است، اما نباید Errorهای Data Quality را بی‌صدا دفن کند.

۲۸.۲۶. Error Record

در طراحی حرفه‌ای‌تر می‌توان نتیجه try را بررسی کرد تا بفهمیم تبدیل موفق بوده یا خطا رخ داده و Exception جدا بسازیم.

۲۸.۲۷. Function چیست؟

Function ورودی می‌گیرد و خروجی می‌دهد. وقتی یک منطق را چند بار تکرار می‌کنی، Function می‌تواند کد را تمیزتر کند.

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

۲۸.۲۸. Function با دو ورودی

(qty as number, price as number) as number => qty * price

۲۸.۲۹. Function سفارشی برای پاک‌سازی

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

۲۸.۳۰. چرا Function؟

اگر یک Rule را در ۲۰ Query Copy/Paste کنی، تغییر آینده دردسر می‌شود. Function می‌تواند منطق مشترک را یکجا نگه دارد.

۲۸.۳۱. Scope

Stepهای تعریف‌شده داخل let در همان Expression قابل استفاده‌اند. نام‌گذاری واضح باعث می‌شود مسیر داده را راحت‌تر دنبال کنی.

۲۸.۳۲. نام‌گذاری بد و خوب

۲۸.۳۳. Comments

// Remove invalid orders
Filtered = Table.SelectRows(Source, each [IsValid] = true)

برای منطق غیرواضح Comment کوتاه مفید است؛ ولی اسم Step خوب از Comment زیاد بهتر است.

۲۸.۳۴. Query Dependencies

یک Query می‌تواند خروجی Query دیگری را Source خودش قرار دهد. این الگو برای Staging و معماری لایه‌ای بسیار مفید است.

۲۸.۳۵. Reference و Duplicate

Reference به Query موجود وابسته می‌ماند؛ Duplicate یک کپی مستقل از منطق فعلی می‌سازد. انتخاب درست روی نگهداری اثر دارد.

۲۸.۳۶. الگوی Staging

stg_SalesRaw
→ stg_SalesClean
→ FactSales

Queryهای Raw را کم‌دستکاری نگه دار و Transformationهای کسب‌وکاری را در لایه‌های مشخص انجام بده.

۲۸.۳۷. M و Query Folding

بعضی Stepهای M می‌توانند به Source ترجمه شوند و همان‌جا اجرا شوند. بعضی Stepها Folding را متوقف می‌کنند.

در داده حجیم ترتیب Stepها را با Query Folding و Performance Analyzerهای مناسب بررسی کن.

۲۸.۳۸. Native Query؛ احتیاط

نوشتن SQL دستی گاهی مفید است، ولی می‌تواند Portability، Folding یا Governance را تحت تأثیر قرار دهد. تصمیم باید معماری‌محور باشد.

۲۸.۳۹. Lazy Evaluation؛ نگاه ساده

در M الزاماً هر چیزی که تعریف کرده‌ای همان لحظه به همان شکل سنتی اجرا نمی‌شود. Engine بر اساس نیاز Expressionها را ارزیابی می‌کند.

برای شروع لازم نیست وارد جزئیات نظری شویم؛ فقط بدان ترتیب ظاهری کد همیشه تصویر کامل Performance نیست.

۲۸.۴۰. Immutable Thinking

در هر Step معمولاً جدول قبلی را «ویرایش درجا» نمی‌کنیم؛ یک Value جدید از قبلی می‌سازیم. این طرز فکر خواندن M را خیلی ساده‌تر می‌کند.

۲۸.۴۱. Debug کردن Step به Step

در Power Query روی Applied Stepها کلیک کن و خروجی هر مرحله را ببین. اگر خطا از Step هفتم شروع شده، لازم نیست کل Query را حدس بزنی.

۲۸.۴۲. Formula Bar

Formula Bar برای تغییر یک Step خاص عالی است. Advanced Editor برای دیدن معماری کل Query مناسب‌تر است.

۲۸.۴۳. خطای Expression.Error

وقتی Field یا Column پیدا نشود، Type ناسازگار باشد یا Function ورودی اشتباه بگیرد، معمولاً Error قابل بررسی می‌بینی. متن Error را بخوان؛ خیلی وقت‌ها دقیقاً سرنخ را می‌دهد.

۲۸.۴۴. Missing Column

Table.SelectColumns(Source, {"OrderNo","Amount"})

اگر Amount در Source حذف شود، این Step ممکن است Fail شود. برای Schema Drift باید سیاست مشخص داشته باشی.

۲۸.۴۵. MissingField.UseNull

Table.SelectColumns(
    Source,
    {"OrderNo","Amount"},
    MissingField.UseNull
)

این رفتار برای ستون اختیاری مفید است؛ برای ستون حیاتی ممکن است بهتر باشد عمداً Error بدهیم.

۲۸.۴۶. List.Transform

List.Transform({1,2,3}, each _ * 10)
→ {10,20,30}

علامت _ در شکل کوتاه Function به مقدار جاری اشاره می‌کند.

۲۸.۴۷. List.Contains

List.Contains({"DEV","TEST","PROD"}, pEnvironment)

همان الگویی که در Session قبل برای Validate کردن Environment دیدیم.

۲۸.۴۸. Record.Field

Record.Field([Name="Hana",City="Tehran"], "City")

۲۸.۴۹. ساخت Table با #table

#table(
    {"Code","Name"},
    {{"A","Active"},{"I","Inactive"}}
)

۲۸.۵۰. Mapping کوچک در M

برای Mapping خیلی کوچک می‌توان Table داخل M ساخت؛ ولی Mapping کسب‌وکاری بزرگ بهتر است منبع قابل مدیریت داشته باشد.

۲۸.۵۱. مثال شرکت هانا؛ پاک‌سازی فروش

let
    Source = stg_SalesRaw,
    ValidRows = Table.SelectRows(Source, each [OrderNo] <> null),
    CleanProduct = Table.TransformColumns(
        ValidRows,
        {{"ProductCode", each Text.Upper(Text.Trim(_)), type text}}
    ),
    AddedSales = Table.AddColumn(
        CleanProduct,
        "SalesAmount",
        each [Quantity] * [UnitPrice],
        type number
    )
in
    AddedSales

۲۸.۵۲. کد بالا را فارسی بخوانیم

اول داده خام فروش را می‌گیریم. بعد ردیف بدون شماره سفارش را کنار می‌گذاریم. کد محصول را Trim و Upper می‌کنیم. سپس مبلغ فروش را از تعداد ضربدر قیمت می‌سازیم و در پایان AddedSales را خروجی می‌دهیم.

۲۸.۵۳. تبدیل کد UI به کد قابل فهم

Power Query ممکن است Stepهایی با نام خودکار بسازد. بعد از اینکه Query درست شد، Stepهای مهم را Rename کن تا کد مثل داستان خوانده شود.

۲۸.۵۴. M Code Review

۲۸.۵۵. خطای رایج؛ کد خیلی فشرده

اینکه بتوانی کل Query را در سه خط بنویسی الزاماً حرفه‌ای نیست. خوانایی و قابلیت Debug برای تیم مهم‌تر است.

۲۸.۵۶. خطای رایج؛ کپی Function از اینترنت

کدی که معنی‌اش را نمی‌فهمی وارد Production نکن. اول ورودی، خروجی، Error behavior و اثر Performance را بفهم.

۲۸.۵۷. خطای رایج؛ try otherwise null همه‌جا

این کار Query را ظاهراً بدون خطا می‌کند، ولی ممکن است هزاران مقدار خراب را به Null تبدیل کند.

۲۸.۵۸. خطای رایج؛ منطق کسب‌وکاری در ده Query

اگر Rule «کد شعبه استاندارد» در ده جا تکرار شده، احتمال ناسازگاری بالا می‌رود. Mapping یا Function مشترک را بررسی کن.

۲۸.۵۹. Best Practiceها

کد را Step به Step و خوانا بنویس.

نام Stepها را معنی‌دار کن.

Type ورودی و خروجی Functionها را تا جای مناسب مشخص کن.

Hard-code محیطی را با Parameter مدیریت کن.

Errorها را پنهان نکن؛ مدیریت کن.

منطق تکراری را Function یا Mapping کن.

قبل و بعد Transformation، Grain و Total را Validate کن.

Advanced Editor را ابزار فهم Query بدان، نه محل جادو.

۲۸.۶۰. تمرین پایه

یک Query با let/in بساز که Table کوچک فروش را ایجاد کند، فقط Amountهای بیشتر از 1000 را نگه دارد و خروجی Filtered را برگرداند.

۲۸.۶۱. تمرین List

List شامل 10، 20، 30 و 40 بساز. با List.Transform همه را 5 درصد افزایش بده.

۲۸.۶۲. تمرین Record

Record مشتری با Name، City و Segment بساز و فقط City را از آن استخراج کن.

۲۸.۶۳. تمرین Function

Functionای بنویس که Quantity و UnitPrice را بگیرد و SalesAmount را برگرداند.

۲۸.۶۴. تمرین Data Quality

Function تبدیل Text به Number بنویس، ولی به جای پنهان کردن خطا، خروجی‌ای طراحی کن که بتوانی Rowهای نامعتبر را جدا شناسایی کنی.

۲۸.۶۵. تمرین چالشی

Query فروش را طوری Refactor کن که پاک‌سازی ProductCode در یک Function جدا باشد و سه Query مختلف بتوانند از همان Function استفاده کنند.

۲۸.۶۶. پروژه Session؛ M Refactoring شرکت هانا

هدف پروژه این است که یک Query کلیکی شلوغ را به M خوانا، قابل تست و قابل نگهداری تبدیل کنی.

جمع‌بندی Session 28

اگر فقط یک نکته از این جلسه یادت بماند، این باشد: M چیزی جدا از Power Query نیست؛ همان کاری است که تا امروز با UI انجام می‌دادی، فقط حالا زبان پشت آن را می‌فهمی.

let
    Understand = "steps",
    Refactor = "repeated logic",
    Validate = "data quality"
in
    BetterPowerQuery

در Session 29 می‌توانیم وارد Custom Functions و الگوهای حرفه‌ای M شویم؛ Functionهای پارامتریک، پردازش فایل، Reusable Transformations و Error Handling ساختاریافته.