آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

با قسمت سوم سریال آموزشی Power BI در خدمت شما همراهان عزیز هستیم. در این قسمت به بررسی پاکسازی داده در Power Query خواهیم پرداخت.

اصولا همیشه داده ما آماده گزارش گیری نیست و لازم است تغییراتی در جهت پاکسازی داده صورت پذیرد. ETL مخفف عبارت Extract- Transform- Load به کلیه اعمال استخراج، تبدیل و پاکسازی داده اشاره دارد که این اعمال در Power Query با قدرت بالا قابل انجام است. اعمالی که برای انجام آن ها به ابزارهای سنتی هوش تجاری مانند SSIS یا ODI نیاز بود در حال حاضر با Power BI قابل انجام است.

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

برای مطالعه بیشتر در مورد ETL مطالب زیر را از دست ندهید.

ETL چیست؟

انبار داده چیست؟

بارگذاری داده

برای پاکسازی داده در Power Query ابتدا نیاز به یک مجموعه داده داریم. در قسمت قبل نحوه اتصال به منبع داده ای اکسل را بررسی کردیم. اما این بار نحوه اتصال به منابع داده وب را بررسی کنیم. فرض کنید می خواهیم از این لینک داده های مربوط به فیلم ها را استخراج کنیم.

برای این منظور ابتدا از Get Data، وب را انتخاب می نماییم.

Screenshot 866 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

در باکس باز شده لینک مورد نظر را وارد می نماییم.Screenshot 867 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

در نهایت Connect را انتخاب می نماییم.Screenshot 868 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

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

در انتها دو گزینه Load و Edit پیش روی شماست. اگر گزینه Load را انتخاب نمایید بارگذاری آغاز می شود و اگر گزینه Edit را انتخاب نمایید به دنیای Power Query وارد خواهید شد. در این جا ما گزینه Edit را انتخاب کردیم.

Screenshot 869 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

اگر گزینه Load را انتخاب کردید نگران نباشید. شما می توایند مطابق تصویر زیر از طریق Edit Query نیز قدم به دنیای Power Query بگذارید.

Screenshot 874 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

محیط Power Query

خب! با هم با دنیای پاور کوئری آشنا شویم. برای شروع جدول زیر را که از لینک یاد شده استخراج شده است، را در نظر بگیرید. در این جدول چه مشکلی به نظر می رسد؟

Screenshot 871 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

آفرین! درست حدس زدید. اولین ردیف این جدول نام ستون های ماست که به عنوان داده در نظر گرفته شده است. برای رفع این مشکل از تب Transform گزینه Use First Row as Header را انتخاب کنید. مطابق تصویر اولین ردیف به عنوان سر ستون جدول شما قرار می گیرد.

Screenshot 873 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

نکته بسیار مهم با مشاهده فلش مشخص شده در تصویر، نمایان می گردد. با ایجاد هر تغییر بر روی جدول، یک گام به قسمت Applied Steps اضافه می گردد. با کلیک بر روی ضربدر کنار هر گام می توانید، جدول را به حالت قبل برگردانید که این ویژگی بسیار کاربردی و پر اهمیت است. همچنین این گام ها بر روی جدول ذخیره گردیده و با ورود داده جدید به جدول این اعمال تبدیل، تغییر و پاکسازی روی داده جدید (فقط در همین جدول)، نیز اعمال می شود.

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

Screenshot 875 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )پس به راحتی با انتخاب ستون و راست کلیک بر روی آن و انتخاب گزینه Remove، این ستون حذف می گردد.

Screenshot 876 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

همچنین می توانید ستون را انتخاب نموده و از تب Home، گزینه Remove Columns را انتخاب نمایید.

Screenshot 877 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

اگر قصد نگه داشتن تعداد محدودی ستون دارید، ستون های مورد نظر را انتخاب نموده و گزینه Remove Other Columns را انتخاب کنید. در نتیجه ستون های انتخابی باقی می مانند و بقیه ستون ها حذف می گردند.

فرض کنید قصد داریم دو ستون Rank و Title را نگه داشته و بقیه ستون ها را حذف کنیم. برای این منظور این دو ستون را با کلید Ctrl انتخاب نمایید. گزینه ذکر شده را با کلیک راست یا از تب Home انتخاب نمایید.

Screenshot 878 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

در نهایت ستون های مورد نظر شما باقی مانده و بقیه حذف می شوند.

Screenshot 879 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

همچنین با کیک راست بر روی هر ستون می توان نام ستون مورد نظر خود را تغییر دهید.

Screenshot 887 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

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

Screenshot 880 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

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

Screenshot 881 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

اگر به تب View رفته و گزینه Column Distribution را فعال کنید اطلاعات بیشتری در مورد ستون ها کسب می کنید. به تصویر زیر توجه نمایید. با فعال نمودن این گزینه اطلاعات بیشتری زیر هر ستون پدیدار می شود.

