پاور کوئری چیست؟ (استفاده از آن در اکسل و نرم افزار power BI)

پاور کوئری یک ابزار تبدیل و آماده سازی داده است که توسط شرکت مایکروسافت توسعه یافته است.

این ویرایشگر یک رابط گرافیکی برای اعمال تغییرات دارد و حل وظایف رایج داده را آسان می‌کند.

معمولاً زمان زیادی برای کارهای دستی تکراری مانند ترکیب ستون‌ها و اعمال فیلتر صرف می‌شود که ابزار پاور کوئری انجام چنین کارهایی را بسیار آسان می‌کند.

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

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

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

این ویرایشگر بخشی از Microsoft Excel و Power BI است که یک رابط قدرتمند و کاربرپسند برای جستجو، تبدیل و ترکیب داده‌ها از فرمت‌ها و مکان‌های مختلف در اختیار کاربران قرار می‌دهد.

پاور کوئری به کاربر، این امکان را می‌دهد که بدون زحمت اقدامات زیر را روی داده‌ها انجام دهد و مطمئن شود که در قالب‌های مورد نظر برای اهداف تجزیه و تحلیل یا گزارش قرار گرفته اند:

• تمیز کردن
• فیلتر کردن
• شکل دهی
• تغییر فرمت

یکی از ویژگی‌های کلیدی پاور کوئری، توانایی آن در اتوماتیک سازی و تکرار کوئری‌ها است.

کاربر به راحتی می‌تواند اتصالات داده و داده‌های جدید را بازیابی کند و همان تغییرات را اعمال کند تا تجزیه و تحلیل خود را بدون نیاز به مداخله دستی به‌روز نگه دارد.

همچنین این ابزار، طیف گسترده ای از توابع تبدیل داده را ارائه می‌دهد، مانند:

• تقسیم ستون‌ها
• ادغام جدول‌ها
• جمع آوری داده‌ها
• استفاده از فرمول‌های سفارشی

که آن را به یک ابزار همه کاره برای کارهای آماده سازی داده تبدیل کرده است.

این تبدیل‌ها را می‌توان به راحتی با استفاده از یک رابط گرافیکی اعمال کرد و دیگر نیاز به کدنویسی یا اسکریپت نویسی‌های پیچیده نخواهد بود.

1-1# اجزای پاور کوئری

ویرایشگر پاور کوئری دارای اجزای زیر است و به کاربر کمک می‌کند تا به راحتی آن را پیمایش کند و بسنجد:

• Ribbon: مانند اکسل Ribbon به کاربر کمک می‌کند تا با کلیک یک دکمه، تمامی عملیات اصلی خود را انجام دهد.
• نمای داده: این بخش، بیشترین حجم را در صفحه اشغال می‌کند و راه اصلی کاربر است به منظور بررسی و ارزیابی داده‌هایی که وارد کرده است.
• نوار فرمول: در حالی که پاور کوئری به کاربر کمک می‌کند تا از یک رابط گرافیکی، برای کلیک کردن روی تغییر شکل‌ها استفاده کند؛ مخفیانه آن را به زبانی به نام M – Code تبدیل می‌کند.
  نوار فرمول به کاربر کمک می‌کند تا ببیند دقیقا چه عملیاتی در حال انجام است و همچنین امکان اصلاح هر مرحله را در صورت لزوم خواهد داد.
• Query Pane: اگر کاربر با بیش از یک کوئری یا جدول کار کند، این ابزار امکان جابه‌جایی راحت بین صفحه‌های گوناگون را به او خواهد.
• Applied Steps & Properties: در این ابزار کاربر می‌تواند تغییراتی که در مجموعه داده‌ها اعمال شده را ببیند و به درخواست خود یک نام اختصاص دهد.

2# موضوعات تحت پوشش پاور کوئری

پاور کوئری، طیف گسترده ای از موضوعات را پوشش می‌دهد که به کاربران کمک می‌کند تا به صورت کارآمدی داده‌ها را تبدیل کنند.

در ادامه با برخی از موضوعات کلیدی آشنا خواهیم شد.

1-2# دریافت و استخراج داده

پاور کوئری به کاربران این امکان را می‌دهد که داده‌ها را از منابع مختلف مانند موارد زیر استخراج کنند:

• پایگاه داده
• صفحات وب
• فایل اکسل
• فایل‌های متنی

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

2-2# فیلتر و تمیز کردن داده‌ها

کاربران می‌توانند از فیلترهایی برای حذف داده‌های غیرضروری و تمرکز روی اطلاعات مرتبط استفاده کنند.

همچنین ابزارهایی را برای تمیز کردن و استاندارد کردن داده‌ها با حذف موارد تکراری، رفع ناسازگاری‌ها و قالب بندی داده‌ها به شیوه‌ای یکسان فراهم می‌کند.

3-2# تبدیل داده

این ابزار کاربران را قادر می‌سازد تا داده‌ها را با استفاده از طیف گسترده ای از عملیات تبدیل کنند که شامل موارد زیر است:

• تقسیم ستون‌ها
• ادغام جدول‌ها
• چرخش داده‌ها
• تجمیع مقادیر

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

