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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

برای آگاهی از نحوه کار با تاریخ شمسی حتما و حتما این ویدئو را مشاهده فرمایید!

گروه بندی

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ادغام جداول

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

فرض کنید اطلاعات فروش ۳ سال ۱۳۹۶، ۱۳۹۷ و ۱۳۹۸ را به صورت جداگانه در اختیار ما قرار داده اند. در تصویر ساختار جداول را مشاهده می نمایید.

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

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

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

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

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

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

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

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

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

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

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

Join کردن جداول

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

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

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

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

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

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

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

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

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

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

اگر بر روی علامت موجود در تصویر کلیک کنید لیستی باز می شود که از شما می خواهد ستون های مورد نیاز خود را انتخاب نمایید. تنها ستون CustomerName در جدول ما موجود نمی باشد، لذا فقط همان ستون را انتخاب می نماییم. اگر گزینه Use Original Column name as prefix را فعال کنیم نام جدول پیش از نام ستون ظاهر می شود. ما این گزینه را غیر فعال نموده ایم.Screenshot 1031 - آموزش Power BI صفر تا سکو : قسمت پنجم ( ترکیب داده ها در Power Query)

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

توجه!




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

حسین وثوقی

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

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

avatar
  عضویت  
به دوستتان خبر دهید.