Power Query چیست؟ آشنایی با قابلیت ها، ویژگی ها و نحوه نصب پاور کوئری

Power Query چیست؟ برای درک پاسخ این سؤال، آیا تابه‌حال با انبوه داده‌هایی که مثل نخ و کلاف کاموا به‌هم پیچیده و گره خوردند، روبه‌رو شده‌اید؟ خبر خوب اینکه دردسر مرتب‌کردن داده‌های به‌هم ریخته با Power Query ساده می‌شود. این ابزار قدرتمند در نرم‌افزارهایی مثل اکسل و Power BI است که به شما کمک می‌کند به‌سادگی بر اطلاعات خود مسلط شوید. در این مقاله، به‌طور کامل بررسی می‌کنیم پاور کوئری چیست، با کاربردها، مزایا و محدودیت‌های آن آشنا می‌شوید و یاد می‌گیرید که چطور از این ابزار قدرتمند برای مدیریت و آماده‌سازی داده‌هایتان استفاده کنید.

مشاهده و خرید کامل‌ترین دوره Power bi از نیک آموز

Power Query چیست؟

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

فرقی نمی‌کند از پاور کوئری در کدام نرم‌افزار مایکروسافت مثل اکسل یا Power BI استفاده کنید؛ خروجی نهایی در همان محیط ذخیره می‌شود. با این ابزار، فرآیند ETL (استخراج، تبدیل و بارگذاری) داده‌ها را به‌سادگی می‌توانید انجام دهید و دیگر نگران به‌هم‌ریختگی اطلاعات نباشید. همچنین می‌توانید مقاله تفاوت های کلیدی بین Power BI و Excel چیست؟ را مطالعه کنید تا به‌طور کامل با تفاوت‌های این دو نرم‌افزار آشنا شوید.

قابلیت ها و ویژگی های کلیدی Power Query چیست؟

شاید تعجب کنید اگر بدانید ۸۰ درصد وقت تحلیل‌ گران داده، صرف آماده‌ سازی دیتا می‌شود؛ این یعنی زمان کافی برای تجزیه‌ و تحلیل داده برایشان باقی نمی‌ماند. Power Query به شما کمک می‌کند تا بر بسیاری از چالش‌ های آماده‌ سازی داده‌ ها غلبه کنید.

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

M زبان اسکریپت‌نویسی قدرتمندی است که پشت صحنه‌ تمام تحولات پاور کوئری قرار دارد. هر اتفاقی که در یک Query می‌افتد، درنهایت به زبان M نوشته می‌شود. اگر می‌خواهید تحولات پیشرفته‌تری انجام دهید، از ویرایشگر پیشرفته (Advanced Editor) برای دسترسی به اسکریپت Query و ویرایش دلخواه آن استفاده کنید.

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

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

اکنون که فهمیدیم Power Query چیست، سراغ بررسی کاربرد آن می‌رویم. فرقی نمی‌کند با چه حجم و نوع داده‌ای سروکار دارید؛ پاور کوئری ابزاری قدرتمند برای شماست. در ادامه، به چند گروه از کسب‌وکارهایی که به‌طور ویژه از این ابزار بهره می‌برند، اشاره می‌کنیم:

۱- علاقه مندان به هوش تجاری (BI)

اگر به دنیای هیجان‌انگیز هوش تجاری (BI) و استفاده از داده‌ها برای تصمیم‌گیری بهتر در کسب‌وکارتان علاقه‌مند هستید، Power Query نقطه شروع ایده‌آلی برای شماست. منظور از فرآیندهای BI، فرآیندهایی است که با استفاده از هوش تجاری انجام می‌شود.

هوش تجاری (BI) مجموعه‌ای از ابزارها، فرآیندها یا روش‌هایی است که به سازمان‌ها کمک می‌کند تا داده‌های خود را جمع‌آوری، تجزیه‌وتحلیل و به‌کار بگیرند تا تصمیمات آگاهانه‌تر و مبتنی‌بر داده برای بهبود عملکرد کسب‌وکار خود داشته باشند. پیشنهاد می‌کنیم برای درک بهتر BI ، ویدئو بررسی مفهوم هوش تجاری را مشاهده نمایید.

این ابزار چند مزیت مهم دارد:

