راهنمای جامع و علمی Pivot و Unpivot در Power Query

Power Query ابزار قدرتمندی‌ست با قابلیت‌های Pivot و Unpivot، که امکان بازآرایی ساختار داده‌ها را در کوتاه‌ترین زمان و با کمترین کدنویسی فراهم می‌کند. در این مقاله علاوه بر مفاهیم پایه، به جزئیات فنی، بررسی عملکرد داخلی، سناریوهای پیشرفته، نکات بهینه‌سازی و مثال‌های عملی از دیتابیس AdventureWorksDW می‌پردازیم.

چرا Pivot/Unpivot اهمیت دارد؟

• یکنواختی فرمت داده: بسیاری از توابع آماری و بصری‌سازی در Power BI یا دیگر ابزارها تنها با داده‌های Long Format (حالتی که هر سطر یک رکورد منحصربه‌فرد است) کار می‌کنند. Unpivot این ساختار را تضمین می‌کند.
• گزارش‌های مقایسه‌ای: Pivot با چیدمان عرضیِ داده‌ها، مقایسه‌ ستونی (مثلاً مقایسه فروش ماهانه) را ساده می‌کند.
• خوانایی و نگهداری ساده‌تر: داشبوردهای مدیریتی با جداول Pivoted حرفه‌ای‌تر به نظر می‌رسند و کلاینت‌ها راحت‌تر متوجه روندها می‌شوند.

مفاهیم پایه

عملکرد داخلی در Power Query

Table.Pivot

• ورودی: یک جدول، نام ستون کلید (Key Column)، نام ستون مقدار (Value Column).
• مکانیزم: ابتدا داده را گروه‌بندی می‌کند، سپس برای هر مقدار یکتا در کلید، یک ستون جدید ایجاد و مقادیر مربوطه را قرار می‌دهد.
• پیچیدگی: O(n·k) که n تعداد سطرها و k تعداد مقادیر یکتا در کلید است.

Table.UnpivotOtherColumns / UnpivotColumns

• ورودی: لیستی از ستون‌هایی که می‌خواهید Unpivot شوند یا لیستی از ستون‌های باقیمانده.
• مکانیزم: با چرخش ماتریسی ستون→سطر، خروجی را می‌سازد.
• پیچیدگی: O(n·m)، m تعداد ستون‌های Unpivot شده.

مثال‌های عملی با AdventureWorksDW

آماده‌سازی داده

let Source = Sql.Database(“YourServer”, “AdventureWorksDW2019″), SalesFact = Source{[Schema=”dbo”,Item=”FactResellerSales”]}[Data], FilteredColumns = Table.SelectColumns(SalesFact, {“SalesTerritoryKey”,”OrderDateKey”,”SalesAmount”}) in FilteredColumns

مثال Pivot (گزارش فروش ماهانه)

1. 1. تبدیل OrderDateKey به ماه و سال:

AddDate = Table.AddColumn(FilteredColumns, “Month”, each Date.MonthName(Date.FromText(Text.From([OrderDateKey]))), type text)

1. ‌Pivot کردن:

PivotedTable = Table.Pivot( Table.TransformColumnTypes(AddDate, {{“SalesAmount”, type number}}), List.Distinct(AddDate[Month]), “Month”, “SalesAmount”, List.Sum )

• خروجی:
  | SalesTerritoryKey | January | February |

مثال Unpivot (داده‌های آماده تحلیل)

1. جدولی با ستون‌های ماهانه:
   | Territory | Jan | Feb | Mar |
2. Unpivot:

Unpivoted = Table.UnpivotOtherColumns( MonthWideTable, {“Territory”}, “Month”, “SalesAmount” )

• خروجی:
  | Territory | Month | SalesAmount |

نکات پیشرفته و بهینه‌سازی

• حفظ نوع داده: بعد از Unpivot ستون Value را حتماً به نوع مناسب (type number، type date و…) تبدیل کنید تا موتور VertiPaq دچار خطا نشود.
• فیلتر زودهنگام: پیش از هر عملیات Pivot/Unpivot فیلتر کنید تا حجم داده کاهش یابد.
• Batch Size: در صورت مواجهه با خطای OutOfMemory، قدم‌به‌قدم (Incremental) پردازش کنید یا از پارامترهای Query Folding بهره ببرید.
• خطایابی: از Preview Row count و Diagnostics در تب View استفاده نمایید تا مراحل زمان‌بر یا سنگین را شناسایی کنید.

مقایسه کاربردها

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

Pivot و Unpivot در Power Query ستون فقرات آماده‌سازی داده برای داشبورد و تحلیل‌های پیچیده را تشکیل می‌دهند. با درک مکانیسم داخلی و به‌کارگیری نکات بهینه‌سازی می‌توانید حجم داده‌های خود را کاهش، سرعت بارگذاری را افزایش و خوانایی گزارش‌ها را بهبود بخشید.

FAQ (سؤالات پرتکرار)

۱. آیا می‌توان همزمان دو ستون را pivot کرد؟
خیر؛ هر Pivot صرفاً یک ستون کلید (Key) و یک ستون مقدار (Value) می‌پذیرد. برای چند بعدی‌سازی باید مراحل مجزا انجام شود.

۲. بعد از Unpivot، چگونه خطاهای ناچیز در داده را شناسایی کنم؟
از Table.Profile در بسته Power Query SDK یا از Diagnostics → Column Distribution استفاده کنید.

۳. آیا Pivot/Unpivot در Power BI Desktop و Excel متفاوت است؟
خیر؛ موتور و توابع M در هر دو یکسان است، فقط رابط کاربری (UI) کمی تغییر دارد.

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

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

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