Power Query؛ خط تولید دادهٔ پیشرفت کارگاهی

هر هفته همان سناریو: بیست پیمانکار، بیست فایل اکسل با ستون‌های کمی فرق‌دار، و شما وسط جلسهٔ کنترل پروژه نشسته‌اید و باید تا عصر یک جدول تمیز از پیشرفت فیزیکی تحویل بدهید. Power Query (داخل خود اکسل، بدون نصب هیچ‌چیز) این خط تولید را یک‌بار می‌سازد و بعد از آن، هر هفته فقط دکمهٔ Refresh را می‌زنید. این پست یک خط تولید واقعی را قدم‌به‌قدم می‌سازد — با فرمول‌های M آماده‌ی کپی.

معماری کلی: پوشه، پاک‌سازی، Lookup، گزارش

خط تولید چهار ایستگاه دارد:

1. پوشه‌خوانی: همهٔ فایل‌های پیمانکار از یک پوشهٔ شبکه خوانده می‌شوند (Folder Connector).
2. پاک‌سازی: سرستون‌های بی‌قاعده، ردیف‌های خالی، فرمت‌های درصدی خراب و نام‌های متفاوت فعالیت‌ها یکدست می‌شوند.
3. Lookup: هر سطر به WBS اصلی پروژه وصل می‌شود تا کد و وزنِ استاندارد برگردد.
4. تحویل: جدول نهایی در یک شیت تمیز، آمادهٔ PivotTable یا Power BI.

قاعدهٔ طلایی: هیچ‌وقت روی فایل خام پیمانکار تغییر دستی ندهید. تمام پاک‌سازی داخل Power Query ثبت می‌شود؛ فایل خام فقط خوانده می‌شود. این تفکیک باعث می‌شود هفتهٔ بعد، با فایل‌های جدیدِ همان قالب، صفر دقیقه کار دستی داشته باشید.

ایستگاه ۱ — Folder Connector به‌جای Copy-Paste

مسیر: Data ← Get Data ← From File ← From Folder. آدرس پوشهٔ گزارش‌های هفتگی را بدهید (مثلاً \\server\progress\w36). Power Query هر فایل را باز می‌کند، شیت و بازهٔ داده را می‌گیرد و همه را زیر هم می‌چیند. نکته‌های حرفه‌ای:

• در پنجرهٔ Combine، به‌جای «Combine & Transform» گزینهٔ Transform Data را بزنید — کنترل بیشتری روی فایل‌های بد دارید.
• ستون Source.Name را نگه دارید؛ بعداً می‌فهمید کدام پیمانکار چه فرستاده.
• قالب پوشه را هفتگی نگه دارید (w36, w37, ...) و در Query فقط به پوشهٔ «هفتهٔ جاری» اشاره کنید — با یک پارامتر (Manage Parameters) که هر هفته عوض می‌شود.

let
  Source = Folder.Files("\\server\progress\&" & ParWeek),
  OnlyXlsx = Table.SelectRows(Source, each Text.EndsWith([Extension], ".xlsx")),
  Filtered = Table.SelectRows(OnlyXlsx, each not Text.StartsWith([Name], "~$"))
in
  Filtered

ایستگاه ۲ — پاک‌سازی: جایی که ۸۰٪ زمان می‌رود

دو فاجعهٔ همیشگی گزارش‌های کارگاهی: درصد به‌صورت متن («۴۰٪» یا 0.40 بدون فرمت) و کد فعالیت با فاصلهٔ اضافه («A-120 »). هر دو در Query حل می‌شوند:

= Table.TransformColumns(PromotedHeaders,
    {{"Activity ID", Text.Trim, type text},
     {"Physical %", each try Number.From(Text.Remove(_, "%")) otherwise null, type number}})

و برای فایل‌هایی که با Merge cells آمده‌اند: اول Unpivot را فراموش نکنید — اگر گزارش در قالب «ماتریس هفته‌ها» است (فعالیت × هفته)، Unpivot ستون‌های هفته را به دو ستون Week و Progress تبدیل می‌کند و جدول از حالت افقیِ شکننده به عمودیِ قابل‌گزارش می‌رود.

ایستگاه ۳ — Lookup با WBS استاندارد (مثل VLOOKUP ولی سندشده)

جدول مرجع WBS (کد فعالیت، عنوان، وزن، سیستم) را یک‌بار در یک فایل «Master» بسازید و در Query به‌عنوان Query دوم وارد کنید. سپس Merge Queries:

1. Home ← Merge Queries ← جدول پیشرفت، ستون Activity ID.
2. جدول Master، ستون Activity Code.
3. Join Kind = Left Outer (تمام ردیف‌های پیشرفت بماند، مطابقت‌ها بیاید).

بعد Expand کنید و وزن استاندارد و نام سیستم را بیاورید. حالا یک پرسش کلیدی: چند ردیف match نشد؟ یک ستون شرطی بسازید که اگر وزن null بود، «⚠» بگذارد. این همان کنترل کیفیت دادهٔ شماست — قبل از آن‌که عدد غلط به داشبورد برسد، فهرست کدهای خارج از WBS را دارید.

ایستگاه ۴ — سودو-مدل ستاره‌ای داخل اکسل

خروجی نهایی را به‌صورت دو جدول مدل کنید (حتی بدون Power Pivot هم جواب می‌دهد، ولی با Power Pivot عالی است):

• FactProgress: هفته، کد فعالیت، پیمانکار، پیشرفت فیزیکی، مقدار پیشرفت.
• DimWBS: کد فعالیت، عنوان، وزن، سیستم، WBS والد.

رابطه بر اساس کد فعالیت. از این به بعد، گزارش هفتگی یک PivotTable است: ردیف = سیستم، ستون = هفته، مقدار = وزن × پیشرفت. اگر خواستید سراغ Power BI بروید، همین دو جدول را همان‌طور منتقل می‌کنید — مسیر کامل داشبورد P6 + Power BI را در پست داشبورد Power BI نوشته‌ام و مبنای وزن‌دهی را در محاسبهٔ ضریب وزن (W.F).

چک‌لیست کنترل کیفیت — قبل از انتشار هر گزارش

این پنج چک، ۹۰٪ شرمندگی‌های جلسه را می‌گیرد:

جمع‌بندی

Power Query آن «برنامه‌نویسی بدون برنامه‌نویس» است که برنامه‌ریز و کنترل پروژه بیش از هر ابزار دیگری به آن نیاز دارد: یک‌بار می‌سازید، هر هفته فقط Refresh می‌زنید، و کنترل کیفیت داده به‌جای حافظهٔ شخصی، در خود Query ثبت می‌شود. از همین هفته شروع کنید با یک پوشه و دو فایل — بعد از دو دورهٔ گزارش‌دهی دیگر برنمی‌گردید به روش دستی.

سوالات متداول