فیلتر جدول با جدول دیگر در Power Query

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

قبل تر در مورد فیلتر کردن در Power Query به بحث پرداخته ایم.

به منظور  بررسی فیلتر جدول با مقادیر جدول دیگر در Power Query به پیاده سازی یک سناریو می پردازیم. همان طور که خوب می دانید جدول DimDate یا همان بعد تاریخ یکی از مهمترین جداولی است که باید در هر پروژه ای وجود داشته باشد. این جدول تعداد زیادی ردیف دارد. به طور مثال جدول بعد تاریخ ما از سال 1320 تا سال 1420 را در خود جای داده است. پس حساب کنید 100 سال و هر سال 365 روز که حدود 36000 رکورد را در بر می گیرد.

شما می توانید با ترفند هایی این جدول را فیلتر کنید و فقط سال های مورد نیاز خود را بارگذاری کنید. مثلا می توانید بگویید 100 سال زمان به درد من نمی خورد. من عملیات فیلترینگ را روی جدول انجام داده و سال های بین 1390 تا 1410 را فیلتر می کنم. با این کار 100 سال را به 20 سال کاهش می دهیم و تعداد رکورد ها را کاهش می دهیم. همیشه بر این نکته تاکید داشتیم که تا جای امکان از بارگذاری ستون ها و ردیف های اضافی در مدل خود خودداری کنید.

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

خب بیایید به پیاده سازی این سناریو بپردازیم. ابتدا من با این مشکل مواجهم که ستون تاریخ من شمسی و از نوع رشته است. پس با استفاده از عملیات Replace یک ستون جدید ایجاد می کنم و علامت اسلش (/) را از تاریخ های جدول فروش حذف می کنم.

سپس بر روی ستون تاریخ کلیک راست کرده و گزینه Duplicate Column را انتخاب می کنم تا یک کپی از ستون را در اختیار من قرار دهد. سپس با عملیات Replace علامت های مورد نظر را حذف می کنم و نام آن را به نام دلخواه تغییر می دهم.

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

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

حال می خواهیم کمترین و بیشترین مقدار تاریخ را از جدول فروش استخراج کنیم. برای این منظور یک Blank Query ایجاد می کنیم. کافی است بر روی New Source کلیک کنید تا گزینه Blank Query در دسترس شما قرار گیرد.

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

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

حال به جدول DimDate می رویم. ستون تاریخ (ستون تاریخ که به صورت عدد صحیح است) را انتخاب می نماییم. توجه کنید که نوع این ستون نیز باید به نوع عدد صحیح تغییر یافته باشد. حال بر روی زبانه کنار ستون کلیک می کنیم و Numbers Filter را انتخاب می نماییم و گزینه Between را انتخاب می کنیم.

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

خب! به کد ایجاد شده در تصویر که به صورت اتوماتیک ایجاد شده دقت نمایید.

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

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

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

حدس می زنید چه تغییری در جدول تاریخ ایجاد شود؟ آفرین درست حدس زدید. 31 ردیف به جدول تاریخ ما اضافه می شود و جدول ما 490 رکورد در بر خواهد داشت.

تنها یک نکته را به خاطر بسپارید. بر روی 2 کوئری خود کلیک راست کنید و گزینه Enable Load را برای آن ها غیر فعال کنید تا از بارگذاری آن ها در مدل جلوگیری کنید. در واقع شما به این 2 کوئری در مدل خود هیچ نیازی ندارید و فقط برای فیلتر کردن جدول تاریخ خود از آن استفاده می کنید.

شما می توانید همین کار را برای سال انجام دهید و سال های موجود را به طور کامل در جدول بارگذاری کنید.

در این مطلب به بررسی نحوه فیلتر جدول بر اساس مقادیر جدول دیگر در power Query پرداختیم. امیدوارم از این مطلب نهایت استفاده را ببرید و با نظرات خود ما را همراهی کنید.