Pivot و Unpivot در Power Query؛ تبدیل Excel گزارشی به داده تحلیلی

مقدمه؛ چرا فایل Excel مدیر برای Power BI مناسب نیست؟

یکی از رایج‌ترین فایل‌هایی که به دست تحلیل‌گر می‌رسد این شکلی است: یک ردیف برای محصول و دوازده ستون برای فروردین تا اسفند. برای چشم انسان خوب است، ولی برای مدل تحلیلی معمولاً ساختار مناسبی نیست.

اینجاست که Pivot و مخصوصاً Unpivot به دادمان می‌رسند.

۲۲.۱. Wide و Long Data

۲۲.۲. Unpivot چیست؟

Unpivot چند ستون مشابه را به دو ستون Attribute و Value تبدیل می‌کند.

Product | Jan | Feb
A       | 10  | 20

→
Product | Month | Sales
A       | Jan   | 10
A       | Feb   | 20

۲۲.۳. Unpivot Columns

وقتی دقیقاً می‌دانی کدام ستون‌ها باید تبدیل شوند، همان ستون‌ها را انتخاب و Unpivot Columns می‌کنی.

۲۲.۴. Unpivot Other Columns

در فایل‌هایی که ستون‌های ثابت مثل ProductCode و Branch داریم و ماه‌ها ممکن است زیاد شوند، معمولاً نگه داشتن ستون‌های ثابت و Unpivot Other Columns مقاوم‌تر است.

۲۲.۵. چرا برای فایل ماهانه خوب است؟

اگر ماه جدید به فایل اضافه شود، طراحی Long معمولاً با مدل ستاره‌ای و Date Dimension بهتر جور درمی‌آید.

۲۲.۶. مثال بودجه

CostCenter | 1405-01 | 1405-02 | 1405-03
→
CostCenter | Period | Budget

۲۲.۷. بعد از Unpivot چه کنیم؟

نام Attribute را به Month/Period تغییر بده، Value را به Amount تغییر بده و Data Type هر دو را اصلاح کن.

۲۲.۸. Nullها

بعد از Unpivot ممکن است تعداد زیادی Null ببینی. قبل از حذف مشخص کن Null یعنی «داده ثبت نشده» یا «واقعاً صفر».

۲۲.۹. Grain بعد از Unpivot

قبل از تبدیل شاید هر Row یک Product بود؛ بعد از تبدیل هر Row می‌شود Product-Month. این تغییر Grain باید در طراحی مدل ثبت شود.

۲۲.۱۰. Pivot چیست؟

Pivot برعکس Unpivot است: مقدارهای یک ستون را به Headerهای جدید تبدیل می‌کند.

۲۲.۱۱. Pivot با Aggregate

اگر برای یک Product و Month چند Row وجود داشته باشد، Pivot باید بداند آن‌ها را چطور Aggregate کند؛ Sum، Count یا روش دیگری.

نکته: اگر انتظار داشتی یک مقدار یکتا باشد ولی Pivot از تو Aggregate می‌خواهد، ممکن است Grain داده را اشتباه فهمیده باشی.

۲۲.۱۲. مثال Status

Order | Status | Count
1     | Open   | 5
1     | Closed | 2
→ Pivot Status

۲۲.۱۳. Pivot همیشه برای مدل نیست

گاهی Pivot برای خروجی نمایشی یا یک Transformation میانی خوب است، ولی Fact Table تحلیلی معمولاً ساختار Long را ترجیح می‌دهد.

۲۲.۱۴. Unpivot و Star Schema

وقتی ستون‌های Jan/Feb/Mar را به Period و Amount تبدیل می‌کنی، اتصال به DimDate یا DimPeriod طبیعی‌تر می‌شود.

۲۲.۱۵. فایل‌های چندسال

اگر ستون‌ها مثل Sales_1404_01 باشند، بعد از Unpivot می‌توان Attribute را Split کرد و Year/Month را استخراج کرد.

۲۲.۱۶. مثال شرکت هانا

۲۲.۱۷. Unpivot و Refresh آینده

Transformation خوب باید با فایل ماه بعد هم کار کند. طراحی را فقط برای Snapshot امروز نساز.

۲۲.۱۸. خطای رایج؛ انتخاب ستون‌های ماه به صورت ثابت

اگر هر ماه ستون جدید اضافه می‌شود و Query فقط ۱۲ ستون قدیمی را Unpivot کرده، ماه جدید ممکن است جا بماند. الگوی مناسب را با ساختار منبع انتخاب کن.

۲۲.۱۹. خطای رایج؛ Pivot قبل از فهم Duplicate

Duplicate ممکن است نشانه چند تراکنش واقعی باشد یا مشکل Data Quality. قبل از Aggregate معنی آن را بفهم.

۲۲.۲۰. خطای رایج؛ حذف Null

حذف Null بعد از Unpivot می‌تواند رکوردهای «بدون ثبت» را حذف کند. این تصمیم باید با معنی کسب‌وکار هماهنگ باشد.

۲۲.۲۱. تست خروجی

۲۲.۲۲. تمرین

یک فایل فروش با ستون‌های Product، Jan، Feb، Mar بساز و آن را Unpivot کن. بعد یک Date/Period Key مناسب طراحی کن.

۲۲.۲۳. پروژه فصل

فایل Target شرکت هانا با ۱۲ ستون ماهانه را به FactTarget با Grain «Branch-Month» تبدیل کن و مجموع سال را قبل و بعد تطبیق بده.

جمع‌بندی فصل بیست‌ودوم

Unpivot یکی از مهم‌ترین ابزارها برای تبدیل Excelهای گزارشی به داده تحلیلی است. Pivot هم ابزار خوبی است، ولی باید بدانی خروجی را برای تحلیل می‌خواهی یا نمایش.

Understand grain → Reshape → Fix types → Reconcile totals

فصل بعد سراغ Group By می‌رویم؛ یعنی خلاصه‌سازی داده در Power Query و اینکه چه زمانی Group By درست است و چه زمانی باید محاسبه را به DAX بسپاریم.