Power Query ۲: Unpivot، Merge Queries، Group By و مدیریت Refresh

تغییر شکل داده؛ کاری که فرمول سخت انجامش می‌دهد

بسیاری از فایل‌هایی که به دستتان می‌رسد «برای خواندن» طراحی شده‌اند نه «برای تحلیل»: ماه‌ها در ستون‌ها، نام استان در یک فایل و کد آن در فایل دیگر. Power Query این جدول‌ها را به شکل استاندارد تحلیلی (هر سطر یک رکورد) درمی‌آورد.

Unpivot: از جدول پهن به جدول بلند

جدول هدف فروش ستون نماینده و دوازده ستون فروردین تا اسفند دارد. Pivot و نمودار با این شکل خوب کار نمی‌کنند. در Power Query ستون نماینده را انتخاب کنید و Transform ← Unpivot Other Columns. نتیجه سه ستون است: نماینده، Attribute (ماه) و Value (هدف). ستون‌ها را به Month و Target تغییر نام دهید.

چرا «Other Columns» و نه «Selected Columns»؟ چون اگر سال بعد ستون جدیدی اضافه شود، Unpivot Other Columns آن را هم خودکار می‌چرخاند؛ در حالی که Selected Columns نام دوازده ستون را ثابت در کد نگه می‌دارد.

Merge Queries: VLOOKUP در مقیاس صنعتی

Home ← Merge Queries دو جدول را بر اساس یک یا چند ستون مشترک به هم وصل می‌کند؛ مثلاً اضافه کردن استان هر نماینده از جدول پرسنل، یا سال و ماه شمسی هر تاریخ از جدول تقویم.

کد M یک جریان کامل

let
    Source     = Excel.CurrentWorkbook(){[Name = "tblTargets"]}[Content],
    Unpivoted  = Table.UnpivotOtherColumns(Source, {"Rep"}, "Month", "Target"),
    Merged     = Table.NestedJoin(Unpivoted, {"Rep"}, Reps, {"Rep"}, "RepInfo", JoinKind.LeftOuter),
    Expanded   = Table.ExpandTableColumn(Merged, "RepInfo", {"Province"}),
    Grouped    = Table.Group(Expanded, {"Province", "Month"},
                   {{"Target", each List.Sum([Target]), type number},
                    {"Reps",   each Table.RowCount(_), Int64.Type}})
in
    Grouped

Group By (Transform ← Group By) مثل یک Pivot ثابت است: داده را خلاصه می‌کند تا حجم خروجی کم شود. Append Queries دو جدول هم‌ساختار را زیر هم می‌گذارد (فروش دو کارخانه).

مدیریت Refresh

روی Query در پنل Queries & Connections راست‌کلیک ← Properties: Refresh data when opening the file برای گزارش‌هایی که همیشه باید تازه باشند؛ Refresh every n minutes برای منابع زنده؛ و خاموش کردن Enable background refresh وقتی ترتیب Refresh اهمیت دارد.

نکته‌هایی که کمتر کسی می‌داند

• Merge به فاصله‌ی انتهایی و بزرگی و کوچکی حروف حساس است؛ گزینه‌ی Use fuzzy matching در پنجره‌ی Merge می‌تواند «علی رضائی» و «علی رضایی» را با آستانه‌ی شباهت به هم وصل کند — اما نتیجه را حتماً بازبینی کنید.
• View ← Query Dependencies نمودار ارتباط Queryها و منابعشان را نشان می‌دهد؛ برای فهمیدن فایلی که کس دیگری ساخته، اولین جایی است که باید نگاه کنید.
• خطای «Formula.Firewall» معمولاً از ترکیب منابع با Privacy Level متفاوت است؛ در File ← Options and settings ← Query Options ← Privacy تنظیم سطح حریم یا (در فایل‌های داخلی) Ignore Privacy Levels آن را حل می‌کند.
• روی یک ستون راست‌کلیک ← Keep Errors فقط سطرهای خطادار را نشان می‌دهد؛ سریع‌ترین راه پیدا کردن تاریخ یا عددی که تبدیل نشده.
• بارگذاری در Data Model (Close & Load To ← Add this data to the Data Model) سقف یک میلیون سطر شیت را ندارد و داده را فشرده نگه می‌دارد؛ برای چند میلیون ردیف فروش، مستقیم Pivot را از Data Model بسازید.