انواع Join در Power Query

حتما تا به حال نیاز داشته اید که دو یا چند جدول را با یکدیگر Join کنید. Join کردن جداول یکی از متداول ترین اعمالی است که معمولا در مرحله ETL در پروژه های داشبورد و BI به آن نیاز پیدا می کنید. در این مطلب با نحوه Join و انواع آن در Power Query آشنا خواهید شد.

join در مباحث مدل سازی داده کاربرد زیادی دارد. برای آگاهی بیشتر می توانید مطالب زیر را مطالعه نمایید:

عمل Join

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

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

اگر بخواهیم اطلاعات این ۲ جدول را در کنار هم داشته باشیم باید عمل Join را انجام دهیم که در Power BI به این عمل Merge گفته می شود. برای انجام عمل Merge مطابق تصویر از گزینه Merge Queries استفاده می کنیم.

بعد از انتخاب گزینه یک برگه مطابق تصویر برای کاربر نمایش داده خواهد شد. در این برگه می بایست ستون های مشترک از هر دو جدول برای عمل Merge انتخاب شوند، در نهایت یکی از انواع Join نیز انتخاب می شود که ما در این مثال Inner Join را انتخاب می کنیم.

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

۳ ستون ابتدایی این جدول از جدول Customers که ابتدا انتخاب شده و ستون چهارم حاوی ستون های جدول Orders است. با استفاده از گزینه مشخص شده در تصویر برگه ای باز می شود که می توانید ستون های مورد نظر خود را از میان ستون های جدول Orders انتخاب کنید.

در این مثال ستون CustomerID به دلیل وجود داشتن در جدول Customers و برای جلوگیری از وجود ستون تکراری از ستون ها حذف شده است. در صورتی که گزینه Use original column name as prefix انتخاب شده باشد نام جدول دوم (Orders) در ابتدای نام هر ستون قرار می گیرد تا مشخص شود این ستون ها از کدام جدول به جدول نهایی اضافه شده اند.

انواع Join در Power BI

در Power BI و از طریق Power Query نیز می توانیم انواعی از Join را انجام دهیم. جدولی که در ابتدا انتخاب می کنید به عنوان جدول چپ و جدول دوم به عنوان جدول راست در نظر گرفته می شود. در Power Query انتخاب پیش فرض Left outer Join می باشد. انواع دیگر Join در Power Query در تصویر زیر قابل مشاهده است.

حال با هم به مثال هایی در مورد انواع Join می پردازیم.

Left outer Join

همان طور که پیش تر گفته شد انتخاب پیش فرض Power Query گزینه Left outer Join است که این عمل به این معنی است که تمامی ردیف های جدول اول چه ردیف متناظری در جدول دوم داشته باشند چه نداشته باشند در جدول نهایی ظاهر خواهند شد. اگر به ۲ جدول ابتدایی دقت کنید، متوجه خواهید شد که ۲ ردیف در جدول مشتریان وجود دارد (احمد و سارا) که در جدول سفارشات خریدی برای آن ها ثبت نشده است.  با عمل left Join این دو ردیف نیز در جدول نهایی ظاهر خواهند شد. این بدین معنی است که تمام مشتریان چه تا کنون از ما خرید کرده اند چه تا کنون خریدی انجام نداده اند در خروجی ظاهر می شوند.

Right outer Join

در این نوع تمامی ردیف های جدول دوم چه ردیف متناظری در جدول اول داشته باشند چه نداشته باشند در جدول نهایی ظاهر خواهند شد. اگر به ۲ جدول ابتدایی توجه کنید متوجه خواهید شد که در جدول سفارشات، یک سفارش بدون مشتری ثبت شده است. لذا تمامی ردیف های جدول دوم (سفارشات) چه متناظری در جدول اول (مشتریان) داشته باشند چه نداشته باشند در جدول نهایی ظاهر می شوند. این بدین معنی است که تمامی سفارشات چه مشتری آن مشخص باشد چه مشخص نباشد در جدول نهایی ظاهر خواهد شد.

Inner Join

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

Full outer Join

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

Left Anti Join

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

Right Anti Join

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

کدام نوع Join را انتخاب کنیم؟

انتخاب نوع Join به نوع پرس و جو و هدف ما وابسته است. هر کدام از انواع Join برای هدف خاصی به وجود آمده اند و برتری نسبت به دیگری ندارند. به طور مثال اگر از Inner Join استفاده کنیم و قصد مشاهده میزان خرید مشتریان را داشته باشیم مشتریانی که از ما خرید نکرده اند را مشاهده نخواهیم کرد اما اگر از Left Join استفاده کنیم این مشتریان نیز در خروجی نمایش داده خواهند شد. پس بهتر است از خود بپرسید که چه نوع پرس و جویی مد نظر شماست؟

تذکر

ترتیب جداول در Join اهمیت دارد. جدول ابتدایی به عنوان جدول چپ و جدول دوم به عنوان جدول راست در نظر گرفته می شود. اگر جای جداول را عوض کنیم نتیجه تغییر خواهد کرد.