Power Query چیست؟ (نام مستعار آن Get & Transform می باشد.)

این افزونه یک نرم افزار ETL است. E که از کلمه Extract گرفته شده به معنی استخراج داده ها از یک منبع دیگر است. T که از کلمه Transform آمده به معنی تغییر شکل داده ها می باشد (در مورد تغییر شکل مثال هایی همینجا ارائه خواهد شد اما در یک اشاره کلی هرگونه نیاز به نغییر ساختار و مفادیر داده ها و یا ایجاد ستون های جدید و تغییرات و پاکسازی را Transform می گوییم.) و L از کلمه Load به معنی بازگذاری داده ها گرفته شده است. بنابراین Power Query کمک می کند تا کاربران Excel و Power BI داده ها را از منابع گوناگون استخراج کرده و به فرم مورد نظر خود تغییر داده و وارد نرم افزار کنند. البته ابزار های ETL چیز جدیدی نیستند، ابزار های قدرتمند زیادی در بازار وجود دارند که توسط متخصصان IT استفاده می شوند. چه چیزی این ابزار نوآورانه و خط شکن را به اولین ابزار مورد استفاده کاربران تجاری (غیر فنی) تبدیل کرده است؟

در واقع Power Query یک نرم افزار ETL است. E که از کلمه Extract گرفته شده به معنی استخراج داده ها از یک منبع دیگر است. T که از کلمه Transform آمده به معنی تغییر شکل داده ها می باشد (در مورد تغییر شکل مثال هایی همینجا ارائه خواهد شد.)

چه کسانی می توانند از Power Query سود ببرند؟

اگر یک یا چند مورد از سناریو های زیر برای شما اتفاق افتاده باشد Power Query ابزار بسیار مفیدی برای شما خواهد بود:

1. وقتی زمان بسیار زیادی را صرف ترکیب و ادغام چند فایل (مثلا فایل های اکسل یا CSV) و تبدیل آن ها به یک فایل می کنید تا بتوانید روی آن کار کنید و گزارش بگیرید.
2. داده هایی در یک فایل دارید اما شکل فعلی آن ها مناسب انجام کار بر روی آن نیست. نیاز دارید تا برای تغییر شکل این داده ها وقت زیادی صرف کنید تا آماده شوند و به شکل مطلوب در بیایند. مثلا وقتی داده های ساید کدال بورس را میگیرید یا وقتی از نرم افزار های شرکت خروجی اکسل میگیرید.
3. ده ها VLookup بر روی جداول گوناگون می نویسید تا داده های آن ها را به هم ربط دهید و به قول کامپیوتری ها آن ها را Join کنید. همچنین زمانی که VLookup های چند شرطی می نویسید.
4. هرماه باید بصورت دستی داده های خود را پاکسازی کنید و خطاها را رفع کنید و بخش هایی از داده ها را که نیاز ندارید حذف کنید. یا مثلا مشکل پاکسازی «ی» و «ی» یا نظیر این ها را دارید.
5. نیاز است تا داده های موجود در وب سایت های اینترنتی را به اکسل بیاورید تا بتوانید آن ها را تحلیل کنید. آن ها را Copy و Paste می کنید و ساعت ها صرف فرمت بندی آن ها و حذف موارد اضافی می کنید.

Power Query چه کاری انجام می دهد؟

از پاورکوئری برای موارد زیر استفاده می شود:

• یک ابزار خواندن (بارگذاری) داده است.
• یک ابزار پاکسازی داده است. (حذف ستون ها و سطر ها و کاراکتر های زائد و …)
• یک ابزار تغییر شکل دهنده داده است که می تواند داده هایی که به شکل Cross یا همان Pivot هستند را از این حالت خارج کند. (به شکل زیر توجه کنید.)

• ابزاری برای ترکیب کردن و ادغام داده ها از فایل ها و شیت های مختلف و تبدیل آن ها به یک فایل است.

• ابزاری برای ادغام منابع گوناگون داده به یک منبع منسجم است.
• ابزار نظارت است – مقایسه لیستی از داده ها برای یافتن شباهت ها و تفاوت ها.
• ابزاری برای خزیدن و خواندن داده های یک وب سایت است – به راحتی داده های جدولی یک سایت را به یک فایل اکسل یا Power BI منتقل می کند.
• ابزار تولید داده است. (به عنوان مثال می توان با آن یک تقویم جدولی ایجاد کرد.)

چه چیزی Power Query را جذاب تر کرده است؟

افزوه Power Query به دلایل زیر جذاب شده است:

• رابط کاربرای بسیار ساده ای دارد بطور که کاربران اکسل می توانند کار با آن را یاد بگیرند.

به منو بندی ساده آن توجه کنید:

• رابط کاربری آن با یک زبان فرمول نویسی قدرتمند که توانمندی های زیادی برای کاربران فراهم میکند تکمیل شده است. اگر قابلیت های بیشتری نسبت به این رابط کاربری خوب و دوست داشتنی نیاز دارید فرمول ها در اختیار شما هستند و می توانید با کمک آن ها کارهای زیادی را بدون تغییر نرم افزار و یادگیری محیط های دیگر انجام دهید.

• Power Query فرآیند های کاری (پردازشی) را که شما بار ها و بارها روی داده ها و جدول ها انجام می دهید، ضبط کرده و انجام می دهد. همچنین می توانید مراحل ضبط شده را به عقب برگردانید و داده ها را قبل از اینکه بارگذاری (Load) شوند، ببینید. حتی می توانید مراحل جدیدی را به مراحل تعریف شدهء قبلی اضافه کنید.

فرآیند های تکراری یعنی اینکه می توانید مراحلی را برای تغییر داده ها ایجاد کنید. این حالت معمولا هفتگی و ماهانه نیاز می شود و کاربران می گویند من یکروز در هفته / ماه تا ساعت ۹ شب در شرکت می مانم تا کارهای گزارش را انجام دهم!

ورژن های Power Query

اکسل ۲۰۱۰ و ۲۰۱۳

اولین بار این افزونه در اکسل ۲۰۱۰ و ۲۰۱۳ ارائه شد. بعد از نصب این افزونه منوی جدید در Ribbon اکسل نمایش داده می شود. میتوانید نصب کننده این افزونه برای اکسل های ۲۰۱۰ و ۲۰۱۳ را با یک جستجوی ساده در گوگل پیدا کنید.

اکسل ۲۰۱۶

در اکسل ۲۰۱۶ این محصول به Get & Transform تغییر نام داده است و بطور کامل با اکسل یکپارچه شده و نیازی به نصب و راه اندازی یا فعال سازی ندارد و از طریق زبانه Data به راحتی قابل دسترس است. (تصویر زیر)

پیشنهاد می کنم برای استفاده از Power Query اکسل ۲۰۱۶ و یا ۲۰۱۹ را نصب کنید چون این افزونه در اکسل های جدید از قبل وجود دارد و نیاز به نصب آن نیست.

Power BI Desktop

Power Query با نرم افزار Power BI نیز بطور کامل یکپارچه شده و به نام Get Data در این نرم افزار شناخته می شود. می توانید آن را در زبانه Home پیدا کنید. Get Data تنها راه ورود داده ها به Power BI است.