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

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

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

نکته: پاور کوئری در ورژن 2016 در مجموعه Get & transform در ریبون data قابل دسترس است. در ورژن های 2010 و 2013 اکسل این افزونه پاور کوئری قابل نصب است که در فهرست مطالب به صورت کامل ای موضوع را خواهید آموخت.

20 قسمت فیلم رایگان آموزش پاور کوئری در اکسل

ابزار پاور کوئری به عنوان یکی از ابزارهای مدرن در اکسل و یکی از پایه های نرم افزار پاور بی آی (power bi) محسوب می شود. خیلی از مسائلی که با سایر ابزار اکسل گاهی اوقات به سختی حل می شوند با پاور کوئری به راحتی قابل حل است. از مزایای اولیه پاور کوئری توانایی اتصال به پایگاه داده های مختلف، پاکسازی دیتا و سپس ادغام و چسباندن داده ها به هم است. در زیر می توانید فیلم های مقدماتی آموزش پاور کوئری را مشاهده کنید و از کار با این ابزار لذت ببرید. همچنین اگر تمایل دارید بیشتر با چیستی و کاربردهای پاور کوئری آشنا شوید ادامه مطلب را مطالعه کنید.

قسمت اول: فیلم آموزشی نصب پاور کوئری در اکسل

(برای ورژن های 2013 و 2010 اکسل، اگر از ورژن 2016 یا 2019 استفاده می کنید نیاز به دیدن این قسمت نیست.)
در این آموزش نحوه نصب و فعال سازی پاور کوئری در اکسل 2013 به قبل را خواهید دید. اگر از اکسل 2016 به بعد استفاده می کنید نیاز به مشاهده این آموزش نیست و به ارحتی می توانید از تب Data به ابزار پاور کوئری دسترسی داشته باشید.

دانلود قسمت اول

قسمت دوم: آموزشی ساخت کوئری از جدول های اکسل (5 دقیقه)

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

دانلود قسمت دوم

قسمت سوم: فیلم آموزشی فراخوانی داده ها از سایر فایل های اکسل (3 دقیقه)

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

دانلود قسمت سوم

قسمت چهارم: فیلم آموزشی ذخیره سازی کوئری ها در پاور کوئری (9 دقیقه)

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

دانلود قسمت چهارم

قسمت پنجم دوره پاور کوئری: تجمیع و ادغام صدها فایل با چند کلیک (6 دقیقه)

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

دانلود قسمت پنجم تجمیع و ادغام صدها فایل

قسمت ششم: فیلم آموزشی لود کردن دیتا و آشنایی با پنجره پاور کوئری (4 دقیقه)

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

دانلود قسمت ششم

قسمت هفتم: فیلم آموزشی جدا کردن ستون با استفاده از split column و تعیین نوع دیتا در پاور کوئری (4 دقیقه)

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

دانلود قسمت هفتم

قسمت هشتم: فیلم آموزشی پاکسازی دیتا و تغییر نام ستون ها در پاور کوئری (5 دقیقه)

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

دانلود قسمت هشتم

قسمت نهم:فیلم آموزشی خارج کردن دیتا از حالت پیوت (unpivot) (7 دقیقه)

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

دانلود قسمت نهم

قسمت دهم: فیلم آموزشی مفهوم ادغام و مرج کردن دیتا در پاور کوئری

یکی از کارهای بسیار مهم که در اکثر کوئری های پیچیده اتفاق می افتد، استفاده از قابلیت merge و append در پاور کوئری است. این ابزارها به شما کمک می کنند تا چندین جدول و کوئری را با هم پکیچارچه کنید و به جای داشتن ده ها جدول از یک جدول استفاده کنید. همچنین می توانید داده های متناظر با یک مقدار را از جدول های مختلف در مقابل آن قرار دهید.

دانلود قسمت دهم (بخش اول)

در آموزش بعدی با هم یک مثال از نحوه استفاده از merge را خواهیم دید.

نکته: در این ویدئو به دلیل قدیمی بودن نسخه اکسل فقط نحوه ایجاد join را به همراه حل یک مسئله بیان شده است. در این ورژن از پاور کوئری انواع اتصال به پنجره Merge اضافه نشده بود.

دانلود قسمت دهم (بخش دوم)

شما در پاوری کوئری می توانید از انواع Join استفاده کنید که در مباحث تخصصی تر مورد بحث قرار می گیرند.

