آموزش 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، وب را انتخاب می نماییم.

در باکس باز شده لینک مورد نظر را وارد می نماییم.

در نهایت Connect را انتخاب می نماییم.

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

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

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

محیط Power Query

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Data Profiling

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

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

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

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

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

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

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

مطابق تصویر ستون اضافی را حذف نمایید.

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

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

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

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

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

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

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

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

حسین وثوقی

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