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

با قسمت چهارم آموزش سریالی Power BI در خدمت شما هستیم. در قسمت سوم تا حدی با اهداف و ویژگی های Power Query‌ آشنا شدیم. حال به ادامه آموزش آماده سازی داده در Power Query می پردازیم.

به طور خلاصه باید بدانید که Power Query با بهره گیری از زبان m برای مقاصد دستکاری، پاکسازی و تبدیل داده بهترین گزینه است. پس سعی کنید تمام این اعمال را در Power Query انجام دهید. (بسیاری از اعمالی که در Power Query قابل انجام است با استفاده از زبان DAX نیز دست یافتنی می باشد اما سعی کنید کارهای پاکسازی و تبدیل داده را در Power Query انجام دهید). پس با هم به بررسی مثال هایی از آموزش آماده سازی داده در Power Query بپردازیم.

قسمت های قبلی سریال آموزش Power BI صفر تا سکو

قسمت اول

قسمت دوم

قسمت سوم

حذف Space های اضافی

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

حال قصد تهیه یک گزارش داریم. می خواهیم میزان فروش هر شخص را بر روی یک نمودار مشاهده کنیم. برای این منظور نیاز داریم که نام و نام خانوادگی را در یک ستون و نه به صورت جدا داشته باشیم. لذا دو ستون را انتخاب نموده، از تب Add Column گزینه Merge را انتخاب می کنیم. دقت داشته باشید که این گزینه در تب Transform نیز وجود دارد اما در صورت استفاده از این گزینه ستون جدیدی به جدول اضافه نمی شود و این تبدیل در یکی از ستون های موجود صورت می پذیرد. نمی توان گفت کدام بهتر یا بدتر است؟ برتری یکی از این روش ها بستگی به نیاز و نظر شما به عنوان توسعه دهنده دارد.

حال یک باکس پیش روی شما باز خواهد شد. از شما می خواهد که یک جدا کننده برای این دو ستون در نظر بگیرید. ما در این مورد Space را در نظر گرفتیم و نام FullName را برای این ستون برگزیدیم.

خروجی به صورت زیر در می آید.

همان طور که می بینید ستون مورد نظر به جدول اضافه می شود. حال گزینه Close And Apply را انتخاب نمایید. گزارشی تهیه کنیم که میزان فروش افراد مختلف را نشان دهد. گزارش زیر حاصل استفاده از ستون FullName در Axis و Sales در Value است.

همان طور که مشاهده می کنید برای پوریا بغدادی 3 میله در نظر گرفته شده است، این در حالیست که باید فروش به هر شخص در یک میله تجمیع شود. دلیل این اتفاق چیست؟

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

برای این منظور بر روی ستون مورد نظر راست کلیک کنید و از Transform گزینه Trim را انتخاب نمایید. این کار Space های اضافی اول و آخر هر ستون را حذف می کند. ابتدا این کار را برای FirstName سپس برای LastName انجام داده و در نهایت عمل Merge برای این دو ستون را انجام می دهیم.

خروجی به صورت زیر در می آید.

حال Close and Apply را انتخاب نموده و گزارش به روز شده را مشاهده نمایید.

یکسان سازی داده

حال قصد بررسی مثال دیگری داریم. به همان جدول ساده قبل ستون جنسیت را اضافه نمودیم. جدول را در تصویر مشاهده می کنید.

یک گزارش بر اساس جنسیت ایجاد کنید. برای این منظور جنسیت را در Axis و فروش را در Sales قرار دهید.

همان طور که مشاهده می نمایید برای هر مقدار ثبت شده در جنسیت یک میله ایجاد شده و عملا گزارش غلط ایجاد شده است. برای این منظور باید یکسان سازی مقادیر ستون جنسیت را در دستور کار خود قرار دهیم. کافیست از تب Add Column گزینه Conditional Column را انتخاب نماییم.

در باکسی که پیش روی شما باز می شود هر مورد و معادل جایگزین آن را درج کنید.

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

حال Close and Apply را انتخاب نمایید. برای ایجاد گزارش به جای Gender از ستون NewGender استفاده می نماییم. گزارش نهایی به صورت زیر قابل مشاهده است.

گروه بندی داده ها

گاهی اوقات نیاز داریم که داده ها را گروه بندی کنیم. به طور مثال افراد را به دسته های نوجوان، جوان، میانسال و کهنسال دسته بندی کنیم. یا افراد را به سه دسته کم درآمد، متوسط و پر درآمد دسته بندی کنیم. برای این منظور با استفاده از Conditional Column در تب Add Column می توانیم این ستون را ایجاد کنیم.

می خواهیم افرادی را با سطح درآمد کمتر از 30000 دلار را به عنوان افراد کم درآمد، افرادی با درآمد بین 30000 تا 75000 دلار را به عنوان افراد با درآمد متوسط و افراد با درآمد بالای 75000 دلار را با عنوان افراد با درآمد بالا دسته بندی کنیم.

ستون ایجاد شده را در تصویر مشاهده می کنید.

استفاده از اولین ردیف به عنوان سر ستون ها

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

در این هنگام می توانید مطابق تصویر از تب Home و با استفاده از گزینه Use First Row as Header ردیف اول را به عنوان نام ستون قرار دهید.

جابه جایی ترتیب ستون ها

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

همان طور که در تصویر می بینید ستون انتخابی به سمت راست انتقال داده شده است.

سر و ته کردن جدول!

گاهی اوقات بنا به دلایلی می خواهید جدول را سر و ته! ببینید. در واقع با استفاده از گزینه Reverse در تب Transform می توانید این عمل را انجام دهید.

در تصویر زیر نتیجه این عمل را مشاهده می نمایید.

انتقال به ستون مورد نظر

گاهی اوقات به دلیل طویل بودن جدول پیدا کردن ستون مورد نظر کمی دشوار به نظر می رسد. در این هنگام با استفاده از گزینه Go to Column در تب View می توانید به ستون مورد نظر منتقل شوید. در تصویر زیر جدولی با 32 ستون را مشاهده می نمایید. می خواهیم به ستون Yearly Income منتقل شویم. مطابق تصویر عمل می کنیم و بعد از آن به این ستون منتقل می شویم.

ایجاد شماره ردیف

گاهی اوقات نیاز دارید تا برای ردیف جدول خود شماره ردیف ایجاد کنید. این کار از تب Add Column و گزینه index Column قابل انجام است. با انتخاب این گزینه از شما سوال می شود که شماره ردیف ها از 0 آغاز شود یا 1؟ ما برای این مثال 1 را انتخاب نمودیم. توجه داشته باشید که تنها زمانی این کار را انجام دهید که به آن نیاز دارید چرا که اضافه کردن index بی مورد تنها کاهش عملکرد مدل را به همراه خواهد داشت.

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

حذف ردیف های تکراری

گاهی اوقات ممکن است جداول ما حاوی ردیف های تکراری باشد و ما باید آن ها را حذف کنیم. برای این کار کافی است از تب Home گزینه Remove Rows را انتخاب نموده و از موارد پیش رو Remove Duplicate را انتخاب نماییم.

تصویر جدول بدون ردیف تکراری را مشاهده می نمایید.

حذف ردیف های بدون مقدار

گاهی اوقات ممکن است در داده های خود ردیف های بدون مقدار داشته باشیم و بخواهیم از شر آن ها خلاص شوم. برای این منظور کافی است از تب Home گزینه Remove Rows را انتخاب نموده و از موارد پیش رو Remove Blank Rows را انتخاب می نماییم.

بعد از انجام این عمل جدول به صورت زیر در می آید.

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