ورود به عصر داده‌های بزرگ با Power Query و Power Pivot

تا اینجا با ابزارهای اکسل برای داده‌های نسبتاً کوچک کار کردید. اما اگر داده‌های شما میلیون‌ها رکورد داشته باشند، از چندین منبع مختلف بیایند و نیاز به پاک‌سازی و مدل‌سازی پیچیده داشته باشند، ابزارهای استاندارد اکسل پاسخگو نیستند. اینجاست که دو ابزار فوق‌قدرت وارد می‌شوند: Power Query برای اتصال، پاک‌سازی و تبدیل داده‌ها، و Power Pivot برای مدل‌سازی داده‌های حجیم و ایجاد محاسبات پیشرفته. این دو ابزار، پلی بین اکسل و ابزارهای هوش تجاری (BI) مانند Power BI هستند و تسلط بر آن‌ها شما را به یک متخصص داده تبدیل می‌کند.

۱. Power Query؛ اتوماسیون پاک‌سازی و تبدیل داده

Power Query (که در نسخه‌های جدید اکسل با نام Get & Transform Data شناخته می‌شود) یک موتور قدرتمند برای اتصال به منابع داده متنوع (فایل‌های اکسل، CSV، متن، پایگاه داده SQL، وب‌سایت‌ها، SharePoint و حتی پوشه‌های کامل) و انجام عملیات پاک‌سازی و تبدیل روی آن‌هاست. مهم‌ترین ویژگی Power Query این است که تمام مراحل پاک‌سازی را به صورت یک کوئری (پرس‌وجو) ذخیره می‌کند و با یک کلیک، همان مراحل را روی داده‌های جدید تکرار می‌کند. این یعنی دیگر نیازی نیست هر بار که داده‌های جدید دریافت می‌کنید، فرآیند پاک‌سازی را از صفر شروع کنید. برای شروع، از زبانه Data، گزینه Get Data را انتخاب کنید و منبع داده خود را مشخص کنید.

۲. عملیات‌های رایج در Power Query

پس از اتصال به داده، پنجره Power Query Editor باز می‌شود که محیطی کاملاً متفاوت از اکسل استاندارد دارد. در این محیط، هر مرحله از پاک‌سازی در سمت راست (Applied Steps) ثبت می‌شود. برخی از عملیات‌های رایج عبارتند از: حذف سطرها یا ستون‌های خالی، تغییر نوع داده (متن به عدد یا تاریخ)، جایگزینی مقادیر (مثلاً «آقای» را با «مرد»)، تقسیم ستون بر اساس جداکننده، ادغام ستون‌ها، استخراج بخشی از متن (مثل کد پستی از آدرس کامل)، افزودن ستون‌های شرطی (مشابه IF)، گروه‌بندی و جمع‌آوری داده‌ها، و الحاق (Append) چند جدول به یکدیگر یا ادغام (Merge) جداول مانند یک JOIN در پایگاه داده. تمام این عملیات بدون نوشتن یک خط کد و با رابط کاربری گرافیکی انجام می‌شود.

۳. به‌روزرسانی خودکار داده‌ها با Power Query

قدرت واقعی Power Query زمانی نمایان می‌شود که داده‌های منبع تغییر می‌کنند. فرض کنید هر ماه یک فایل فروش جدید از سیستم مالی دریافت می‌کنید. با Power Query، یک بار فرآیند پاک‌سازی را تعریف می‌کنید و کوئری را ذخیره می‌کنید. سپس هر ماه، فقط فایل جدید را جایگزین کرده و دکمه Refresh را می‌زنید. اکسل کل فرآیند پاک‌سازی را دوباره اجرا کرده و داده‌های پاک‌سازی‌شده را به‌روز می‌کند. برای داده‌هایی که از پایگاه داده می‌آیند، می‌توانید کوئری را به‌گونه‌ای تنظیم کنید که هر بار که فایل را باز می‌کنید، به‌طور خودکار داده‌های جدید را دریافت کند. این قابلیت، گزارش‌های شما را به داشبوردهای زنده (Live Dashboards) تبدیل می‌کند.

۴. Power Pivot؛ مدل‌سازی داده‌های حجیم

Power Pivot یک موتور محاسباتی درون‌حافظه (In-Memory) است که به شما اجازه می‌دهد تا میلیون‌ها رکورد داده را در یک مدل داده (Data Model) بارگذاری کرده و روابط پیچیده بین جداول مختلف برقرار کنید. تفاوت اصلی Power Pivot با PivotTable معمولی این است که PivotTable استاندارد روی داده‌های موجود در کاربرگ کار می‌کند، اما Power Pivot روی یک مدل داده جداگانه که می‌تواند شامل داده‌هایی از منابع مختلف باشد، عمل می‌کند. برای فعال‌سازی Power Pivot، از منوی File، Options، Add-ins، COM Add-ins بروید و Microsoft Power Pivot for Excel را تیک بزنید. سپس زبانه Power Pivot در نوار ابزار ظاهر می‌شود.

