Group By در Power Query؛ خلاصه‌سازی داده بدون گم کردن منطق

مقدمه؛ Group By یعنی داده را خلاصه کنیم

فرض کن ۲۰ میلیون ردیف تراکنش داری ولی برای یک مرحله میانی فقط فروش روزانه هر شعبه را لازم داری. Group By می‌تواند ردیف‌ها را بر اساس یک یا چند ستون گروه‌بندی و Aggregate کند.

اما اگر بی‌جا استفاده شود، جزئیاتی را حذف می‌کنی که بعداً دیگر قابل بازیابی نیست.

۲۳.۱. Group By چیست؟

Branch | Sales
A      | 100
A      | 200
B      | 50

→ Group by Branch, Sum Sales
A | 300
B | 50

۲۳.۲. Basic Group By

در حالت Basic یک یا چند ستون گروه و یک Aggregate تعریف می‌کنی؛ مثلاً Sum Sales by Branch.

۲۳.۳. Advanced Group By

در حالت Advanced می‌توانی چند ستون Grouping و چند Aggregate هم‌زمان داشته باشی.

Group by: Branch, Year
Outputs:
Total Sales = Sum
Orders = Count Rows
Max Sale = Max

۲۳.۴. Aggregateهای رایج

۲۳.۵. All Rows

All Rows به جای یک عدد، یک Table کوچک برای هر Group نگه می‌دارد. این قابلیت برای Transformationهای پیشرفته‌تر خیلی مفید است.

۲۳.۶. مثال آخرین سفارش مشتری

می‌توانی بر اساس Customer گروه‌بندی کنی، Max OrderDate را بگیری یا با All Rows منطق پیچیده‌تری برای آخرین رکورد بسازی.

۲۳.۷. Group By و Grain

اگر Grain قبل OrderLine باشد و Group By بر اساس Branch-Date انجام دهی، Grain جدید Branch-Date می‌شود. جزئیات Product و Customer ممکن است از بین برود.

۲۳.۸. Group By یا DAX؟

۲۳.۹. قانون ساده انتخاب

اگر خلاصه‌سازی بخشی از ساختار داده و ETL است، Group By را بررسی کن. اگر کاربر باید با Slicerها نتیجه پویا ببیند، معمولاً Measure مناسب‌تر است.

۲۳.۱۰. Pre-Aggregation

در بعضی معماری‌ها می‌توان جدول Aggregate جداگانه ساخت تا تحلیل‌های پرتکرار سریع‌تر شوند. این کار نیازمند طراحی دقیق Grain و مدل است.

۲۳.۱۱. Count Rows

Orders per Customer
Group by CustomerKey
Count Rows

اگر جدول در Grain OrderLine است، Count Rows الزاماً تعداد سفارش نیست؛ شاید تعداد خطوط سفارش باشد.

۲۳.۱۲. Count Distinct

برای تعداد سفارش واقعی ممکن است OrderID یکتا لازم باشد. همیشه Aggregate را با Grain تطبیق بده.

۲۳.۱۳. Average خطرناک

میانگین میانگین‌ها همیشه برابر میانگین کل نیست. اگر Group By چندمرحله‌ای داری، وزن هر گروه را در نظر بگیر.

نکته: Averageهای تجمیع‌شده یکی از جاهایی هستند که خیلی راحت عدد ظاهراً منطقی ولی غلط تولید می‌شود.

۲۳.۱۴. مثال میانگین قیمت

اگر شعبه A دو فروش و شعبه B صد فروش دارد، Average قیمت متوسط دو شعبه با Average کل تراکنش‌ها یکی نیست.

۲۳.۱۵. Group By چندستونی

Group by:
BranchKey
ProductCategory
Month

Aggregate:
SalesAmount = Sum

۲۳.۱۶. Group By قبل از Merge

گاهی قبل از Merge بهتر است جدول Detail را به Grain مورد نیاز خلاصه کنیم تا Join چندبه‌چند ناخواسته ایجاد نشود.

۲۳.۱۷. مثال هدف فروش

اگر FactTarget در Grain Branch-Month است و جدول تخصیص چند Row برای هر Branch دارد، قبل از Join باید Grain دو طرف را بفهمی.

۲۳.۱۸. Group By بعد از Append

