Power Query چیست؟

اکنون بهترین زمان برای صحبت در باره Power Query است. البته تصور می کنم نرم افزار Power BI این عدم شناخت Power Query را رفع کرد. Power BI بیشتر دیده می شود و در روند ها و جستجوهای گوگل رشد خیره کننده ای دارد اما من اینجا در مورد Power Query صحبت خواهم کرد.

به روند جستجو ها بصورت آنلاین توجه کنید:

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

یادگیری این ابزار جدید سخت نیست؟

ممکن است با خود فکر کنید که اکنون با این همه مشغله و کارهای پی در پی در سازمان چگونه یک ابزار جدید را باید یاد بگیرید. نگران نباشید یادگیری پاورکوئری به مراتب از اکسل نیز آسان تر است:

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

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

چه کسانی می توانند از 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

اکسل 2010 و 2013

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

اکسل 2016

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

Power BI Desktop

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

فرمول نویسی در Power Query  – زبان M

M نام زبانی است که با آن در Power Query فرمول نویسی می شود. فرمول های این نرم افزار تفاوت هایی نسب به اکسل دارند چرا که در این برنامه هدف آماده سازی و پاکسازی داده ها می باشد. می توانید لیست و راهنمای فرمول ها را از لینک زیر دریافت کنید:

دانلود فایل راهنمای فرمول های Power Query

دسته بندی، تعداد و لیست فرمول های Power Query

تعداد  فرمول های پاور کوئری تا این زمان (می 2019) 677 فرمول بوده و به 23 گروه تقسیم می شوند:

1. فرمول های گروه Accessing Data که شامل 105 فرمول می باشند.
2. فرمول های گروه Binary که شامل 46 فرمول می باشند.
3. فرمول های گروه Combiner که شامل 5 فرمول می باشند.
4. فرمول های گروه Comparer که شامل 5 فرمول می باشند.
5. فرمول های گروه Date که شامل 64 فرمول می باشند.
6. فرمول های گروه DateTime که شامل 25 فرمول می باشند.
7. فرمول های گروه DateTimeZone که شامل 15 فرمول می باشند.
8. فرمول های گروه Duration که شامل 12 فرمول می باشند.
9. فرمول های گروه Table که شامل 108 فرمول می باشند.
10. فرمول های گروه Text که شامل 55 فرمول می باشند.
11. فرمول های گروه Value که شامل 21 فرمول می باشند.
12. فرمول های گروه Type که شامل 20 فرمول می باشند.
13. فرمول های گروه Error Handling که شامل 8 فرمول می باشند.
14. فرمول های گروه Function Values که شامل 5 فرمول می باشند.
15. فرمول های گروه List که شامل 69 فرمول می باشند.
16. فرمول های گروه Lines که شامل 4 فرمول می باشند.
17. فرمول های گروه Logical که شامل 3 فرمول می باشند.
18. فرمول های گروه Number که شامل 63 فرمول می باشند.
19. فرمول های گروه Record که شامل 17 فرمول می باشند.
20. فرمول های گروه Replace که شامل 2 فرمول می باشند.
21. فرمول های گروه Splitter که شامل 12 فرمول می باشند.
22. فرمول های گروه Time که شامل 9 فرمول می باشند.
23. فرمول های گروه Uri که شامل 4 فرمول می باشند.

دانلود Power Query

همانطور که اشاره شد در نسخه 2013 اکسل و نسخه های قبل از آن تا 2010 شما باید پاورکوئری را خودتان نصب و فعال سازی کنید. برای دانلود این افزونه می توانید به لینک زیر مراجعه کنید:

دانلود افزونه از سایت مایکروسافت

منبع