پاور کوئری (Power Query) یکی از افزونه‌های مفیدِ نرم‌افزار اکسل می‌باشد که اصلاح و مدیریتِ داده‌ها را برای کاربران ساده‌تر از همیشه کرده است. اما به دور از تعاریف رسمی پاور کوئری چیست ؟ اگر قصد دارید با این افزونه آشنا شده و با لحنی بسیار ساده کاربرد آن را فرا بگیرید با اکسلِر همراه باشید.

کاربرد پاور کوئری (Power Query) به زبان ساده!

در ابتدا به دور از پیچیدگی‌های تعاریفِ رسمی و واژه‌های تخصصی، می‌خواهیم شما را مستقیماً با کاربرد Power Query آشنا کنیم. به عبارت دیگر “لبِ کلام” در 3-4 پاراگرافِ پیشِ رو قرار دارد و شاید با خواندنِ این بخش نیازی به خواندنِ ادامه‌ی مطلب احساس نکنید، کما اینکه با خواندنِ کل محتوای این مطلب به اطلاعات بیشتری از پاورکوئری دست خواهید یافت. تصمیم با شماست 🙂

وظیفه‌ی پاور کوئری ویرایش، اصلاح، ادغام و یکپارچه‌سازیِ داده‌ها برای اکسل می‌باشد. به عبارت دیگر با استفاده از پاور کوئری می‌توانیم داده‌های مختلف را ساماندهی و اصلاح نموده و بعنوان خروجی، داده‌های اصلاح‌شده را به فایل‌ اکسل‌مان اضافه کنیم. Power Query در بسیاری از موارد به کارِ ما سرعتِ عمل، دقت و کیفیتِ بیشتری نسبت به سایر ابزارِ اکسل می‌بخشد.

فرض کنید داده‌هایی از فروش محصولتان دارید و اطلاعات فروشِ همه‌ی ماه‌های سال درونِ یک فایل اکسل وارد شده‌اند، در این صورت ویرایش داده‌ها با استفاده از خودِ اکسل دردسرِ چندانی ندارد. حال فرض کنید داده‌ها و اطلاعاتِ فروشِ هر ماهِ محصولتان در یک فایلِ اکسلِ مجزا ذخیره شده باشد و برای ایجاد نمودار و بررسی وضعیتِ کسب‌وکارتان بخواهید اطلاعاتِ این فایل‌های مجزا را با هم ترکیب کنید: در صورتی که بخواهید این کار را با استفاده از ابزارِ معمولیِ اکسل انجام دهید کارِ زمان‌بر و پردردسری در انتظارِ شماست، اینجاست که پاور کوئری رخ نشان می‌دهد! با استفاده از پاور کوئری انجامِ این فرآیند با چند کلیک و در کسری از ثانیه انجام‌پذیر است و شما می‌توانید دیتای ترکیب‌شده در پاور کوئری را به راحتی واردِ فایل اکسل‌تان کنید و از روی آن نمودار و گزارش بسازید.

این مثال را از جهاتِ مختلف تعمیم دهید. بسیاری از اوقات اطلاعات و دیتاهایی با آن‌ها سر و کار دارید بسیار بزرگ هستند و انجام اصلاحات و ویرایش‌های ساختاری روی آنها با استفاده از ابزارهای اکسل بسیار زمانگیر و کُند خواهد بود، در این مواقع می‌توانید با قابلیت‌های Power Query داده‌ها را به سرعت ویرایش کنید و سروسامان دهید و سپس داده‌های اصلاح شده و ترگل ورگل(!) را به فایلِ اکسل‌تان اضافه کنید (یا اصطلاحاً در فایل اکسل‌تان Load کنید).

مثال بالا را در نظر بگیرید، در اصطلاح به این مثال یک “سناریو” گفته می‌شود. به عبارتِ دیگر به هر مسئله ای که با پاور کوئری قابل حل باشد یک سناریو می‌گوییم. در مثالی که ذکر شد سناریوی پیشِ رو این بود: ترکیب/ادغامِ داده‌های فروشِ ماه‌های سال در حالی که اطلاعاتِ هر ماه درونِ یک فایلِ مجزا قرار داشت. در دنیای پاور کوئری و تجزیه‌وتحلیلِ داده‌ها سناریوهای بی‌شماری وجود دارند. برای مثال یک سناریوی دیگر می‌تواند بدین‌گونه باشد:

