در نرم‌افزار اکسل مجموعه آفیس مایکروسافت برای استفاده از مجموعه داده‌ها که از فایل‌ها و دیتابیس‌ها و منابع آنلاین دیگر تهیه شده، می‌توانید از Power Query استفاده کنید که قابلیت‌های زیادی دارد. به عنوان مثال می‌توانید اطلاعات موجود در دو فایل اکسل را به راحتی وارد کرده و به شکل جداولی بزرگ‌تر، ترکیب کنید.
در این مقاله به آشنایی با Microsoft Power Query در Excel و آموزش مقدماتی استفاده از آن در قالب یک مثال ساده می‌پردازیم.
آشنایی با Microsoft Power Query در اکسل
توانمندی Power Query این است که می‌توانید داده‌هایی را از چند منبع مختلف تهیه کنید و سپس داده‌ها را ویرایش و پردازش کرده و در نهایت در صفحه‌ای از یک فایل جدید اکسل وارد کنید.
ابزاری به اسم Microsoft Power Query در اکسل ۲۰۱۶ و نسخه‌های جدیدتر قابل استفاده است و در نسخه‌های قدیمی وجود ندارد.
یکی از منابع وارد کردن داده، خود اکسل است! در واقع می‌توانید دیتایی که در صفحات دیگر از یک فایل موجود است را به عنوان منبع داده‌ها انتخاب کنید یا از اطلاعاتی که در یک فایل دیگر موجود است، استفاده کنید.
در این مقاله برای توضیح استفاده از Power Query، اطلاعات موجود در دو فایل اکسل را به عنوان منبع یا سورس در نظر می‌گیریم و این اطلاعات را به کمک پاور کوئری ترکیب می‌کنیم.
فرض کنید دو فایل اکسل به نام‌های Cars.xlsx و Trucks.xslx داریم که در فایل اول مشخصات چند خودرو و در فایل دوم مشخصات چند کامیون ذکر شده است. مشخصات شامل برند، مدل، رنگ و سال تولید می‌شود که ساده و روشن است. به محتویات صفحهی اکسل در این دو فایل توجه کنید:
می‌خواهیم یک فایل دیگر Excel به اسم Vehicles.xslx بسازیم که در آن اطلاعات خودروها و کامیون‌ها جمع می‌شود.
البته برای ساده شدن مراحل کار فرض کرده‌ایم که هدرهای این دو سری داده یکسان است و در واقع مشخصات محصولات مشابه است. اما با استفاده از پاور کوئری می‌توانید داده‌های پیچیده‌تر که یه عنوان مثال ستون‌های سری‌های داده یکسان نیست را هم ترکیب کنید.
بارگذاری داده‌ها از فایل‌های اکسل
گام نخست این است که سری‌های داده را بارگذاری و ویرایش کنیم. برای این مهم می‌توانید از Power Query Editor استفاده کنید.
ابتدا فایل Vehicles.xlsx را باز کنید و سپس روی تب Data در نوار ابزار بالای صفحه کلیک کنید. در این تب روی گزینه‌ی Get Data کلیک کنید. با توجه به اینکه منبع داده‌ها، فایل اکسل است، گزینه‌ی From File و سپس From Workbook را انتخاب کنید.
همان‌طور که در تصویر زیر مشاهده می‌کنید، گزینه‌های منوی کرکره‌ای باز شده متعدد است. در واقع دریافت اطلاعات اولیه از منابع مختلف مثل دیتابیس، سرویس Azure مایکروسافت و دیگر منابع آنلاین و سایر منابع است.
پنجره‌ی انتخاب فایل باز می‌شود. فایل Cars.xlsx را به عنوان فایل اول انتخاب کنید. پاور کوئری مشغول به کار می‌شود و اطلاعات موجود در فایل را پردازش کرده و پیش‌نمایشی از آن ارایه می‌کند. در ستون کناری نام فایل و نام صفحات موجود در فایل را مشاهده می‌کنید و می‌توانید روی دیگر صفحات فایل انتخاب شده کلیک کنید و اطلاعات آن را مشاهده کنید. در نهایت پس از انتخاب کردن صفحهی موردنظر که در مثال ما Cars نام دارد، روی Transform Data کلیک کنید تا داده‌ها ایمپورت و در پنجره‌ی ادیتور باز شود.
کار روی داده‌ها در پنجره‌ی Power Query Editor
پنجره‌ی ویرایش داده‌های پاور کوئری گزینه‌های زیادی دارد و کارهایی زیادی می‌توان انجام داد. همان‌طور که در تصویر زیر مشاهده می‌کنید داده‌های وارد شده به صورت جدول و درست شبیه جداول Excel در ستون‌ها و ردیف‌ها چیده شده است. نام ستون‌ها نیز در ردیف بالایی موجود است. البته داده‌هایی که از دیگر منابع وارد می‌کنید هم به همین شکل نمایش داده می‌شود.
در سمت راست منویی به اسم Query Settings موجود است که در آن مراحل پردازش داده‌ها ذکر می‌شود. در واقع اگر تغییراتی در داده‌های اولیه داده‌اید که اشتباه بوده، می‌توانید مراحل و تغییرات را حذف کنید!
در سمت چپ آیکون Queries دیده می‌شود که با کلیک روی آن، منویی در سمت چپ باز خواهد شد. در این منو نام جدولی که روی آن کار می‌کنید نمایش داده می‌شود. اگر چند جدول داده داشته باشید هم نامشان در سمت چپ لیست می‌شود.
افزودن داده‌های مختلف از منابع بعدی
برای ترکیب کردن داده‌ها می‌بایست داده‌های منابع بعدی را هم ایمپورت کنیم. منبع دوم در این مثال، یک فایل اکسل دیگر است. برای افزودن داده‌های این منبع، در پنجره‌ی Power Query Editor روی دکمه‌ی New Source که در تب Home و در گوشه‌ی بالا و سمت راست واقع است، کلیک کنید.
گزینه‌ی File و سپس Excel را انتخاب کنید تا پنجره‌ی انتخاب فایل باز شود. حال فایل دوم که در مثال ما Trucks.xlsx است را انتخاب کنید. این بار هم پنجره‌ی Navigator باز می‌شود و می‌توانید نام فایل انتخاب شده و صفحه‌های موجود از آن را در ستون کناری و پیش‌نمایش داده‌های آن صفحه را در سمت راست مشاهده کنید. در نهایت با انتخاب کردن صفحهی موردنظر و کلیک روی OK، اطلاعات جدید را وارد ویرایشگر پاور کوئری کنید.
همان‌طور که مشاهده می‌کنید اکنون در نوار ابزار Queries در سمت چپ، دو جدول داده اضافه شده است.
ترکیب جداول داده در Power Query Editor
پس از آماده‌سازی داده‌ها نوبت می‌توانید از داده‌ها به شکل‌های مختلفی استفاده کنید و یکی از کارهای ساده، تلفیق داده‌ها است. برای ترکیب کردن اطلاعات، روی جدول cars کلیک کنید و سپس در نوار ابزار بالای صفحه و تب Home روی Append Queries که در بخش Combine قرار دارد، کلیک کنید.
به این ترتیب پنجره‌ی Append باز می‌شود و در مورد جدولی که می‌خواهید به جدول انتخاب شده اضافه کنید، سوال می‌شود. با توجه به اینکه روی جدول Cars کلیک کرده بودید، جدول جدید Trucks خواهد بود. بنابراین از منوی کرکره‌ای Table to append، گزینه‌ی trucks را انتخاب کنید و سپس روی OK کلیک کنید.
نتیجه‌ی ترکیب کردن این دو جدول را در تصویر زیر مشاهده می‌کنید:
همان‌طور که مشاهده می‌کنید اطلاعات تمام خودروها و کامیون‌ها در یک جدول بزرگ‌تر ترکیب شده است. در سمت راست و لیست Applied Steps هم یک مرحله‌ی جدید که Appended Query نام دارد، اضافه شده است.
استفاده از داده‌های پردازش شده توسط پاور کوئری در اکسل
آخرین گام این است که حاصل ویرایش کردن داده‌های وارد شده به کمک ادیتور پاور کوئری را در یک صفحهی اکسل قرار بدهید و برای کارهای بعدی آن را ذخیره کنید. برای این منظور در پنجره‌ی Power Query Editor روی گزینه‌ی Close & Load در سمت بالا و چپ کلیک کنید. نتیجه این است که اطلاعات در صفحهی Vehicles به شکل جدول قرار می‌گیرد.
با بستن پنجره‌ی پاور کوئری ادیتور، مراحل یا Query Steps ذخیره می‌شود و منویی به اسم Workbook Queries در سمت راست صفحهی اکسل باز می‌شود که در آن کوئری‌ها که در مثال ما دو فایل اکسل بوده، لیست می‌شود. با نگاهی به این بخش می‌توان منابع داده را شناسایی کرد.
makeuseofسیاره‌ی آی‌تی