• محیط آشنا: با رابط کاربری شبیه به اکسل، یادگیری پاور کوئری برای کاربران اکسل بسیار آسان است.
• مهارت‌های قابل انتقال: مهارت‌هایی که در Power Query به‌دست می‌آورید، به‌راحتی در ابزارهای قدرتمندتر BI ، مانند Power BI ، نیز قابل استفاده هستند.
• کاربری آسان: این ابزار قدرتمند بدون نیاز به دانش فنی پیچیده، به شما امکان می‌دهد تا به‌سرعت با داده‌ها کار کنید و آن‌ها را برای تحلیل آماده نمایید.

۲- تحلیلگران اکسل

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

• داده‌ ها را از منابع مختلف مانند فایل‌های CSV، پایگاه داده و وب‌ سایت‌ها وارد کنید.
• داده‌ های خام را پاکسازی و سازماندهی کنید تا برای تجزیه‌ و تحلیل آماده شوند.
• محاسبات و فرمول‌ های سفارشی روی داده‌ها اعمال نمایید.
• داده‌ های ورودی را به‌صورت خودکار به‌ روز رسانی کنید.

۳- همه کسانی که با داده سروکار دارند

حتی اگر اکسل یا Power BI ابزار اصلی شما برای کار با داده‌ها نیستند، Power Query همچنان می‌تواند برایتان مفید باشد. به‌عنوان مثال:

• می‌توانید به‌راحتی داده‌ها را از منابع مختلف ترکیب و ادغام کنید.
• بدون نیاز به نرم‌افزارهای پیچیده، به‌سادگی داده‌ها را دستکاری و مدیریت کنید. 
• خروجی‌های این ابزار را به یک صفحه گسترده منتقل کرده و از آن برای ایجاد نمونه اولیه (پروتوتایپ) سریع یک پروژه BI با داده‌های سازماندهی‌شده استفاده کنید.

مثال ها و کاربرد های عملی از Power Query

در این بخش، شما را با مثال‌ها و کاربردهای عملی Power Query در اکسل و فرآیندهای هوش تجاری آشنا خواهیم کرد.

تبدیل داده ها و قابلیت های دیگر 

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

این ابزار، حتی قابلیت‌های قدرتمندتری مانند ادغام‌کردن چند جدول، وصل‌کردن داده‌ها، گروه‌بندی، چرخش و بازکردن جداول را نیز ارائه می‌دهد.

مثال: فرض کنید عملکرد ماهانه فروشگاه را می‌خواهید بررسی کنید. مشکل اینجاست داده‌های شما در یک فایل متنی به شکل زیر است.

کاربران اکسل به‌خوبی می‌دانند که مرتب‌کردن چنین داده‌‌ای برای تحلیل، چقدر فرسایشی است. Power Query با چند کلیک ساده در عرض چند دقیقه این کارها را انجام می‌دهد:

• ردیف‌های اضافی بالا را حذف می‌کند.
• ستون‌های خالی را پاک می‌کند.
• مقادیر جاافتاده را پر می‌کند.
• ساختار جدول را برای تحلیل ماهانه تغییر می‌دهد (Unpivot).

پایان انجام کار های تکراری در Power BI

با Power BI دیگر نگران تکرار کارهای خسته‌کننده برای آماده‌سازی داده‌ها نخواهید بود. این نرم‌افزار، هوشمندانه تمام مراحلی را که برای تغییر و آماده‌سازی داده‌ها انجام می‌دهید، به‌خاطر می‌سپارد؛ یعنی هربار که بخواهید گزارش‌هایتان را به‌روزرسانی یا تغییراتی اعمال کنید، دیگر لازم نیست کل فرآیند را از اول تکرار کنید. Power BI در عرض چند ثانیه ورودی‌های مدل را با اعمال همان مراحل قبلی، به‌روزرسانی می‌کند.

برای درک بهتر، فرض کنید برای آماده‌سازی داده‌های فروش، چند مرحله‌ مرتب‌سازی، فیلتر و تغییر فرمت انجام دادید. حالا می‌خواهید اطلاعات فروش ماه جدید را هم اضافه کنید. با Power BI فقط کافیست منبع داده را به‌روز کنید. خود نرم‌افزار همان مراحل قبلی را روی داده‌های جدید اعمال می‌کند و خروجی به‌روز شده را در اختیار شما قرار می‌دهد.

ادغام چند جدول 

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

به مثال زیر توجه کنید:

فرض کنید دو جدول زیر را دراختیار دارید. در سمت چپ جدول اول گزارشی از از تراکنش‌ها قرار دارد.

در سمت راست جدول دوم، اطلاعات مربوط به تحویل هر تراکنش را دارید.