۵. ایجاد روابط بین جداول در Power Pivot

یکی از ویژگی‌های کلیدی Power Pivot، امکان ایجاد روابط بین جداول مختلف است. فرض کنید یک جدول «فروش» با ستون «کد محصول» و یک جدول «محصولات» با ستون‌های «کد محصول» و «نام محصول» و «دسته‌بندی» دارید. با ایجاد یک رابطه بین این دو جدول بر اساس کد محصول، می‌توانید در یک PivotTable، مجموع فروش را بر اساس دسته‌بندی محصولات محاسبه کنید، بدون اینکه نیازی به الحاق جداول با VLOOKUP داشته باشید. برای ایجاد رابطه، در نمای Diagram View Power Pivot، کد محصول را از جدول فروش به جدول محصولات بکشید. این روابط مانند پایگاه‌های داده رابطه‌ای عمل می‌کنند و مدل داده شما را بسیار قدرتمند می‌سازند.

۶. DAX؛ زبان فرمول‌نویسی Power Pivot

قدرت واقعی Power Pivot در زبان فرمول‌نویسی آن یعنی DAX (Data Analysis Expressions) نهفته است. DAX شامل بیش از ۲۰۰ تابع است که بسیاری از آن‌ها مشابه توابع اکسل هستند اما با تفاوت‌های کلیدی. برخی از توابع پرکاربرد DAX عبارتند از: =CALCULATE() که مقدار یک عبارت را در زمینه‌ای تغییر‌یافته محاسبه می‌کند (بسیار قدرتمندتر از SUMIFS). =FILTER() برای فیلتر کردن یک جدول. =RELATED() برای دسترسی به ستون‌های جداول مرتبط. =SUMX() و =AVERAGEX() که روی هر سطر یک جدول محاسبه کرده و سپس جمع یا میانگین می‌گیرند. =DIVIDE() برای تقسیم با مدیریت خطای تقسیم بر صفر. =TOTALYTD() برای محاسبه مجموع از ابتدای سال تا تاریخ فعلی. یادگیری DAX یک مهارت پیشرفته است اما شما را به سطح تحلیل‌گران حرفه‌ای داده می‌رساند.

۷. KPI و محاسبات زمانی در Power Pivot

یکی از کاربردهای مهم Power Pivot، ایجاد محاسبات زمانی و شاخص‌های کلیدی عملکرد (KPI) است. با توابع هوشمند زمان در DAX (مثل =SAMEPERIODLASTYEAR()، =DATESYTD()، =DATEADD() و =PARALLELPERIOD()) می‌توانید تحلیل‌های مقایسه‌ای قدرتمندی انجام دهید. مثلاً می‌توانید فروش امسال را با فروش سال گذشته در همان ماه مقایسه کنید، یا رشد درصدی ماه‌به‌ماه را محاسبه کنید. تمام این محاسبات به صورت پویا و با تغییر فیلترهای تاریخ، به‌روز می‌شوند. برای تعریف KPI، در Power Pivot یک اندازه (Measure) ایجاد کرده و سپس آن را به عنوان یک KPI تنظیم کنید، با تعریف یک مقدار هدف (مثلاً ۱۰۰ میلیون) و یک آستانه (مثلاً ۸۰ میلیون)، نمایش بصری وضعیت عملکرد به صورت چراغ راهنمایی (سبز، زرد، قرمز) دریافت می‌کنید.

۸. ادغام Power Query و Power Pivot

این دو ابزار به صورت کامل با هم ادغام شده‌اند. معمولاً فرآیند به این شکل است: داده‌ها را با Power Query از منابع مختلف دریافت و پاک‌سازی می‌کنید، سپس داده‌ها را به مدل داده (Data Model) بارگذاری می‌کنید (با انتخاب گزینه Load To و تیک Add this data to the Data Model). در Power Pivot، روابط بین جداول را تعریف کرده و اندازه‌های محاسبه‌شده با DAX می‌سازید. سپس از این مدل داده برای ایجاد PivotTable‌ها و PivotChart‌های بسیار پیشرفته استفاده می‌کنید که می‌توانند میلیون‌ها رکورد را با سرعت بالا پردازش کنند. این ترکیب، اکسل را به یک ابزار هوش تجاری در سطح سازمانی تبدیل می‌کند که با نرم‌افزارهای تخصصی BI رقابت می‌کند.