پاور کوئری چیست و روش کار با Power query در اکسل

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

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

فهرست 

✅Power Query چیست؟

✅فاز Extract در Power Query

✅فاز Transfom در Power Query

✅فاز Load در PowerQuery

✅روش کار با پاور کوئری

✅بارگذاری داده‌ها از فایل‌های اکسل

✅کار روی داده‌ها در پنجره‌ی Power Query

✅افزودن داده‌های مختلف از منابع بعدی

✅ترکیب جداول داده در Power Query

✅استفاده از داده‌های پردازش شده توسط پاور کوئری در اکسل

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

✅سوالات متداول درباره Power Query

✅جمع بندی

Power Query چیست؟

افزونه Power query در واقع یه نرم افزار ETL هستش. حرف E  از کلمه Extract گرفته شده و به معنی استخراج داده ها از یه منبع دیگه هستش. حرف T هم از کلمه Transform گرفته شده که به معنی تغییر شکل داده ها هست (به هر جور نیاز به تغییر ساختار و مقدار داده ها، ساخت ستون های جدید و تغییرات و پاکسازی Transform  گفته می شه). حرف L هم از کلمه Load به معنای بارگذاری داده ها به دست اومده. در نتیجه Power Query به کاربران کمک می کنه تا با  Excel داده ها رو از منابع مختلف استخراج کنن و پس از تغییر به فرم مورد نظر خودشون، اونارو وارد نرم افزار کنن. بسیاری از مسائلی که قبل تر به سختی حل می شدن، حالا با پاور کوئری خیلی راحت حل می شن. از جمله مزایای پاور کوئری می تونیم به مواردی مثل امکان اتصال به پایگاه داده های گوناگون، پاکسازی داده ها و ادغام و چسباندن دیتا ها به هم اشاره کنیم. Power Query  در اکسل ۲۰۱۶ و نسخه‌های جدیدتر قابل استفاده بوده و در نسخه‌های قدیمی این قابلیت وجود نداره.

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

فاز Extract در Power Query

ابزار Power Qeury باید بتونه داده ها رو از منابع گوناگونی بخونه که به این فاز Extract می گن. شاید این سوال واستون به وجود اومده باشه که قبل از پاور کوئری هم تو اکسل می شد داده ها رو از منابع مختلف خوند، پس مزیت این ابزار چیه؟ در جواب باید بگیم که در پاورکوئری امکانات ویژه ای واسه کاربرایی که به صورت حرفه ای کار می کنن گنجونده شده که یکی از اونا خوندن یکباره 100 ها فایل در یک فولدر هستش که قبلا این امکان وجود نداشت.

اینکار با این ابزار تنها با چند کلیک قابل انجامه و در Power Query می تونین صد ها فایل اکسل، فایل CSV  و ... یکباره بخونین.

فاز Transfom در Power Query

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

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

فاز Load در PowerQuery

در ابزار ETL این فاز بسیار قوی هستش، ولی در PowerQuery در این فاز فقط داده ها تو اکسل بارگذاری می شن.

روش کار با پاور کوئری

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

در این مثال ما دو فایل اکسل به نام‌های Cars.xlsx و Trucks.xslx داریم که در فایل Cars.xlsx مشخصات چند خودرو و در فایل Trucks.xslx مشخصات چند کامیون آورده شده. موارد ذکر شده واسه ماشینا هم مواردی مثل برند، مدل، رنگ و سال تولید هستن.

 

 

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

البته واسه اینکه مراحل کار ساده بشن فرض رو بر این گذاشتیم که هدرهای این دو سری داده با هم یکیه و مشخصات محصولات در دو فایل با هم تشابه دارن. البته باید بدونین که شما با استفاده از پاور کوئری می‌تونین داده‌های پیچیده‌تری که ستون‌های سری‌های داده اون ها با هم یکسان نیست رو هم ترکیب کنین.

بارگذاری داده‌ها از فایل‌های اکسل

قدم اول اینه که سری‌های داده را بارگذاری و ویرایش کنیم. پس ابتدا فایل Vehicles.xlsx رو باز کنین و بعد روی تب Data که  در نوار ابزار بالای صفحه قرار گرفته کلیک کنین. در این تب گزینه‌ی Get Data رو یافته و روش کلیک کنین. چون منبع داده‌هامون در این مثال، فایل اکسله ، گزینه‌ی From File  و بعد From Workbook  رو انتخاب کنین.

 

در پنجره‌ی انتخاب فایل، فایل Cars.xlsx رو به عنوان فایل اول انتخاب کنین. در این لحظه پاور کوئری شروع به کار می کنه و بعد از پردازش اطلاعات موجود در فایل، یه پیش‌ نمایش ازش بهتون نشون می ده. در ستون بغلی اسم فایل و اسم صفحات موجود در فایل رو می تونین ببینین و رو بقیه صفحات فایل انتخاب شده کلیک کنین تا اطلاعات اونارو ببینین. بعد از اینکه صفحه‌ی Cars رو انتخاب کردین، روی Transform Data  کلیک کنین تا داده‌ها Import بشن و در  پنجره‌ی ادیتور باز بشن.