4-2# جستجو و اتصال داده

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

با ویژگی‌هایی مانند منطق شرطی، کاربر می‌تواند داده‌ها را بر اساس معیارهای خاص، فیلتر و گروه بندی کند.

همچنین از اتصال جدول‌ها به منظور ترکیب داده‌ها از منابع مختلف و مطابقت آنها بر اساس فیلدهای مشترک پشتیبانی می‌کند.

5-2# بارگذاری و به‌روزرسانی داده‌ها

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

کاربران همچنین می‌توانند به‌روزرسانی اتوماتیک داده‌ها را تنظیم کنند تا تجزیه و تحلیل‌های موجود را به‌روز نگه دارند.

3# کاربردهای پاور کوئری

پاور کوئری به صورت گسترده در مواردی که مربوط به داده در صنایع مختلف است، استفاده می‌شود.

محصولات و خدمات مایکروسافت که می‌توان پاور کوئری را در آنها پیدا کرد، عبارت اند از:

• اکسل در ویندوز
• اکسل در مک
• Power BI
• Power Apps
• Power Automate
• Power BI Report Server
• Azure Data Factory
• Data Factory in Microsoft Fabric
• SQL Server Integration Services
• SQL Server Analysis Services
• Dynamics 365 Customer Insights

در ادامه نیز با چند کاربرد رایج این ابزار آشنا خواهیم شد.

1-3# پاکسازی و تبدیل داده‌ها

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

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

تبدیل داده‌های معمولی که در پاور کوئری انجام می‌شود، عبارت اند از:

• Pivot / Unpivot
• تقسیم / ادغام ستون‌ها
• فیلتر کردن داده‌ها
• ایجاد ستون‌های سفارشی
• حذف ردیف‌های خطا
• تغییر انواع داده‌ها
• پر کردن مقادیر

2-3# مراحل تکرارشونده

پاور کوئری هر یک از مراحلی که برای تبدیل داده انجام شده است را نیز به خاطر می‌آورد.

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

3-3# ترکیب چند جدول

پاور کوئری به ما این امکان را می‌دهد که چندین جدول از داده‌های مرتبط را با هم ترکیب کنیم.

کاربران اکسل با استفاده از فرمول‌های کوئری به همان عملیات دست پیدا می‌کنند.

آنها به 1 میلیون ردیف محدود شده و متوجه می‌شوند که مدل‌ها با افزودن تعداد زیادی فرمول کندتر شده اند.

در عوض، پاور کوئری با استفاده از ستون‌های منطبق به جدول‌ها می‌پیوندد، به هیچ فرمولی نیاز ندارد و برای کل مجموعه داده در چند ثانیه به دست می‌آید.

4-3# ترکیب چندین فایل

پاور کوئری شامل عملکردی برای ترکیب یکپارچه داد‌ها از چندین فایل مشابه است.

5-3# اتصال به منابع داده مرکزی

پاور کوئری شامل تعداد زیادی کانکتور (Connectors) است که به کاربر اجازه می‌دهد تا از دیتابیس SQL، فضای ذخیره سازی ابری و فایل‌های محلی داده استخراج کند.

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

4# چه کسانی از Power Query استفاده می‌کنند؟

افرادی که از این ابزار استفاده می‌کنند به شرح زیر هستند:

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

5# نحوه فعال کردن پاور کوئری

Power Query به عنوان یک افزونه رایگان در Excel 2010 و Excel 2013 در دسترس است.

برای فعال کردن پاور کوئری، ابتدا آن را از سایت مایکروسافت دانلود کنید.

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

پس از آن Power Query دانلود خواهد شد.

این ویرایشگر، یک ابزار داخلی است که با Excel 2016 استارت خورده و در بخش Get & Transforms Data و در زیر گزینه Data Tab موجود است.

6# چگونگی کارکرد Power Query

Power Query به صورت مؤثر در 3 مرحله کار می‌کند:

1. ابتدا به منابع داده مورد نظر و معمولاً با حداقل کد متصل می‌شویم و آن داده‌ها را در محیط پاور کوئری وارد می‌کنیم.
2. سپس، تبدیل‌هایی را که می‌خواهیم را اعمال می‌کنیم تا تغییرات زیر روی داده‌ها اعمال شوند:
   1. فیلتر
   2. ترکیب
   3. مرتب سازی
   4. گروه بندی
   5. ادغام
3. در نهایت ما انتخاب می‌کنیم که آن داده کجا برود.
   می‌توانیم آنها را در یک مدل داده Power Pivot بارگذاری کنیم، در یک صفحه اکسل بارگذاری کنیم یا به سادگی از آن به عنوان یک جدول مرجع برای سایر جستجوها استفاده می‌کنیم.

7# استفاده از پاور کوئری در اکسل و Power BI

پاور کوئری در Microsoft Excel و Microsoft Power BI موجود است.

1-7# پاور کوئری در اکسل

از Excel 2016، پاور کوئری به صورت بومی در دسترس بوده است؛ اما اگر نسخه اکسل شما قبل از 2016 است ممکن است نیاز داشته باید که پاور کوئری را به صورت یک افزونه دانلود کنید.

