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

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

در صورتی که تازه به جمع ما پیوسته اید، قسمت های 1 تا 4 سریال را مطالعه نمایید.

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

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

استخراج ویژگی های تاریخ

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

برای این منظور کافی است ستون تاریخ تولد را انتخاب نموده و از تب Add Column و از Date گزینه Year را انتخاب نماییم.

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

استخراج ویژگی های تاریخ شمسی

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

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

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

ستون تاریخ سفارش را انتخاب نموده و از تب Add Column مورد Extract را انتخاب نموده و گزینه First character را انتخاب می نماییم.

در باکس باز شده عدد 2 را انتخاب می نماییم. در واقع 2 کاراکتر از ابتدا را استخراج می نماییم.

در نهایت ستون مورد نظر به انتهای جدول اضافه می شود که می توانید نام آن را تغییر دهید.

حال از تب Add Column گزینه Custom Column را انتخاب نموده و در باکس مورد نظر کد زیر را تایپ نمایید. در واقع عدد 13 را به ابتدای سال می چسبانیم.

ستون مورد نظر به انتهای جدول اضافه می شود.

حال می توانیم ستون OrderDate را حذف کنیم.

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

گروه بندی

گروه بندی یا Group by عملیست که جدولی را خلاصه می کند. به طور مثال شما جدولی دارید که تراکنش های 2 سال را در آن ذخیره شده است. حال می خواهید این جدول را خلاصه نموده و تعداد فروش در هر سال را محاسبه نمایید.

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

برای این منظور از تب Home، گزینه Group by را انتخاب نمایید.

در باکس باز شده در قسمت Group by مشخص می کنید که توقع دارید بر اساس کدام ستون عمل خلاصه سازی انجام شود. ما سال را برای این منظور انتخاب نمودیم، چرا که می خواهیم بر اساس سال خلاصه سازی را انجام دهیم. در قسمت new Column name نام ستون را از ما سوال می کند. در قسمت Operation از ما درخواست معرفی یک تابع را دارد.  ما می خواهیم تعداد در هر سال را جمع کنیم پس Sum را انتخاب می کنیم. در قسمت Column از ما سوال می کند این تابع (جمع) بر روی کدام ستون اعمال شود که ما ستون تعداد (Qty) را انتخاب نمودیم. در نهایت خروجی مورد نظر به صورت زیر در می آید.

Pivot- Unpivot کردن جداول

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

این گونه ساختار برای انجام عملیات تحلیلی اصلا مناسب نیست و باید جدول را به ساختار مناسب تبدیل کرد. برای این منظور از ستون Student را انتخاب نموده و از تب Transform گزینه Unpivot Other Columns را انتخاب می کنیم.

در نهایت جدول ابتدایی به جدول زیر تبدیل می گردد و انجام هر گونه عملیات تحلیلی را امکان پذیر می کند.

ترکیب داده ها در Power Query

حال به نحوه ترکیب داده ها در Power Query می پردازیم.

ادغام جداول

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

فرض کنید اطلاعات فروش 3 سال 1396، 1397 و 1398 را به صورت جداگانه در اختیار ما قرار داده اند. در تصویر ساختار جداول را مشاهده می نمایید.

برای تجمیع این فایل ابتدا به هر کدام از این جداول سال را اضافه می کنیم.

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

حال می توانیم از تب Home، عمل Append Queries را انتخاب کنیم. در صورتی که Append Queries as New را انتخاب کنیم عمل ادغام در یک جدول جدید اتفاق می افتد.

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

جدول نهایی به صورت زیر خواهد بود. توجه کنید که این جدول با نام Append1 شده که ما نام آن را به Sales تغییر می دهیم.

فراموش نکنید برای انجام عمل Append باید ساختار و حتی نام ستون ها در هر 3 جدول یکی باشد. در غیر این صورت این عمل به درستی انجام نمی شود.

Join کردن جداول

به مثال زیر توجه کنید. جدول Sales شامل اطلاعات فروش می باشد. جدول Customers شامل اطلاعات مشتریان می باشد.

این 2 جدول را در تصویر مشاهده می نمایید که از طریق CustomerID با هم ارتباط برقرار کرده اند.

شاید شما بخواهید نام مشتریان را در جدول فروش داشته باشید، در حالیکه در حال حاضر تنها کلید مشتریان در جدول فروش آمده است. برای این منظور به عمل Merge یا Join نیاز دارید. برای این عمل Merge را انتخاب نمایید.

در باکس باز شده ستون مشترک را انتخاب نمایید.

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

اگر بر روی علامت موجود در تصویر کلیک کنید لیستی باز می شود که از شما می خواهد ستون های مورد نیاز خود را انتخاب نمایید. تنها ستون CustomerName در جدول ما موجود نمی باشد، لذا فقط همان ستون را انتخاب می نماییم. اگر گزینه Use Original Column name as prefix را فعال کنیم نام جدول پیش از نام ستون ظاهر می شود. ما این گزینه را غیر فعال نموده ایم.

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

توجه!