بعد از Append فایل‌های ماهانه می‌توان برای کنترل کیفیت بر اساس SourceFile گروه‌بندی و تعداد Row یا Total Amount را بررسی کرد.

۲۳.۱۹. Data Quality با Group By

Group by OrderID
Count Rows
→ find unexpected duplicates

Group By فقط برای KPI نیست؛ برای کشف Duplicate و بررسی کیفیت داده هم عالی است.

۲۳.۲۰. پیدا کردن Duplicate Key

اگر Dimension باید یک Row برای هر CustomerKey داشته باشد، Group By روی CustomerKey و Count Rows می‌تواند کلیدهای تکراری را پیدا کند.

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

۲۳.۲۲. Performance

Group By می‌تواند حجم داده را کم کند، اما خودش هم عملیات پردازشی است. اگر Source قابلیت Query Folding دارد، جایگاه Stepها مهم می‌شود.

۲۳.۲۳. Query Folding؛ یادآوری

در منابعی مثل دیتابیس، بعضی Transformationها می‌توانند به Source منتقل شوند. برای حجم بالا بررسی کن Group By واقعاً کجا اجرا می‌شود.

۲۳.۲۴. خطای رایج؛ حذف Detail خیلی زود

اگر جدول را در Power Query به ماهانه خلاصه کردی، دیگر Drill Through تا تراکنش را از همان جدول نخواهی داشت.

۲۳.۲۵. خطای رایج؛ Count Rows به جای Count Order

اگر هر Order چند Line دارد، Count Rows تعداد سفارش را بیشتر نشان می‌دهد. باید Grain را بشناسی.

۲۳.۲۶. خطای رایج؛ Aggregate بدون Reconciliation

بعد از Group By جمع مبلغ کل را با قبل مقایسه کن. اگر قرار بوده فقط Reshape شود، Total باید قابل توضیح باشد.

۲۳.۲۷. الگوی کنترل

Profile source
→ Define target grain
→ Group By
→ Check row count
→ Reconcile totals
→ Validate samples

۲۳.۲۸. Group By + All Rows پیشرفته

با All Rows می‌توان داخل هر گروه Sort، Filter یا استخراج رکورد خاص انجام داد. این الگو قدرتمند است ولی Query را خواناتر نگه دار.

۲۳.۲۹. مستندسازی Grain

نام Query یا Description را طوری بنویس که Grain مشخص باشد؛ مثلاً Sales_Daily_Branch به جای Sales2.

۲۳.۳۰. Best Practice

قبل از Group By Grain را بنویس.

Aggregate را با معنی کسب‌وکار تطبیق بده.

Detail موردنیاز آینده را بی‌دلیل حذف نکن.

Totalها را Reconcile کن.

برای Duplicate Detection از Group By استفاده کن.

برای تحلیل پویا Group By را جای Measure نگذار.

۲۳.۳۱. تمرین پایه

جدول تراکنش فروش را بر اساس Branch گروه‌بندی کن و Sum Sales و Count Rows بساز. سپس توضیح بده Count Rows چه چیزی را می‌شمارد.

۲۳.۳۲. تمرین متوسط

بر اساس OrderID گروه‌بندی کن و سفارش‌هایی را پیدا کن که بیش از یک Line دارند. بعد تعداد سفارش واقعی را با تعداد Row مقایسه کن.

۲۳.۳۳. تمرین چالشی

یک سناریو طراحی کن که Group By قبل از Merge جلوی چندبرابر شدن Amount را بگیرد. Grain دو جدول را قبل و بعد بنویس.

۲۳.۳۴. پروژه فصل؛ Daily Sales Aggregate

برای شرکت هانا یک Query خلاصه فروش روزانه بساز، اما جدول Detail را هم برای Drill Through نگه دار و دلیل معماری را توضیح بده.

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

Group By ابزار خلاصه‌سازی است، ولی هر خلاصه‌سازی یعنی تغییر Grain. حرفه‌ای بودن یعنی بدانی چه جزئیاتی را حذف می‌کنی و چرا.

Define grain → Aggregate intentionally → Reconcile → Keep needed detail

در فصل بعد می‌توانیم وارد Merge Queries و انواع Join شویم؛ یکی از مهم‌ترین بخش‌های Power Query برای اتصال داده‌های واقعی چند سیستم.