قسمت یازدهم: فیلم آموزشی ادغام یا مرج کردن (merge) کوئری ها ( 8 دقیقه)

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

دانلود قسمت یازدهم

قسمت داوزدهم فیلم آموزشی فیلتر کردن و تغییر مکان ستون ها ( 6 دقیقه)

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

دانلود قسمت دوازهم

قسمت سیزدهم: فیلم آموزشی چسباندن دادها و کوئری ها پس از فراخوانی دادها  از منابع مختلف (5 دقیقه)

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

دانلود قسمت سیزدهم

قسمت چهاردهم: فیلک آموزشی انتقال داده از پاور کوئری به پیوت تیبل و انجام تحلیل (7 دقیقه)

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

دانلود قسمت چهاردهم

قسمت پانزدهم: فیلم آموزشی چه کسانی و در کجا از پاور کوئری استفاده می کنند؟( بخش اول) ( 8 دقیقه)

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

دانلود قسمت پانزدهم

قسمت شانزدهم: فیلم آموزشی چه کسانی و در کجا از پاور کوئری استفاده می کنند؟ (بخش دوم) (7 دقیقه)

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

دانلود قسمت شانزدهم

قسمت هفدهم: فیلم آموزشی فراخوانی فایل های txt و csv با پاور کوئری ( 6 دقیقه)

در این قسمت می خواهیم به معرفی دو مدل از فرمت های رایج مورد استفاده کابران اکسل بپردازیم. این دو فرمت های txt و csv در سیستم های مختلف مانند وبسایت ها، سیستم های حسابداری و سیستم های مدیریت ارتباط با مشتری مورد استفاده قرار می گیرد.با توجه به اینکه این فرمت ها حجم کمتری را اشغال می کنند و با Notepad به راحتی می توان به آن ها دسترسی داشت مورد توجه قرار گرفته اند.در این بخش به بررسی بیشتر این فرمت ها می پردازیم.

دانلود قسمت هفدهم

قسمت هجدهم: فیلم آموزشی اتصال و فراخوانی فایل های اکسس (6 دقیقه)

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

دانلود قسمت هجدهم

قسمت نوزدهم : فیلم آموزشی اتصال و فراخوانی داده ها از دیتابیس mysql با پاور کوئری ( 5 دقیقه)

آخرین سناریو برای اتصال به فایل ها و دیتابیس ها اتصال به پایگاه داده Mysql می باشد که بسیار رایج در میان وبسایت ها و پلتفرم های تحت وب به خصوص پلترفرم هایی با زبان php می باشد. در فیلم آموزشی زیر با روند این سناریو آشنا می شویم.

دانلود قسمت نوزدهم

قسمت بیستم: حل یک مسئله کاربردی ( استخراج نمرات زیر 10) (10 دقیقه)

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

دانلود قسمت بیستم

قسمت بیست و یکم: حل یک مسئله کاربردی با استفاده از پاور کوئری (

محاسبه مجموع ورودی انبار از چند فایل) (20 دقیقه)

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

دانلود قسمت بیست و یکم

قسمت بیست و دوم آموزش پاور کوئری در اکسل

فرض کنید فایلی از پرداختی های سال 98 دارید که در هر شیت اطلاعات یک ماه ذخیره شده است. از شما مبلغ کل پرداختی به هر فرد یا یک ماه خواسته می شود. یا در یک فایل اکسل پرداختی ها به هر فرد به همراه تاریخ واریز در یک شیت ذخیره شده است و شما بیش از 100 شیت دارید!!! از شما مبلغ کل پرداختی هر ماه خواسته می شود. برای ادغام شیت های یک فایل اکسل فیلم آموزشی زیر رو مشاهده کنید.

• دانلود قسمت بیست و دوم

در آینده فیلم های آموزشی بیشتری برای یادگیری شما در سایت قرار می گیرد.

اما پاور کوئری (power query) چیست؟

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

با استفاده از این ابزار شما بدون دانش برنامه نویسی می توانید به راحتی مانند یک برنامه نویس و مهندس کامپیوتر با داده ها کار کنید. به راحتی می توانید کوئری های خودتان را بسازید و آن ها را با سایرین به اشتراک بگذارید. سایر کابران به راحتی می توانند به این کوئری ها دسترسی داشته باشند و از آن ها استفاده کنند.

با پاور کوئری چه کاری هایی می توانیم انجام دهیم؟

