ابزار Power Query در اکسل

چهار مرحله برای استفاده از Power Query وجود دارد.

1. اتصال برقراری ارتباط با داده‌ها در فضای ابری، سرویس یا محلی

2. داده های Shape را برای برآورده کردن نیازهای خود تغییر    دهید، در حالی که منبع اصلی بدون تغییر باقی می ماند

3.    داده ها را از چندین منبع ترکیب کنید تا یک نمای منحصر به فرد به داده ها داشته باشید

4. بارگیری    پرس و جو خود را کامل کنید و آن را در یک کاربرگ یا مدل داده بارگیری کنید و به طور دوره ای آن را بازخوانی کنید.

بخش های بعدی هر مرحله را با جزئیات بیشتری بررسی می کند. 

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

پس از وارد کردن، می توانید داده ها را برای اضافه کردن، تغییرات و حذف ها از منبع داده خارجی بازخوانی کنید.

تبدیل داده ها به معنای اصلاح آنها به نحوی برای برآورده کردن نیازهای تجزیه و تحلیل داده های شما است. به عنوان مثال، می توانید یک ستون را حذف کنید، نوع داده را تغییر دهید یا ردیف ها را فیلتر کنید. هر یک از این عملیات یک تبدیل داده است. این فرآیند اعمال تبدیل (و ترکیب) به یک یا چند مجموعه داده را شکل دادن به داده ها نیز می نامند.

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

Power Query از یک پنجره اختصاصی به نام Power Query Editor برای تسهیل و نمایش تبدیل داده ها استفاده می کند. می‌توانید ویرایشگر Power Query را با انتخاب Launch Query Editor از دستور Get Data در گروه Get & Transform Data باز کنید، اما هنگامی که به منبع داده متصل می‌شوید، درخواست جدیدی ایجاد می‌کنید یا درخواستی را بارگیری می‌کنید، این ویرایشگر باز می‌شود.

ویرایشگر Power Query هر کاری را که با داده‌ها انجام می‌دهید با ثبت و برچسب‌گذاری هر تبدیل یا مرحله‌ای که روی داده اعمال می‌کنید، پیگیری می‌کند. خواه این تبدیل یک اتصال داده، حذف ستون، ادغام یا تغییر نوع داده باشد، می‌توانید هر تبدیل را در بخش APPLIED STEPS در قسمت Query Settings مشاهده و تغییر دهید .

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

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

می‌توانید با ضمیمه کردن یا ادغام آنها، چندین پرس‌وجو را در کتاب کار اکسل خود ترکیب کنید. عملیات Append و Merge بر روی هر درخواستی با شکل جدولی انجام می شود و مستقل از منابع داده ای است که داده ها از آن می آیند.

Append       یک عملیات ضمیمه یک پرس و جو جدید ایجاد می کند که شامل تمام ردیف های یک پرس و جو اول و سپس تمام ردیف های یک پرس و جو دوم است. شما می توانید دو نوع عملیات الحاقی را انجام دهید:

• Intermediate Append     برای هر عملیات الحاق یک پرس و جو جدید ایجاد می کند.

• Inline Append     داده ها را به درخواست موجود شما اضافه می کند تا زمانی که به نتیجه نهایی برسید.

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

دو راه اصلی برای بارگیری پرس و جوها در کتاب کار شما وجود دارد:

• از ویرایشگر Power Query، می توانید از دستورات Close و Load در گروه Close در تب Home استفاده کنید.

• از صفحه پرس و جوهای کتاب کار اکسل (انتخاب پرس و جوها و اتصالات )، می توانید روی یک درخواست کلیک راست کرده و  Load To را انتخاب کنید .

همچنین می‌توانید با استفاده از کادر محاوره‌ای گزینه‌های پرس و جو (انتخاب فایل > گزینه‌ها و تنظیمات > گزینه‌های پرس و جو ) گزینه‌های بارگذاری خود را دقیق تنظیم کنید تا نحوه مشاهده داده‌های خود و مکان بارگیری داده‌ها را انتخاب کنید، چه در یک کاربرگ یا یک مدل داده (که یک منبع داده رابطه‌ای از چندین جدول است که در یک کتاب کار قرار دارند). 

برای بیش از ده سال، Power Query در Excel برای ویندوز پشتیبانی می‌شود. اکنون، اکسل در حال گسترش پشتیبانی از Power Query در Excel برای Mac و اضافه کردن پشتیبانی به Excel برای وب است. این بدان معنی است که ما Power Query را در سه پلتفرم اصلی در دسترس قرار می دهیم و محبوبیت و عملکرد Power Query را در بین مشتریان اکسل نشان می دهیم. برای اطلاعیه های آینده در  نقشه راه Microsoft 365  و  موارد جدید در Excel برای Microsoft 365 تماشا کنید.

ادغام Get & Transform Data (که اکنون Power Query نامیده می شود) در اکسل در طول سال ها تغییرات زیادی را پشت سر گذاشته است. 

اکسل 2010 و 2013 برای ویندوز

در اکسل 2010 برای ویندوز، ابتدا Power Query را معرفی کردیم و به عنوان یک افزونه رایگان در دسترس بود که پس از فعال شدن، عملکرد Power Query از زبانه Power Query روی نوار در دسترس بود.