با چند کلیک ساده در Power Query، می‌توانید از ستون‌های مشترک برای ادغام این دو جدول استفاده کنید و اطلاعات تحویل را به جدول اصلی تراکنش‌ها اضافه کنید.

ادغام آسان اطلاعات از چندین فایل

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

برای مثال، اینجا سه فایل اکسل مشابه داریم که هرکدام حاوی اطلاعات فروش شعب در یک ماه خاص هستند.

هدف ما این است که یک فایل واحد با لیست کاملی از تراکنش‌ها داشته باشیم. با ابزار قدرتمند پاور کوئری، می‌توانیم به‌راحتی فایل‌های بالا را در یک جدول واحد ادغام کنیم.

این کار باعث صرفه‌جویی قابل توجهی در زمان تحلیلگران و کاهش خطر خطای انسانی می‌شود.

اتصال به منابع مرکزی داده

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

• صفحات وب
• پایگاه داده‌ SQL Server
• فایل‌های محلی روی کامپیوتر
• سرویس‌های ابری برای ذخیره‌سازی اطلاعات

با تکیه بر منابع مرکزی داده، همه تحلیل‌گران اطمینان پیدا می‌کنند که براساس یک نسخه‌ واحد و درست از اطلاعات کار می‌کنند؛ یعنی از شر نگرانی پراکندگی و سردرگمی داده‌ها خلاص می‌شوید. منوی دریافت داده (Get Data) به شما این امکان را می‌دهد تا به‌سادگی و با شخصی‌سازی (Query) از انواع منابع داده، اطلاعات موردنیاز خود را استخراج کنید.

مزایا و محدودیت های Power Query چیست؟

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

• صرفه‌جویی در زمان

Power Query با خودکارکردن بسیاری از کارهای تکراری پاکسازی و آماده‌سازی داده‌ها، در وقت شما صرفه‌جویی قابل توجهی می‌کند.

• کاربر پسند

رابط کاربری ساده و قابل فهم پاور کوئری، استفاده از آن را برای کاربران با هر سطح فنی‌ای آسان می‌کند.

• قابل استفاده مجدد

هر تغییری که روی داده‌ها انجام می‌دهید، قابل ذخیره‌سازی است و از آن می‌توانید برای مجموعه داده‌های دیگر یا با داده‌های جدید، به‌راحتی استفاده کنید.

• انعطاف‌ پذیری بالا

Power Query انواع قابلیت‌های تغییر شکل داده‌ها را ارائه می‌دهد؛ ازجمله ادغام، فیلترکردن، گروه‌بندی، چرخش و بازکردن جداول (Pivot / Unpivot) که در بالا به آن اشاره کردیم. شما می‌توانید آن‌ها را براساس نیازهای خاص خود تنظیم کنید.

• یکپارچه با سایر محصولات مایکروسافت

این ابزار به‌طور کامل با سایر ابزارهای Microsoft Office مانند Excel و Power BI یکپارچه می‌شود.

البته این ابزار محدودیت‌هایی نیز به‌دنبال دارد:

• نیاز به یادگیری اولیه

با اینکه این ابزار رابط کاربری ساده‌ای دارد، اما یادگیری کامل امکانات و گزینه‌های مختلف تغییر شکل داده‌ها نیاز به تمرین دارد.

• سرعت برای داده‌های حجیم

هنگام کار با مجموعه داده‌های بسیار بزرگ یا انجام تغییرات پیچیده، ممکن است Power Query کمی کند شود.

• محدودیت در موارد خاص

ابزار پاور کوئری در برخی موارد خاص، که نیاز به تغییرات بسیار پیشرفته روی داده‌ها یا کار با برخی منابع داده خاص دارید، ممکن است با محدودیت‌هایی روبه‌رو شود.

• دسترسی محدود

Power Query فقط در نسخه‌های خاصی از Microsoft Excel دردسترس است و در سایر نرم‌افزارهای تجزیه‌وتحلیل داده وجود ندارد.

نحوه نصب و راه اندازی Power Query

در ادامه مقاله Power Query چیست، به بررسی نحوه نصب و راه‌اندازی آن می‌رسیم. برای استفاده از این ابزار، دو گام پیش رو دارید. ابتدا باید داده‌ها را به SQL server منتقل و سپس پاور کوئری را به دیتابیس وصل کنیم.

قدم اول: انتقال تمام داده ها به SQL Server

در این مرحله، ما داده‌های خود را ابتدا در یک فایل اکسل ایجاد می‌کنیم و سپس با استفاده از افزونه‌ کاربردی SQL Spreads برای اکسل، آن‌ها را به یک جدول در SQL Server منتقل خواهیم کرد.