در نسخه‌های اخیر، پاور کوئری از طریق تب Data در اکسل قابل دسترسی است و از چندین دکمه در قسمت Get & Transforms Data تشکیل شده است.

2-7# پاور کوئری در پاور بی آی

پاور بی آی یک ابزار داشبورد پیشرفته در بازار مایکروسافت است که به کاربران اجازه می‌دهد تا:

• داده‌ها را وارد و تبدیل کنند.
• یک مدل داده به وجود آورند.
• داشبوردهای تعاملی ایجاد کنند.

Power BI از تکنولوژی پاور کوئری یکسان برای تبدیل داده‌های مورد نیاز استفاده می‌کند که از طریق Data و Query در تب Home قابل دسترسی است.

8# ایجاد تغییرات با استفاده از پاور کوئری

در این قسمت به بررسی توابع مختلف تبدیل پرداختیم که با چند کلیک به راحتی قابل انجام هستند.

1-8# توابع قالب بندی متن

در این بخش نحوه قالب بندی متن با استفاده از حروف بزرگ، کوچک و همچنین چگونگی استفاده از عملیات Trim را خواهید آموخت.

1) حروف بزرگ

برای این کار مراحل زیر را طی کنید:

1. داده‌های مورد نیاز را در ویرایشگر بارگیری کنید.
   این کار را می‌توان با انتخاب منبع داده از قسمت Get & Transform Data و تب Excel Data دریافت کرد.
   با این کار ویرایشگر باز می‌شود و به کاربر اجازه ویرایش داده را خواهد داد.
2. روی نام ستون کلیک کنید و سپس به تب Transform بروید.
   در این حالت، گزینه‌های مختلفی را مشاهده خواهید کرد.
   با کلیک روی گزینه Format Text یک منوی کشویی با گزینه ویرایش UPPERCASE باز خواهد شد.
3. در نهایت، با انتخاب گزینه UPPERCAASE Edit تمامی حروف متن در ستون انتخابی به حروف بزرگ تبدیل می‌شوند.

2) حروف کوچک

برای تبدیل داده‌ها به حروف کوچک باید مراحل زیر را انجام داد:

1. داده‌های مورد نیاز را در ویرایشگر پاور کوئری بارگیری کنید.
2. روی نام ستون کلیک کنید و سپس روی تب Transform رفته که گزینه‌های مختلفی را نمایش می‌دهد.
   روی گزینه قالب بندی متن، یک منوی کشویی با گزینه ویرایش متن با حروف کوچک نمایش داده می‌شود.
3. همان گونه که خواهید دید، تمام متنی که از ستون مورد نظر انتخاب شده به حروف کوچک تبدیل می‌شود.

3) TRIM

مراحل عملیات TRIM نیز به صورت زیر خواهد بود:

1. داده‌های مورد نیاز را در ویرایشگر پاور کوئری بارگیری کنید.
2. برای حذف تمام فضاهای سفید اضافی از داده‌ها، روی نام ستون کلیک کنید و Transform را انتخاب کنید تا گزینه‌های مختلفی که نمایان می‌شود را ببینید.
   با کلیک روی گزینه Format یک منوی کشویی با گزینه ویرایش متن با نام TRIM نمایش داده خواهد شد.
3. در نهایت، با انتخاب گزینه TRIM Edit تمامی فضای سفید اضافی در ستون مورد نظر حذف خواهد شد.

2-8# تقسیم یک ستون با استفاده از جداکننده

برای تقسیم ستون با استفاده از جداکننده مراحل زیر را انجام دهید:

1. داده‌های مورد نیاز را در ویرایشگر پاور کوئری بارگیری کنید.
2. برای جدا کردن ستون با کمک جداکننده از داده‌ها، روی Transform کلیک کنید و سپس گزینه Split Column را انتخاب کنید.
   با این کار یک منوی کشویی با گزینه ای برای تقسیم داده‌ها توسط جداکننده نمایش داده می‌شود.
3. در این مرحله یک کادر نمایش داده می‌شود که در آن می‌توانید یک جداکننده را انتخاب کرده و سپس روی OK کلیک کنید.
4. در این مرحله داده‌ها به دو ستون مربوط به جداکننده تقسیم می‌شوند.

9# مزایا و معایب استفاده از پاور کوئری

مزایا، معایب و چالش‌های پاور کوئری به صورت زیر هستند.

1-9# مزایا

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

• دارای رابط کاربرپسند و ویژگی دراگ و دراپ (Drag and drop)
• ادغام یکپارچه با سایر ابزارهای مایکروسافت مانند اکسل و Power BI
• دارای طیف گسترده ای از منابع داده پشتیبانی شده، از جمله سرویس‌های مبتنی بر ابر و قالب‌های فایل
• قابلیت‌های پیشرفته تبدیل داده از جمله توابع سفارشی و پشتیبانی از زبان M
• ویژگی پاکسازی اتوماتیک داده‌ها و تشخیص خطا

2-9# معایب

معایب پاور کوئری به صورت زیر است:

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