توجه کنید که این ویژگی که تحت عنوان Column Profiling شناخته می شود تنها اطلاعات ۱۰۰۰ ردیف اول داده ها است. در پایین تصویر عبارت Column Profiling based on 1000 top rows را مشاهده می نمایید.

Screenshot 883 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

به طور مثال ستون Year دارای ۲۰ مقدار یکتاست که ۱۱ مقدار آن بدون تکرار است. فرض کنید اعداد ۱،۱،۲،۳،۴،۵،۵ مجموعه داده ماست. این داده ها دارای ۵ مقدار Distinct است (۱،۲،۳،۴،۵) و دارای ۳ مقدار Unique است (۲،۳،۴) که تکرار نشده اند.

اگر گزینه Column Profile را فعال کنید توزیع این ستون را در قسمت Column Distribution مشاهده می نمایید.  همان طور که مشاهده می نمایید سال ۲۰۱۹ بیشترین تکرار را در داده ها دارد.

همچنین در قسمت Column Statistics اطلاعات آماری ستون از جمله تعداد ردیف ها، تعداد خطا، تعداد Distinct، تعداد یکتا، تعداد صفر، ماکزیمم، مینیمم، میانگین، انحراف از معیار و تعداد عدد های زوج و فرد مشاهده می شود.

Screenshot 884 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

همچنین با فعال کردن گزینه Column Quality اطلاعات کیفیت داده های ستون نمایش داده می شود. همان طور که مشاهده می کنید ستون مد نظر ما دارای ۱۰۰ درصد مقدار Valid و عاری از Error  و NULL است.

Screenshot 886 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

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

Data Profiling

یکی از مهمترین نکات در Power Query انتخاب نوع داده مناسب یا همان Data Type مناسب برای ستون های مختلف است. با کلیک راست بر روی هر یک از ستون ها می توانید به صورتی که در تصویر مشاهده می کنید نوع داده مناسب را برای آن انتخاب کنید. اگر چه Power BI خود نوع داده ها را تشخیص می دهد، اما ممکن است برخی اوقات Data Type نامناسبی را برای یک ستون برگزیند. کافی است چک کنید که Data Type های انتخاب شده مناسب هستند.

معمولا برای اعداد صحیح Whole Number، برای اعداد اعشاری Decimal Number، برای تاریخ Date ، برای ستون های رشته ای و متنی text را در نظر می گیریم.

Screenshot 888 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

حال که تغییرات مد نظر خود را اعمال نمودید، گزینه Close & Apply را انتخاب نموده تا داده با تغییرات مد نظر شما در مدل بارگذاری شود.

حال که کمی با Power Query و اهداف آن آشنا شدید و کار با آن را فرا گرفته اید به بررسی مثالی دیگر بپردازیم. برای این مثال از این لینک جدول مورد نظر خود را در مدل بارگذاری کنید.

Edit Query را انتخاب نمایید.

Screenshot 874 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

New Source را انتخاب کنید.

Screenshot 891 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

مانند مثال قبل منبع داده ای Web را انتخاب نموده و لینک جدید را به آن معرفی کنید. جدول زیر جدولی است که از لینک مورد نظر استخراج شده است.

مطابق تصویر ستون اضافی را حذف نمایید. Screenshot 893 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

در نهایت جدول زیر خروجی شما خواهد بود.

Screenshot 894 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

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

برای این منظور Split Columns را انتخاب نموده و By Delimiter را انتخاب می نماییم. By Delimiter زمانی به کار گرفته می شود که شما جدا کننده خود را بشناسید. به عبارت بهتر وقتی که داده های شما از الگوی خاصی تبعیت کنند. همان طور که در تصویر می بینید این نکته در مورد داده ما صدق می کند و پرانتز جدا کننده دو مقدار ما می باشد.

Screenshot 895 1 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

خروجی این مرحله به صورت زیر خواهد بود.

Screenshot 896 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

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

Screenshot 897 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

در باکس باز شده علامت پرانتز را با هیچ! جایگزین می کنیم.

Screenshot 899 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

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

Screenshot 900 - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

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




6b77990865d588b48d7f55a013cdd7ec?s=300&d=mm&r=g - آموزش Power BI صفر تا سکو : قسمت سوم ( پاکسازی داده در Power Query )

حسین وثوقی

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

4
دیدگاه بگذارید

avatar
2 Comment threads
2 Thread replies
1 Followers
 
Most reacted comment
Hottest comment thread
3 Comment authors
سهیل موسویحسین وثوقیsanaz Recent comment authors
  عضویت  
جدید ها قدیمی ها بیشترین رای
به دوستتان خبر دهید.
sanaz
مهمان
sanaz

ممنون بابت آموزشتون.
من دیتامو وادرد نرم افزار و query کردم اما اون نوار سبز رنگی که در موردش توضیح دادین نیست و نمیاد. مشکل از کجا میتونه باشه؟

سهیل موسوی
مهمان
سهیل موسوی

ممنون بابت آموزش های گام به گامتون
توی مثال ها چالش های بیشتری داشته باشید