• نصب افزونه‌ SQL Spreads

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

فرض کنید داده‌ های ارزشمندی را در اکسل جمع‌آوری کردید. حالا می‌خواهید این داده‌ها را به یک جدول کاربردی در SQL Server منتقل کنید. با استفاده از قابلیت «Table Creator» در افزونه‌ کاربردی SQL Spreads، این کار به‌سادگی قابل انجام است. مراحل کار به شرح زیر است:

• داده‌ های خود را در اکسل به یک جدول تبدیل کنید

کافی‌ست روی یکی از سلول‌های داده کلیک کرده و با نگهداشتن کلید Ctrl، کلید T را بزنید. (ترکیب کلیدهای Ctrl+T)

• ایجاد جدول جدید در SQL Server

اگر اولین بار است که از SQL Spreads استفاده می‌کنید، پنجره‌ای برای اتصال به SQL Server شما باز خواهد شد. برای اتصال به SQL Server ، اطلاعات سرور خود را وارد کرده و روی «OK» کلیک کنید. پس از اتصال، روی دکمه‌ «Create new SQL Table» در SQL Server بزنید.

در این قسمت، نامی برای جدول خود انتخاب کرده و پایگاه داده‌ موردنظر در SQL Server را برای ایجاد جدول مشخص کنید. SQL Spreads آخرین سرور و پایگاه داده‌ استفاده‌شده را به‌خاطر می‌سپارد. درصورتی‌که نیاز به تغییر سرور دارید، دکمه‌ «Edit» کنار نام سرور را بزنید.

نام دلخواه خود را برای جدول (مثلاً kpi_demo) وارد کرده و روی «OK» کلیک کنید. پیغامی مبنی‌بر ایجاد موفقیت‌آمیز جدول و تعداد ردیف‌های واردشده به آن، نمایش داده خواهد شد.

• بررسی جدول در SQL Server Management Studio

برای اطمینان از ایجاد جدول و انتقال داده‌ها، می‌توانید با اجرای یک کوئری در SQL Server Management Studio آن را بررسی کنید. برای یادگیری نصب آن می‌توانید مقاله آموزش نصب گام به گام SSMS را مطالعه کنید.

قدم دوم: اتصال Power Query به SQL Server 

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

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

• Power Query Online: این نسخه تحت وب است و در سرویس‌هایی مانند Power BI data flows و Azure Data Factory در دسترس شما قرار می‌گیرد.
• Power Query for Desktop: این نسخه به‌صورت یک برنامه مجزا یا بخشی از نرم‌افزارهایی مانند Power BI Desktop و اکسل قابل استفاده است.

وصل شدن به پایگاه داده SQL Server با Power Query Desktop

اگر می‌خواهید اطلاعات ارزشمند یک پایگاه داده SQL Server را تجزیه‌وتحلیل کنید، Power Query Desktop به کمک شما می‌آید. در چند مرحله ساده، می‌توانید به دیتابیس خود وصل شوید:

• در بخش (Get Data)، گزینه «SQL Server database» را انتخاب کنید.

• پنجره جدیدی برایتان باز می‌شود. در این قسمت، نام سرور (Server) و نام پایگاه داده (Database) که می‌خواهید به آن وصل شوید، وارد کنید. (واردکردن نام پایگاه داده اختیاری است.)

دو حالت برای برقراری ارتباط وجود دارد:

• وارد کردن (Import): با این گزینه، یک کپی از اطلاعات موردنظر شما از پایگاه داده به محیط Power Query Desktop منتقل می‌شود. این روش برای کار با حجم متوسط داده مناسب است.
• کوئری مستقیم (DirectQuery): در این حالت، هربار که نیاز به اطلاعات داشته باشید، Power Query به‌طور مستقیم به پایگاه داده SQL Server متصل شده و اطلاعات به‌روز را دریافت می‌کند. این روش برای حجم بالای داده که دائم درحال تغییر است، کاربرد دارد.

حالت موردنظر خود را انتخاب کرده و سپس روی «OK» کلیک کنید.

• اگر برای اولین بار است که به این پایگاه داده وصل می‌شوید، پنجره دیگری برای واردکردن نام کاربری و رمز عبور باز خواهد شد. اطلاعات ورود خود را وارد کرده و سطح دسترسی را مشخص کنید. درنهایت، روی «Connect» بزنید.

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

