پاکسازی داده با کمک پاور کوئری

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

مشکلات زیر در داده وجود دارد که قصد داریم آنها را مرتب کنیم

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

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

ایمپورت کردن داده

در گام اول باید داده موجود در فایل اکسل را به پاور کوئری وارد کنیم. جهت این کار ابتدا وارد تب Data شوید و سپس مراحل زیر را طی کنید.

Data --> New Query --> From File --> From WorkBook

سپس فایل اکسل را انتخاب کنید.

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

ویرایشگر پاور کوئری

بعد از آنکه گزینه Edit را انتخاب کردید، ویرایشگر پاور کوئری مشابه تصویر زیر ظاهر می‌شود. هر تغییری که بر روی داده‌ها اعمال کنید، به صورت گام به گام بر روی ستون سمت راست که با فلش مشخص شده است، ثبت می‌شود.

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

جداسازی تاریخ

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

بر روی ستون کلیک راست کنید. از منو ظاهر شده گزینه Split Column را انتخاب کنید و سپس By Delimiter را انتخاب کنید.

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

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

جدا کردن نام فروشنده و شماره پرسنلی

برای جدا کردن نام فروشنده و شماره پرسنلی مشابه تاریخ عمل می‌کنیم، اما این بار جداکننده را به جای علامت کولن به علامت پرانتز تغییر میدهیم.

ستون جدید، هنوز پرانتز بسته را دارد. بنابراین با کمک گزینه Replace Values پرانتز بسته را حذف می‌کنیم.

حذف فضای خالی قبل از نام فروشنده

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

حذف سطرهای غیر ضروری

ستون مورد نظر را انتخاب کنید و مطابق تصویر زیر عمل نمایید.

تغییر نام ستون ها

بر روی نام ستون دبل کلیک کنید و نام جدید را تایپ کنید.

آنپیوت کردن نام شهر

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

تغییر فرمت تاریخ

فرمت تاریخ Date است اما از آنجاییکه ما تاریخ را به شمسی وارد کردیم برای اکسل خوانا نیست. برای اینکه به مشکل برنخوریم، فرمت تاریخ را به Text تغییر می‌دهیم. در مقالات آتی بیشتر به مساله تاریخ شمسی در پیوت خواهیم پرداخت.