یک فایلِ اکسل داریم که شامل 50 شیت است و در هر شیت اطلاعاتِ درآمدِ یکی از شعبه‌‌های شرکت وارد شده. و متاسفانه این اطلاعات به گونه ای وارد شده‌اند که ساختارِ ورودِ اطلاعات یکسان نیست (یعنی: مثلا در شیت‌هایی 1 تا 20 ستونِ اولِ جدولمان “میزان درآمد” است و ستونِ دوم “میزانِ سود“. در حالی که در سایرِ شیت‌ها ستونِ اولِ جدول “میزان سود” است و ستونِ دوم “میزان درآمد“)، و در انتهای سالِ مالی می‌خواهیم این داده ها را با هم ادغام نموده و از آنها نمودار یا Pivot Table تهیه کنیم، در این صورت باید قبل از ادغامْ ساختارِ این داده‌ها را یکسان و یکپارچه کنیم.

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

قابل ذکر است دیتا و اطلاعاتی که می‌توان با پاور کوئری ویرایش و اصلاح کرد صرفاً فایل‌های اکسل نیستند، بلکه در پاور کوئری می‌توان داده‌ها را از فایل‌های متنی، وب، مایکروسافت اکسس و سایر دیتابیس‌ها نیز وارد نموده و ویرایش کرد. مجدداً تاکید می‌کنیم که دیتای ویرایش‌شده توسطِ Power Query را فقط و فقط در اکسل می‌توان Load نمود. پس فهمیدیم که: پاور کوئری دیتا و اطلاعات را از منابع مختلف (اعم از فایل xlsx، txt، csv و فایل‌های انواعِ دیتابیس‌ها) می‌گیرد، روی آن‌ها به دلخواهِ ما پردازش‌های مختلف صورت می‌دهد و در نهایت دیتایِ اصلاح‌شده را در نرم‌افزارِ اکسل (یک فایلِ اکسل) نمایش می‌دهد. این پردازش‌ها می‌توانند شاملِ: ادغامِ داده‌ها، حذف داده‌های زائد و اضافه، نرمال‌سازیِ داده‌ها و… باشند.

چند سناریو (مثالِ) ساده از کابرد پاور کوئری

با ذکرِ چند سناریوی دیگر از پاور کوئری، توضیحاتِ بخشِ “به زبانِ ساده” را خاتمه می‌دهیم:

1. فرض کنید 300 فایل حاوی داده‌های هم‌ساختار داریم و می‌خواهیم آنها را با هم ادغام کنیم (مثلاً اطلاعاتِ حقوق و دستمزد 300 کارمند). اینکار در اکسل یکی دو ساعتی شما را درگیر می‌کند، اما اگر از پاور کوئری استفاده کنید انجامِ این کار 30 ثانیه زمان خواهد برد!
2. فرض کنید 1000 فایل دارید که حاوی اطلاعاتِ پرونده‌های مناقصه‌ی شرکت‌های مختلف اند، و هر پرونده (فایل) شامل ده‌ها ستون اطلاعاتِ متنوع است. و مدیرتان از شما می‌خواهد فقط اطلاعاتِ زیر را از تمام پرونده‌ها (فایل‌ها) جدا کرده، ادغام کنید و گزارشش را به او تحویل دهید:  نام شرکت، موضوع مناقصه‌‌ی مورد بحث، قیمت پیشنهادی، وضعیتِ مناقصه. انجام این کار نیز ساعت‌ها زمان از شما خواهد گرفت، حال آنکه با پاور کوئری می‌توانید در عرض 3 دقیقه گزارشِ ذکرشده را آماده کرده و به مدیرتان تحویل دهید!
3. فرض کنید یک فایل اکسل حاوی 5 هزار سطر اطلاعات در اختیار شماست که در بینِ این اطلاعات، چندین سطرِ خالی نیز وجود دارد و از شما خواسته شده که سطرهای خالی را پاک کنید. بدون استفاده از پاور کوئری این امر بسیار خسته‌کننده و زمانگیر می‌باشد، اما این سناریو در پاور کوئری با یک کلیک و در کمتر از 5 ثانیه قابل انجام است.
4. فرض کنید فایل اکسل‌تان به تعداد استان‌های کشور شیت دارد و درونِ هر شیت نیز در یک جدول نامِ تمام شهرهای آن استان به همراه اطلاعاتشان درج شده است. و شما برای هر جدول یک Vlookup نوشته‌اید تا با جستجوی نامِ شهر بتوانید به اطلاعاتِ آن شهر دسترسیِ سریع و آسانی داشته باشید، اما Vlookup های نوشته شده به خاطر تفاوت حرفِ “ی” فارسی با‌ “ي” عربی و همچنین “ک” فارسی با “ك” عربی به درستی کار نکنند. در این سناریو نیز به سادگی و در عرض چند دقیقه می‌توانید با استفاده از Power Query دیتاهایتان را اصلاح و استاندارد کنید.

پاور کوئری چیست ؟