• به منابع داده ای مختلف و متنوعی متصل شوید. (از یک فایل متنی ساده، فایل های اکسل تا sql server و سایر دیتابیس ها)
• ادغام داده ها از منابع مختلف و آماده سازی آن ها جهت انجام تجزیه و تحلیل های متنوع و استفاده در سایر ابزار مانند پاور پیوت (Power Pivot)، پاور ویو (Power View) و پیوت تیبل یا جدول های محوری (Pivot Table).
• ایجاد نمایش های سفارشی بر روی داده ها
• فراخوانی داده ها در قابل JSON  جهت مصورسازی داده های بزرگ
• پاک سازی و مرتب سازی داده ها
• آماده سازی داده ها از چندین فایل log (فایل های تاریخی یا داده های ثبت شده توسط سیستم ها)
• دریافت اطلاعات با استفاده از api ها از سیستم های مختلف مانند فیس بوک و …
• انتقال داده های موجود در چند پوشه و سایر منابع داده ای به مدل های داده ای و ابزار پاور پیوت
• اشتراک گذاری کوئری ها برای استفاده توسط سایرین
• و هزاران قابلیت دیگر

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

قسمت اول:

قسمت دوم:

پاور کوئری یک ابزار ETL است، ابزار ETL چیست؟

قبل از شروع کار با پاور کوئری و مطالعه آموزش های پاور کوئری در اکسل باید با این ابزار بیشتر آشنا شوید و کاربردها و اهداف آن را بدانید. بعد از آشنایی کار شما در فهم و آموزش این ابزار بهتر خواهد شد و با سرعت بیشتری پیش خواهید رفت. قبل از هر چیز ETL مخفف Extract Transform and Load است که به معنای استخراج (extract)، پالایش (transform) و بارگذاری (load) داده ها است. ETL فرایندی است که در آن اطلاعات از یک یا چند منبع مختلف جمع آوری، پالایش و در نهایت بارگذاری می‌شود.
با استفاده از ETL عملیات استخراج داده ها (extract) از یک یا چند منبع داده ای و تبدیل آن به داده مورد نیاز ما (transform) از طریق پاکسازی و تبدیل داده ها و ایجاد فیلدهای محاسباتی و سپس در اختیار بودن داده ها (Load) به ساده ترین شکل ممکن انجام می شود. در بسیاری از موارد به جای اینکه نیاز به نوشتن کوئری های پیچیده داشته باشید، می توانید با گزینه هایی که در اختیار دارید به ادغام و تبدیل داده های خود بپردازید.

پس از مدل سازی، تحلیل و طراحی داده، نوبت به بارگذاری داده‌ها در آن می شود.

استخراج (extract) داده ها در پاور کوئری

استخراج داده‌ها تابع قوانین خاصی است که در زیر به آنها اشاره می کنیم:

1- شناسایی داده هایی که در مدل می خواهیم بارگذاری کنیم

2- قرار دادن داده ها در یک محیط واسط برای انجام عملیات پاکسازی و تبدیل (بانک اطلاعاتی بین داده های اولیه و مدل داده ای جدید)

چرا باید داده را در یک محیط واسط قرار دهیم و مستقیم در مدل خود نمی آوریم؟ بسیار ساده است، چون در اکثر موارد داده های منابع ابتدایی نیاز به تغییر دارند و شما معمولا نمی توانید در داده های اولیه و منابع اصلی تغییر ایجاد کنید. به همین دلیل تمام داده های اولیه را در یک محیط واسط قرار می دهیم و تغییر مورد نظر خود را انجام میدیم. در اکسل این کار در ابزار پاور کوئری یا افزونه پاور کوئری (power query) انجام می شود.

پالایش (transform) کردن داده ها در پاور کوئری

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

بررسی کیفیت داده‌ها (Verify data quality) در پاور کوئری

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

 آیا داده‌های ما کامل هستند (نیازهای ما را پوشش می دهند.)؟

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

داده‌ها صحیح هستند یا دارای اشتباه هستند؟ دلیل اشتباه بودن داده ها چیست؟

