چگونه Power Query جایگزین گزارش‌گیری دستی در اکسل با پایپ‌لاین‌های خودکار ETL می‌شود

زمانی که مایکروسافت در نسخه آفیس ۲۰۱۶، ابزار Power Query را به‌طور کامل در تب Data اکسل ادغام کرد، معماری صفحات گسترده به‌طور بنیادین تغییر یافت. پیش از این به‌روزرسانی، اکسل عمدتاً یک شبکه ثابت بود که کاربران داده‌ها را به‌صورت دستی در آن کپی و دستکاری می‌کردند. اما امروز، این نرم‌افزار میزبان یک موتور قدرتمند استخراج، تبدیل و بارگذاری (ETL) است که می‌تواند داده‌ها را از پایگاه‌های داده خارجی فراخوانی کرده، آن‌ها را پاک‌سازی کند و بدون نیاز به حتی یک کلیدزدن دستی، یک گزارش نهایی ارائه دهد.[3]

نکته کاربردی برای هر متخصصی که با داده‌ها سروکار دارد بسیار ساده است: اگر ماهانه بیش از پنج دقیقه را صرف کپی، پیست یا فرمت‌بندی یک خروجی تکراری می‌کنید، باید به‌جای آن یک اتصال Power Query بسازید. این کار هیچ هزینه‌ای ندارد، نیازی به دانش برنامه‌نویسی ندارد و یک کار دستی چندساعته را به یک کلیک تبدیل می‌کند.[4]

جریان کاری سنتی در گزارش‌گیری به‌شدت مستعد خطای انسانی و ناکارآمدی است. معمولاً کاربر یک فایل CSV را از سیستم مدیریت ارتباط با مشتری (CRM) دانلود می‌کند، آن را در اکسل باز می‌کند، ستون‌های غیرضروری را حذف می‌کند، ردیف‌های خالی را فیلتر می‌کند، از تابع VLOOKUP برای اضافه‌کردن نام بخش‌ها استفاده می‌کند و در نهایت تاریخ‌ها را فرمت‌بندی می‌کند.[1]

با رسیدن داده‌های ماه بعد، تمام این مراحل باید از حفظ تکرار شوند. فراموش‌کردن تنها یک مرحله یا اشتباه در تنظیم VLOOKUP می‌تواند گزارش نهایی را خراب کرده و به تصمیمات تجاری غلط منجر شود. تحلیل اخیر MakeUseOf از جریان‌های کاری دقیقاً به همین نقطه ضعف اشاره کرده و یادآور می‌شود که خودکارسازی این فرآیند به «همان گزارش، همان داده‌ها، اما با دردسر بسیار کمتر» ختم می‌شود.[1]

ابزار Power Query این مشکل را با جداسازی منبع داده از لایه ارائه حل می‌کند. در مرحله استخراج (Extract)، این ابزار یک اتصال زنده با فایل منبع، پوشه، پایگاه داده SQL یا وب API برقرار می‌کند. به‌جای واردکردن مستقیم داده‌های خام به شبکه صفحه گسترده، آن‌ها را در یک موتور پردازش پس‌زمینه بارگذاری می‌کند.[2]

این جداسازی به این معناست که داده‌های اصلی هرگز تغییر نمی‌کنند. ابزار Power Query منبع را به‌عنوان یک ورودی فقط‌خواندنی (read-only) می‌خواند و از داده‌های خام در برابر حذف یا بازنویسی تصادفی محافظت می‌کند؛ خطری که در دستکاری دستی صفحات گسترده بسیار رایج است.[4]

مرحله تبدیل (Transform) جایی است که اتوماسیون واقعی رخ می‌دهد. درحالی‌که کاربر در رابط گرافیکی Power Query Editor برای حذف ستون‌ها، تغییر نوع داده‌ها یا ادغام جداول کلیک می‌کند، موتور پردازشی هر اقدام را به‌عنوان یک مرحله متوالی در پنل Applied Steps ثبت می‌کند.[2]