اگر توضیحاتِ قسمت قبل را خوانده باشید احتمالاً جواب این سوال را می‌دانید. با اینحال در این بخش به معرفی پاورکوئری از نگاه متخصصین حوزه تحلیل‌داده خواهیم پرداخت. Power Query که یک افزونه‌ی داخلیِ اکسل است از جمله نرم‌افزارهای ETL محسوب می‌شود و در امر تجزیه‌وتحلیلِ داده به کار می‌رود. ETL در واقع مخففِ کلماتِ Extract (استخراج داده از منابع مختلف)، Transform (تبدیل داده‌ها به نحو دلخواه و ایجاد تغییرات روی آن‌ها) و Load (لود کردن و نمایشِ داده‌های تبدیل شده و اصلاح شده) می‌باشد.

در پاور کوئری می‌توان داده‌ها را از انواع فایل‌های متنی (مانند txt, csv, xml و…)، فایل‌های انواع دیتابیس‌ها (مانند Access، SQL Server, Oracle)، فایل‌های اکسل (xlsx, xlsm, xlsb)، سایت‌های اینترنتی و… انتخاب نموده و روی آن‌ها اصلاحات و ویرایش انجام داده و در نهایت نتیجه‌ی تغییرات (دیتای اصلاح‌شده) را به اکسل اضافه نمود. قابل ذکر است پاور کوئری در زمره نرم افزارهای نه چندان حرفه‌ایِ ETL قرار می‌گیرد، که البته این نکته به کاربردهای فراوان این افزونه برای مدیریت داده در اکسل خدشه وارد نمی‌کند.

امکانات پاور کوئری

در اینجا منظور از امکانات پاور کوئری، امکانات و ابزاری است که در فازِ Transform (تبدیل و اصلاح داده ها) در محیط پاور کوئری گنجانده شده است. قابل ذکر است در نسخه‌های متأخرِ پاور کوئری استفاده از این ابزار بسیار ساده شده و استفاده از آن‌ها به هیچگونه دانشِ کدنویسی نیاز ندارد. از جمله این امکانات می‌توان موارد زیر را نام برد:

• امکان حذف سطرها و ستون‌ها طبقِ منطق دلخواه و با یک کلیک
• قابلیت‌های قدرتمند Replace کردن داده‌ها
• امکان ادغام انواع فایل‌ها و داده‌ها با قابلیت‌های بسیار زیاد
• امکان ترکیب و Merge کردن داده‌ها با انواع Join Type ها
• امکان Unpivot کردن داده‌ها
• انجام انواع محاسبات ریاضی روی ستون‌ها در کسری از ثانیه
• وجود انواع توابع محاسباتِ متنی در قالبِ ابزارهای آماده و امکان استفاده‌ی آسان از آن‌ها
• دستورات وارد شده در پاور کوئری، مرحله به مرحله ذخیره شده و دائماً با همان منطق روی داده‌ها تغییرات اعمال می‌کنند (مراحل تکرارشونده)

ناگفته نماند تمامِ دستوراتی که کاربر توسطِ ابزارِ بالا انجام می‌دهد در پس زمینه‌ی پاور کوئری توسط کدهایی خاص اجرا می‌شوند که به زبانِ M موسوم است. قابل ذکر است مایکروسافت برای راحت‌تر کردنِ کارِ کاربرانِ معمولی، حتی ساده‌ترین دستورهای این کدها را در قالبِ ابزار گرافیکی در نوارِ ریبون قرار داده تا عمومِ کاربران اکسل بتوانند از آن استفاده کنند.

دسترسی / دانلود پاور کوئری

در اکسلِ نسخه های 2016 و 2019، پاور کوئری به هسته‌ی اکسل افزوده شده و از طریق تبِ Data در نوار ریبون قابل دسترسی است. اما در اکسل 2010 و اکسل 2013 کاربران باید این افزونه را از وبسایت مایکروسافت دانلود نموده و نصب نمایند. پیشنهاد می‌کنیم برای آموزش جامع و کاملِ دانلود و دسترسی به پاور کوئری در انواع نسخه‌های اکسل مطلب آموزش دانلود و دسترسی به پاور کوئری در انواع نسخه‌های اکسل (+) را مشاهده نمایید.

سخن پایانی

قطعاً اگر اکنون کسی این سوال را از شما بپرسد که پاور کوئری چیست می‌توانید بدون لکنت و به راحتی آن را معرفی نموده و توضیحات نسبتاً کاملی پیرامون آن بدهید. ما در اکسلر قصد داریم آموزش های پاور کوئری به زبان ساده را نیز به مرور تهیه و ارائه دهیم. در صورتی که موضوعی در این مطلب برای شما ابهام دارد لطفا از طریق نظرات با ما در میان بگذارید.