پس از برقراری اتصال، پاور کوئری لیستی از جداول موجود در پایگاه داده شما را نمایش می‌دهد. جدولی را که می‌خواهید با آن کار کنید، انتخاب کرده و روی «Load» کلیک کنید.

وصل شدن به پایگاه داده SQL Server با Power Query Online

اگر می‌خواهید از Power Query Online به اطلاعات موجود در یک پایگاه داده SQL Server دسترسی پیدا کنید، در این راهنما، قدم‌به‌قدم باهم پیش می‌رویم تا اتصال برقرار شود:

• در بخش مربوط به انتخاب اتصالگر (Connector)، گزینه «پایگاه داده SQL Server» را انتخاب کنید.
• پنجره‌ای برای تنظیمات اتصال باز می‌شود. در این قسمت، نام سرور و پایگاه داده موردنظر را وارد کنید (این بخش اختیاری است).

درصورتی‌که سرور SQL Server شما در محل (On-premises) قرار دارد، ممکن است نیاز به انتخاب یک دروازه داده داخلی (On-premises data gateway) داشته باشید.

اگر برای اولین بار که به این پایگاه داده وصل می‌شوید، نوع احراز هویت را انتخاب کرده و اطلاعات ورود (نام کاربری و رمز عبور) را وارد کنید.

درصورتی‌که اتصال شما رمزگذاری نشده است (یعنی از https استفاده نمی‌کند) و در پنجره تنظیمات گزینه‌ای با عنوان «Use Encrypted Connection» وجود دارد، آن را غیرفعال کنید.

• روی دکمه «Next» کلیک کنید تا به مرحله بعدی بروید.
• در بخش «Navigator» داده‌های موردنیاز خود را انتخاب کنید. 

• درنهایت می‌توانید با استفاده از ابزارهای Power Query Online، داده‌ها را ویرایش و شکل‌دهی کنید.

ادغام Power Query با ابزار های دیگر Microsoft

این ابزار به‌دلیل سادگی و امکانات گسترده‌ خود در زمینه‌ دریافت داده از منابع مختلف و همچنین قابلیت‌های متنوعی که برای تغییر و دسته‌بندی داده‌ها ارائه می‌دهد، به محبوبیت رسیده است. این محبوبیت باعث شده سایر نرم‌افزارها نیز از موتور قدرتمند Power Query برای خواندن اطلاعات استفاده کنند.

Power Query در اکسل

پاور کوئری ابتدا در اکسل معرفی شد و همچنان به‌عنوان بخشی از قابلیت‌های استاندارد این نرم‌افزار باقی مانده است. برای دسترسی به آن، به تب «داده» در اکسل مراجعه کرده و به‌دنبال بخش «دریافت و تبدیل داده» بگردید. با استفاده از این قابلیت، می‌توانید داده‌ها را به مدل داده‌ اکسل (Power Pivot سابق) یا مستقیماً به یک برگه‌ اکسل بارگذاری کنید.

کاربرد Power Query در هوش تجاری (BI)

این ابزار قدرتمند به‌عنوان یکی از اجزای اصلی Microsoft Power BI، نقشی اساسی در فرآیند هوش تجاری (BI) ایفا می‌کند. سؤال اینجاست در هوش تجاری نقش Power Query چیست؟ این ابزار قدرتمند به کاربران کمک می‌کند تا داده‌ها را از منابع مختلف جمع‌آوری، تبدیل و آماده‌سازی کنند تا برای تجزیه‌وتحلیل و گزارش‌دهی در Power BI  آماده شوند. با استفاده از Power BI می‌توانید از همان فناوری Power Query برای انجام تمام تبدیلات ضروری روی داده‌ها بهره ببرید. دسترسی به این قابلیت ازطریق بخش‌های دیتا و کوئری در تب اصلی برنامه امکان‌پذیر است.

Power BI اولین ابزاری بود که از پاور کوئری انجین خارج از اکسل استفاده کرد. در Power BI Desktop، می‌توانید از ویرایشگر Power Query برای خواندن داده‌ها از منابع مختلف استفاده کنید. خروجی این کوئری در قالب یک مدل داده سازماندهی می‌شود. به عبارت دیگر، هر کوئری که داده‌ای را برمی‌گرداند، آن را به‌صورت یک جدول در اختیار شما قرار می‌دهد.

همچنین یک Power Query سبک‌تر در Power BI Service در دسترس است که به شما امکان می‌دهد داده‌ها را از فایل‌های ذخیره‌شده به‌صورت محلی یا در OneDrive بخوانید. با این حال، در اکثر مواقع استفاده از Power BI Desktop برای ایجاد کوئری‌ها بسیار کارآمدتر است.