مایکروسافت 365

ما Power Query را به‌روزرسانی کردیم تا تجربه اولیه در Excel برای وارد کردن و تمیز کردن داده‌ها باشد. می توانید از گروه Get & Transform Data در تب Data در نوار Excel به ابزارها و جادوگران وارد کردن داده Power Query دسترسی داشته باشید .

این تجربه شامل بهبود عملکرد واردات داده، دستورات بازآرایی در برگه Data ، یک صفحه جانبی جدید Queries & Connection ، و ادامه توانایی شکل دادن به داده‌ها به روش‌های قدرتمند با مرتب‌سازی، تغییر انواع داده، تقسیم ستون‌ها، تجمیع داده‌ها و غیره بود. .

این تجربه جدید همچنین جایگزین جادوگران قدیمی‌تر و قدیمی واردات داده تحت فرمان  Data در گروه دریافت داده‌های خارجی شد. با این حال، هنوز هم می توان از  کادر گفتگوی گزینه های اکسل (انتخاب  فایل  >  گزینه ها  >  داده  >  نمایش جادوگران واردات داده های قدیمی) به آنها دسترسی داشت.

اکسل 2016 و 2019 برای ویندوز

We added the same Get & Transform Data experience based on the Power Query technology as that of Microsoft 365.

 

مایکروسافت 365

ما Power Query را به‌روزرسانی کردیم تا تجربه اولیه در Excel برای وارد کردن و تمیز کردن داده‌ها باشد. می توانید از گروه Get & Transform Data در تب Data در نوار Excel به ابزارها و جادوگران وارد کردن داده Power Query دسترسی داشته باشید .

این تجربه شامل بهبود عملکرد واردات داده، دستورات بازآرایی در برگه Data ، یک صفحه جانبی جدید Queries & Connection ، و ادامه توانایی شکل دادن به داده‌ها به روش‌های قدرتمند با مرتب‌سازی، تغییر انواع داده، تقسیم ستون‌ها، تجمیع داده‌ها و غیره بود. .

این تجربه جدید همچنین جایگزین جادوگران قدیمی‌تر و قدیمی واردات داده تحت فرمان  Data در گروه دریافت داده‌های خارجی شد. با این حال، هنوز هم می توان از  کادر گفتگوی گزینه های اکسل (انتخاب  فایل  >  گزینه ها  >  داده  >  نمایش جادوگران واردات داده های قدیمی) به آنها دسترسی داشت.

اکسل 2016 و 2019 برای ویندوز

ما همان تجربه Get & Transform Data را بر اساس فناوری Power Query مانند Microsoft 365 اضافه کردیم.

 

اکسل برای مایکروسافت 365 برای Mac

در سال 2019 سفر برای پشتیبانی از Power Query در اکسل برای مک را آغاز کردیم. از آن زمان، ما توانایی به‌روزرسانی پرس و جوهای Power Query را از فایل‌های TXT، CSV، XLSX، JSON و XML اضافه کردیم. ما همچنین قابلیت به‌روزرسانی داده‌ها را از سرور SQL و از جداول و محدوده‌ها در کتاب کار فعلی اضافه کرده‌ایم.

در اکتبر 2019، ما توانایی به‌روزرسانی پرس‌وجوهای Power Query موجود و استفاده از VBA را برای ایجاد و ویرایش جستارهای جدید اضافه کردیم.

در ژانویه 2021، ما پشتیبانی را برای به‌روزرسانی کوئری‌های Power Query از منابع OData و SharePoint اضافه کردیم.

توجه داشته باشید    هیچ پشتیبانی از Power Query در Excel 2016 و Excel 2019 برای Mac وجود ندارد.

منسوخ شدن کاتالوگ داده

با کاتالوگ داده، می توانید پرس و جوهای به اشتراک گذاشته شده خود را مشاهده کرده و سپس آنها را برای بارگیری، ویرایش یا استفاده در کتاب کار فعلی انتخاب کنید. این ویژگی به تدریج منسوخ شد:

• در 1 آگوست 2018، ورود مشتریان جدید به کاتالوگ داده را متوقف کردیم.

• در 3 دسامبر 2018، کاربران نمی‌توانستند جستارهای جدید یا به‌روزشده را در کاتالوگ داده به اشتراک بگذارند.

• در 4 مارس 2019، کاتالوگ داده از کار افتاد. پس از این تاریخ، توصیه می‌کنیم درخواست‌های به اشتراک‌گذاشته‌شده خود را دانلود کنید تا بتوانید با استفاده از گزینه Open از پنجره وظیفه My Data Catalog Queries ، به استفاده از آنها در خارج از کاتالوگ داده ادامه دهید.

منسوخ شدن افزونه Power Query

در اوایل تابستان 2019، ما به طور رسمی افزونه Power Query را که برای اکسل 2010 و 2013 برای ویندوز مورد نیاز بود، منسوخ کردیم. به عنوان حسن نیت، شما همچنان می توانید از افزونه استفاده کنید، اما ممکن است در تاریخ بعدی تغییر کند.

رابط داده فیس بوک بازنشسته شد

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