این مراحل اساساً با ماکروهای سنتی VBA در اکسل تفاوت دارند. ماکروها دقیقاً کلیدهای فشرده‌شده و ارجاعات سلولی را ثبت می‌کنند، که در صورت تغییر ساختار داده‌ها، آن‌ها را بسیار شکننده و آسیب‌پذیر می‌سازد. اما مراحل Power Query عملیات ساختاری هستند (مانند حذف یک ستون با نامی مشخص یا فیلترکردن مقادیر خالی) که صرف‌نظر از تعداد ردیف‌های مجموعه‌داده جدید، به‌صورت پویا اعمال می‌شوند.[3]

در پشت این رابط گرافیکی، Power Query این کلیک‌ها را به زبان فرمول‌نویسی M ترجمه می‌کند. زبان M یک زبان تابعی و حساس به حروف بزرگ و کوچک است که به‌طور خاص برای دستکاری داده‌ها طراحی شده است. اگرچه کاربران حرفه‌ای می‌توانند برای اجرای منطق‌های شرطی پیچیده مستقیماً کد M بنویسند، اما ویرایشگر بصری بخش اعظم تبدیل‌های استاندارد در گزارش‌گیری را به‌طور خودکار مدیریت می‌کند.[2]

مرحله بارگذاری (Load) تعیین می‌کند که داده‌های پاک‌سازی‌شده در نهایت کجا قرار گیرند. این داده‌ها می‌توانند برای مشاهده فوری در یک جدول استاندارد اکسل قرار گیرند، یا مستقیماً در Power Pivot Data Model بارگذاری شوند.[3]

بارگذاری در Data Model به‌ویژه از این جهت قدرتمند است که محدودیت سخت‌گیرانه ۱,۰۴۸,۵۷۶ ردیفی اکسل را دور می‌زند. کاربر می‌تواند Power Query را به پایگاه داده‌ای با ۱۰ میلیون ردیف متصل کند، داده‌ها را بر اساس ماه و منطقه تجمیع کرده و تنها نتایج خلاصه‌شده را در صفحه گسترده قابل‌مشاهده بارگذاری کند.[4]

تأثیر عملی این معماری، کاهش چشمگیر تأخیر در گزارش‌گیری است. همان‌طور که تحلیل MakeUseOf نشان داد، یک فرآیند گزارش‌گیری ماهانه که پیش‌تر ساعت‌ها بازسازی دستی زمان می‌برد، تنها به یک کلیک روی دکمه Refresh All کاهش یافت. کوئری به‌سادگی به فایل منبع جدید متصل می‌شود، دقیقاً همان مراحل تبدیل را اعمال کرده و جدول خروجی را به‌روزرسانی می‌کند.[1]

مهم‌ترین نکته قابل‌توجه در استفاده از Power Query، شکنندگی مسیر فایل‌ها است. از آنجا که کوئری به یک اتصال ثابت به داده‌های منبع متکی است، انتقال فایل منبع به یک پوشه جدید یا تغییر نام آن، پایپ‌لاین را تا زمانی که مرحله منبع به‌روزرسانی نشود، از کار می‌اندازد.[4]

علاوه بر این، داده‌های به‌شدت بدون ساختار (مانند فایل‌های PDF یا قالب‌های اکسل با سلول‌های ادغام‌شده فراوان) برای تجزیه صحیح ممکن است به کدهای پیچیده M نیاز داشته باشند. ابزار Power Query در پردازش داده‌های جدولی عالی عمل می‌کند، اما برای عملکرد بهینه بدون نیاز به اسکریپت‌نویسی پیشرفته، به ورودی‌های تمیز و قابل‌خواندن برای ماشین نیاز دارد.[2]

با وجود این موانع جزئی، گذر از دستکاری دستی به ETL خودکار یک تکامل ضروری است. با در نظر گرفتن آماده‌سازی داده‌ها به‌عنوان یک برنامه تکرارپذیر به‌جای یک کار دستی خسته‌کننده، Power Query کاربران را آزاد می‌گذارد تا به‌جای صرف وقت برای فرمت‌بندی، روی تحلیل اعداد تمرکز کنند.[4]