کار روی داده‌ها در پنجره‌ی Power Query Editor

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

 

 

کار روی داده‌ها در پنجره‌ی Power Query Editor - خدمات کامپیوتری تلفنی

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

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

افزودن داده‌های مختلف از منابع بعدی

واسه ترکیب کردن داده‌ها، باید داده‌های منابع بعدی رو هم Import کنین. منبع دوم ما تو این مثال، یه فایل اکسل دیگه هستش. واسه اضافه کردن داده‌های منبع دوم، در تب Home  از گوشه‌ی بالا سمت راست پنجره‌ی Power Query Editor رو باز کنین و روی دکمه‌ی New Source   کلیک کنین.

 

 

روی گزینه‌ی File و سپس Excel‌ کلیک کنین. از پنجره ای که براتون باز می شه فایل Trucks.xlsx  رو انتخاب کنین. با این کار پنجره‌ی Navigator  باز می‌شه و می‌تونین اسم فایل انتخاب شده و صفحه‌های موجود از آن رو تو ستون کناری و پیش‌ نمایش داده‌های اون صفحه در سمت راست ببینین. در انتها صفحه‌‌ی مورد نظرتون رو انتخاب کنین و روی OK کلیک کنین تا اطلاعات جدید وارد ویرایشگر پاور کوئری بشه.

حالا در نوار ابزار Queries در سمت چپ، دو تا جدول داده اضافه شدن.

 

ترکیب جداول داده در Power Query Editor

واسه ترکیب کردن اطلاعات دو تا فایلی که وارد کرده بودین، روی جدول cars کلیک کنین. از نوار ابزار بالای صفحه وارد تب Home بشین و از بخش Combine  روی Append Queries  کلیک کنین.

با این کار پنجره‌ی Append بهتون نشون داده می شه و ازتون  در مورد جدولی که می‌خواین به جدول انتخاب شده اضافه کنین، سوال می کنه. چون شما روی جدول Cars کلیک کرده بودین، جدول جدیدتون Trucks می شه. پس از منوی کرکره‌ای Table to append، روی گزینه‌ی trucks کلیک کنین و سپس OK رو انتخاب کنین.

 

در تصویر زیر نتیجه‌ی ترکیب این دو جدول نشون داده شده:

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

استفاده از داده‌های پردازش شده توسط پاور کوئری در اکسل

تو گام آخر باید نتیجه ی ویرایش کردن داده‌هایی که توسط ادیتور پاور کوئری وارد شده بودن رو در یه صفحه‌ی اکسل قرار بدین و اونوذخیره کنین. واسه انجام این کار در پنجره‌ی Power Query Editor گزینه‌ی Close & Load که در سمت بالا و چپ قرار گرفته رو انتخاب کنین. با این کار اطلاعات در صفحه‌ی Vehicles به صورت جدول قرار می‌گیره.

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

استفاده از داده‌های پردازش شده توسط پاور کوئری در اکسل - ارتباط با کارشناسان کامپیوتری

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

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

• ابزاری برای خواندن داده هستش.
• ابزاری برای پاکسازی داده هستش و با اون می تونین اقدام به حذف ستون ها و سطر ها و کاراکتر های زائد کنین.
• ابزارتغییر شکل دهنده داده هستش که می تونه داده هایی که به صورت Cross یا  Pivot هستن رو از این حالت دربیاره.
• یه ابزار واسه ترکیب و ادغام داده ها از فایل ها و شیت های گوناگون و تبدیل اونها به یک فایل هستش.
• یه ابزار واسه ادغام منابع مختلف داده به یک منبع منسجمه.
• ابزاری واسه نظارته که باهاش می تونین اقدام به مقایسه لیستی از داده ها واسه پیدا کردن شباهت ها و تفاوت ها کنین.
• یه ابزار واسه خزیدن و خوندن داده های یه وب سایته که باهاش می تونین خیلی راحت داده های جدولی یه سایت رو به یه فایل اکسل یا Power BI منتقل کنین.
• ابزاری واسه تولید داده هستش.

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

سوالات متداول درباره Power Query

1.     Power Query  چیه؟

Power Query یه موتور تبدیل داده و آماده سازی داده هستشو این ابزار دارای یه رابط گرافیکی واسه دریافت داده ها از منابع و یک ویرایشگر Power Query برای اعمال تغییرات هستش.

2.     Power Query چه کاربردهایی داره؟

با Power Query می‌تونین داده‌های خارجی را وارد اکسل کرده و سپس به اون داده‌ها شکل بدین، مثلاً یه ستون رو حذف کنین، جداول رو ادغام کنین و ... .

جمع بندی

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

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

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