یکپارچه سازی با Integration Services

علاوه‌بر موارد فوق، می‌توانید از یک منبع Power Query در Flow data یک بسته SSIS استفاده کنید. (SSIS مخفف SQL Server Integration Services است و برای خودکارسازی انتقال و تبدیل داده‌ها استفاده می‌شود.) فعلاً ویرایشگر بصری مانند اکسل یا Power BI برای SSIS وجود ندارد و باید کد زیر را که به زبان M در Power BI است، به‌صورت دستی در یک کادر متنی کپی و پیست کنید. برای آشنایی با نحوه استفاده از SSIS ، این مقاله را بخوانید.

let
    Source = Exchange.Contents("xyz@contoso.com"),
    Mail1 = Source{[Name="Mail"]}[Data],
    #"Expanded Sender" = Table.ExpandRecordColumn(Mail1, "Sender", {"Name"}, {"Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Sender", each ([HasAttachments] = true)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Subject] = "sample files for email PQ test") and ([Folder Path] = "\Inbox\")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Attachments"}),
    #"Expanded Attachments" = Table.ExpandTableColumn(#"Removed Other Columns", "Attachments", {"Name", "AttachmentContent"}, {"Name", "AttachmentContent"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Expanded Attachments", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Mail", each #"Transform File from Mail"([AttachmentContent])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Mail"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Mail", Table.ColumnNames(#"Transform File from Mail"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}})
in
    #"Changed Type"

با اینکه یکپارچه‌سازی پاور کوئری با SSIS هنوز درحال تکمیل است، اما می‌تواند به‌سادگی هرچه‌تمام، تبدیل‌های داده‌ای را که در گذشته با SSIS دشوار بود، انجام دهید.

 Azure Data Factory (مخفف ADF)

در ADF می‌توانید یک جریان داده Power Query ایجاد کنید. مزیت اجرای این ابزار قدرتمند در ADF این است که می‌تواند داده‌ها را در حجم بالا جابه‌جا و تغییر دهد؛ زیرا از کلاسترهای محاسباتی ADF استفاده می‌کند. این جریان داده را می‌توان در Pipeline معمولی ADF ادغام کرد تا بخشی از یک معماری بزرگ‌تر انتقال داده باشد.

تغییر داده ها با Power Query Data flow 

تصور کنید با یک ابزار قدرتمند، داده‌ها را از منابع مختلف جمع‌آوری و آن‌ها را مطابق نیازتان تغییر شکل دهید. این دقیقاً همان کاری است که پاور کوئری انجام می‌دهد، اما درون هر نرم‌افزار، محدود به همان نرم‌افزار است. اینجاست که Power Query Data Flows کمکتان می‌کند. Data Flows نسخه‌ ابری و مستقل از محصول Power Query است. 

به‌این‌ترتیب، شما می‌توانید داده‌ها را دریافت و تغییر شکل دهید، اما خروجی را به جای اکسل یا Power BI، در منابع دیگری مانند Dataverse یا Azure Data Lake Storage ذخیره کنید. این کار باعث می‌شود تا از خروجی Dataflows در سایر محصولات و سرویس‌ها نیز استفاده کنید. به بیان ساده، Dataflows قفل داده‌ها را باز کرده و آن‌ها را برای استفاده در هر جایی که نیاز دارید، در دسترس قرار می‌دهد.

منظور از قابلیت اتصال با گزینه های پیشرفته در Power Query چیست؟

چه در Power Query Desktop و چه در Power Query Online، امکان استفاده از گزینه‌های پیشرفته‌ برای تنظیم دقیق‌تر اتصال وجود دارد. در جدول زیر، با تک‌به‌تک این گزینه‌ها آشنا می‌شویم:

سخن پایانی

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

• تبدیل داده‌ها به فرمت دلخواه.
• جمع‌ آوری داده از منابع مختلف.
• اشتراک‌ گذاری داده‌ها در یک منبع دلخواه.
• دسته‌ بندی و آماده‌سازی داده‌ها برای تجزیه‌ و تحلیل.

در این مقاله، به بررسی کامل این ابزار، کاربرد، مزایا، معایب و نحوه نصب آن پرداختیم. اگر سؤالی در این زمینه دارید، می‌توانید آن را در بخش نظرات نیک آموز بنویسید تا راهنمایی‌تان کنیم.