فرض کنید در سیستم اسامی تکراری وجود دارد یا از حرف «ي» عربی استفاده شده باشد و در منبع دیگر از «ی» فارسی استفاده شده باشد. شما باید کارهای یکسان سازی داده ها را نیز انجام دهید. به طور مثال اگر در یک جدول نام دپارتمان «منابع انساني» با ي عربی نوشته شده است و در جدول دیگری با ی فارسی، نیاز است این اشتباهات را تصحیح کنیم. گاهی اوقات اشتباهات انسانی وجود دارد، مثلا در یک جدول کلمه «منا بع» با فاصله نوشته شده است. باید تمامی این موارد را بررسی کنیم تا درآینده با مشکل رو به رو نشویم. (البته بسیاری از مشکلات در مرحله اجرا نمایان می شوند و باید به مراحل قبل بازگشت و اصلاح شوند.)

آیا مقدارها و داده های گم شده وجود دارد؟

فرض کنید برای یک ماه داده های فروش شما در بین داده ها موجود نیست. در این صورت باید داده ها را تکمیل کنید. یا در پایگاه داده شما جنیست کارمندان مشخص نشده است و …

پاک‌سازی داده‌ها (Clean data) در پاور کوئری

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

شکل دادن داده‌ها (Construct data) در پاور کوئری

گاهی اوقات نیاز است تغییراتی در دیتای اولیه صورت بگیرد. به صورت مثال اعداد به صورت 17% ذخیره شده اند ولی این دیتا برای ما در محاسبات قابل استفاده نیست و باید به صورت 0.17 تبدیل شود. این فرایند در پاور کوئری به سادگی انجام می شود. گاهی اوقات نیاز است ستون جدیدی به داده ها خود اضافه  کنیم. مثلا تعداد کالا و قیمت واحد کالا را داریم. برای محاسبه درآمد باید فیلد جدیدی که فرمول (تعداد * قیمت واحد) را محاسبه می کند را ذخیره کنیم. اینها مثال های ساده ای از شکل داده داده بود.

ادغام داده‌ها (Integrate data) در پاور کوئری با استفاده از گزینه merge

شما به راحتی می توانید با استفاده از گزینه merge و استفاده از ستون های مشترک در جدول های مختلف دیتای خود را با هم ادغام کنید و سپس در تحلیل ها استفاده کنید. برای مثال جدول کارمندان که شامل کد پرسنلی، نام و نام خانوادگی و جدول دیگری که شامل کد پرسنلی و آدرس هست را دارید. حالا میخواهید آدرس ها در جدول کارمندان قرار بگیرد. با فیلد مشترک «کد پرسنلی» می توانید این دو جدول را با هم ادغام کنید.

قالب بندی داده‌ها (Format data) در پاور کوئری

تغییر فرمت یا قالب بندی داده ها در پاور کوئری مشابه کاری است که با گزینه cell formatting در اکسل انجام می دهیم. گاهی اوقات نیاز است که جنس داده های خود را مشخص کنیم. گاهی اوقات اعداد در منابع داده ای ما به صورت متن ذخیره شده اند که قابلیت انجام محاسبات در آنها نیست و نیاز است که فرمت این فیلد را تغییر دهیم. شما به راحتی در پاور کوئری می توانید قالب داده های خود را تغییر دهید.

بارگذاری (load) داده ها در پاور کوئری

 آخرین کاری که در پاور کوئری یا سایر ابزار ETL انجام می دهیم بارگذاری داده‌های استخراج و پالایش شده از منابع مختلف داده ای است. معمولا پس از بارگذاری تغییراتی در داده ها ایجاد نمی شود و تغییرات در مرحله پالایش انجام می شود. در پاور کوئری به دو صورت دیتا بارگذاری می شود:

1- بارگذاری دیتای پاور کوئری در شیت اکسل

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

2- بارگذاری دیتای پاور کوئری به صورت اتصال

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

پاور کوئری به چه منابع داده ای می تواند متصل شود؟

• صفحات وب
• فایل های اکسل و CSV
• فایل های XML
• فایل های متنی (txt)
• پوشه ها
• پایگاه داده Access
• پایگاه داده SQL Server
• پایگاه داده Microsoft Azure SQL
• پایگاه داده Oracle
• پایگاه داده IBM DB2
• پایگاه داده MySQL
• پایگاه داده PostgreSQL
• پایگاه داده Sybase
• پایگاه داده Teradata
• SharePoint List
• OData feed
• Microsoft Azure Marketplace
• Hadoop File (HDFS)
• Microsoft Azure HDInsight
• Microsoft Azure Table Storage
• Active Directory
• Microsoft Exchange
• Facebook

برگرفته شده از سایت ماکروسافت

45 ساعت در 346 قسمت آموزش در سطح متوسط و حرفه ای.