راهنمای جامع زبان M در Power BI از مقدماتی تا پیشرفته

جایگاه زبان M در Power BI

Power BI از ۳ لایه اصلی تشکیل شده است:

1. اتصال و آماده‌سازی داده‌ها (Power Query)
2. مدل‌سازی (Data Model با DAX)
3. بصری‌سازی و گزارش‌دهی

زبان M مخصوص مرحلهٔ Power Query است و کار اصلی آن:

• خواندن منابع داده متنوع
• پاکسازی، تبدیل و ترکیب داده‌ها (ETL)
• تولید خروجی نهایی برای بارگذاری در مدل داده‌ای

سینتکس زبان M و مفاهیم پایه

ساختار let-in

let
    Step1 = …,
    Step2 = Function(Step1),
    Result = Step2
in
    Result

• let بخش تعریف متغیرها و توابع است.
• in خروجی نهایی را مشخص می‌کند.

Expression و ارزش‌گذاری

هر عبارت (Expression) یک مقدار (Value) برمی‌گرداند:

• عددی، متنی، جدول (Table)، لیست (List)، رکورد (Record)، …

عملیات متداول Transform

توابع داخلی و ساخت توابع سفارشی

توابع آماده

• Text.Upper, Text.Lower (تبدیل حروف)
• Date.Year, Date.Month (استخراج بخش از تاریخ)
• List.Sum, List.Average (کار روی لیست)

تابع سفارشی

/// تابعی برای محاسبه درصد رشد
(percentChange as number, previous as number) as number =>
    if previous = 0 then null
    else (percentChange - previous) / previous * 100

• می‌توانید این تابع را در بخش Advanced Editor در Power Query ذخیره و فراخوانی کنید.

مثال پیشرفته با AdventureWorksDW

سناریو: فروش ۲۰۱۳ تا ۲۰۱۵ و محاسبه ماه به ماه

let
    Source = Sql.Database("localhost", "AdventureWorksDW"),
    Sales = Source{[Schema="dbo",Item="FactInternetSales"]}[Data],
    Filtered = Table.SelectRows(Sales, each Date.Year([OrderDate]) >= 2013 and Date.Year([OrderDate]) <= 2015),
    AddedYearMonth = Table.AddColumn(Filtered, "YearMonth", each Date.ToText([OrderDate], "yyyy-MM")),
    Grouped = Table.Group(
        AddedYearMonth,
        {"YearMonth"},
        {{"TotalSales", each List.Sum([SalesAmount]), type number}}
    ),
    Sorted = Table.Sort(Grouped, {"YearMonth", Order.Ascending})
in
    Sorted

خروجی: جدول فروش تجمعی بر اساس YearMonth برای سال‌های ۲۰۱۳ تا ۲۰۱۵.

بهینه‌سازی عملکرد (Performance Tuning)

1. Filter Early: فیلترها را در اولین گام‌ها اعمال کنید تا حجم داده کاهش یابد.
2. Query Folding: تا حد ممکن از قابلیت Fold شدن کوئری به سمت منبع داده (مثلاً SQL Server) استفاده کنید؛ با ابزار View Native Query آزمایش کنید.
3. اجتناب از توابع پیچیده روی ردیف به ردیف: به جای Table.AddColumn با محاسبات فراخوانی‌شونده، سعی کنید از توابع گروهی (Table.Group) استفاده کنید.
4. غیرفعال کردن بارگذاری غیرضروری: مراحل میانی که لازم نیست در گزارش نهایی حضور داشته باشد را غیرفعال کنید (Enable Load = false).

مدیریت خطا و اشکال‌زدایی

• از try … otherwise برای جلوگیری از خطاهای runtime استفاده کنید:

  try Number.FromText("ABC") otherwise null
  

پیشنهاد مطالعه: راهنمای جامع مدیریت خطا با Try … otherwise در زبان M

• برای بررسی نتایج هر مرحله، روی آن راست‌کلیک و View Native Query یا Peek بزنید.
• در Advanced Editor از کامنت استفاده کنید:

  // این بخش برای محاسبه SalesAmount خالص است
  

بهترین شیوه‌ها (Best Practices)

• نام‌گذاری معنادار: مراحل را طوری نام‌گذاری کنید که فقط با خواندن نام متوجه عملکرد شوید.
• مستندسازی کد: برای توابع و بخش‌های مهم از کامنت استفاده کنید.
• تفکیک وظایف: اگر مرحله‌ای خیلی پیچیده است، آن را به چند مرحلهٔ کوچک‌تر تقسیم کنید.
• کنترل ورژن: با Git یا Azure DevOps تغییرات را مدیریت کنید.
• استفاده از Parameter: برای اتصال به سرور/بانک‌داده، آدرس فایل‌ها و فیلترها از پارامترها بهره ببرید.

الگوهای طراحی (Design Patterns)

1. Parameter Table Pattern
   • جدول پارامترها داخل Excel یا SQL تعریف و در Power Query خوانده شود تا تغییرات بدون ویرایش کد اعمال شود.
2. Function Factory Pattern
   • یک ماژول M بسازید که انواع توابع مشابه (مثلاً تبدیل تاریخ) را از روی متادیتا بسازد.
3. Staging + Presentation
   • مرحلهٔ Staging: بارگذاری تمام جدول‌ها و تبدیل اولیه
   • مرحلهٔ Presentation: آماده‌سازی و تجمیع خاص گزارش

نتیجه گیری و گام‌های بعدی

• با اصول و مبانی پیشرفته زبان M آشنا شدید.
• می‌توانید به‌سرعت داده‌ها را فیلتر، تبدیل و تجمیع کنید.
• از الگوها و بهترین شیوه‌ها برای مدیریت پروژه‌ها بهره ببرید.

سوالات متداول (FAQ)

۱. چرا کوئری من Fold نمی‌شود؟
دلیلش ممکن است استفاده از توابع غیرقابل Fold مثل List.Accumulate یا انجام محاسبات ردیف‌به‌ردیف باشد.

۲. چطور به یک API وب با M وصل شوم؟

let
    Source = Web.Contents("https://api.example.com/data", [Headers=[Authorization="Bearer TOKEN"]]),
    Json = Json.Document(Source)
in
    Json

۳. تفاوت Refresh در Power BI Desktop و Service چیست؟
در Desktop رفرش محلی انجام می‌شود. در Service باید Gateways و Credentials تنظیم شده باشد.

۴. آیا می‌توان از M در Azure Data Factory استفاده کرد؟
در Azure Dataflows می‌توانید مراحل Power Query (M) را اجرا کنید، اما در ADF اورجینال نه.

پیشنهاد مطالعه: کتاب مرجع زبان فرمول نویسی M در پاور کوئری

ارتباط و مشاوره

برای اطلاعات بیشتر و مشاوره می‌توانید از طریق زیر با ما در ارتباط باشید:

• تماس ✆ با شرکت لاندا برای مشاوره، اجرا و یا آموزش تخصصی.