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

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

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

به منظور  بررسی فیلتر جدول با مقادیر جدول دیگر در Power Query به پیاده سازی یک سناریو می پردازیم. همان طور که خوب می دانید جدول DimDate یا همان بعد تاریخ یکی از مهمترین جداولی است که باید در هر پروژه ای وجود داشته باشد. این جدول تعداد زیادی ردیف دارد. به طور مثال جدول بعد تاریخ ما از سال ۱۳۲۰ تا سال ۱۴۲۰ را در خود جای داده است. پس حساب کنید ۱۰۰ سال و هر سال ۳۶۵ روز که حدود ۳۶۰۰۰ رکورد را در بر می گیرد.

شما می توانید با ترفند هایی این جدول را فیلتر کنید و فقط سال های مورد نیاز خود را بارگذاری کنید. مثلا می توانید بگویید ۱۰۰ سال زمان به درد من نمی خورد. من عملیات فیلترینگ را روی جدول انجام داده و سال های بین ۱۳۹۰ تا ۱۴۱۰ را فیلتر می کنم. filter power query - فیلتر جدول با جدول دیگر در Power Queryبا این کار ۱۰۰ سال را به ۲۰ سال کاهش می دهیم و تعداد رکورد ها را کاهش می دهیم. همیشه بر این نکته تاکید داشتیم که تا جای امکان از بارگذاری ستون ها و ردیف های اضافی در مدل خود خودداری کنید.

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

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

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

filter power query 2 - فیلتر جدول با جدول دیگر در Power Query

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

filter power query 3 - فیلتر جدول با جدول دیگر در Power Query

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

filter power query 4 - فیلتر جدول با جدول دیگر در Power Query

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

filter power query 5 - فیلتر جدول با جدول دیگر در Power Query

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

filter power query 8 - فیلتر جدول با جدول دیگر در Power Query

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

filter power query 9 - فیلتر جدول با جدول دیگر در Power Query

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

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

filter power query 11 - فیلتر جدول با جدول دیگر در Power Query

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

filter power query 12 - فیلتر جدول با جدول دیگر در Power Query

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

filter power query 13 - فیلتر جدول با جدول دیگر در Power Query

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

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

filter power query 14 - فیلتر جدول با جدول دیگر در Power Query

حدس می زنید چه تغییری در جدول تاریخ ایجاد شود؟ آفرین درست حدس زدید. ۳۱ ردیف به جدول تاریخ ما اضافه می شود و جدول ما ۴۹۰ رکورد در بر خواهد داشت.

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

filter power query 16 - فیلتر جدول با جدول دیگر در Power Query

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

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



6b77990865d588b48d7f55a013cdd7ec?s=300&d=mm&r=g - فیلتر جدول با جدول دیگر در Power Query

حسین وثوقی

دانش آموخته مهندسی صنایع و مدیریت فناوری اطلاعات دانشگاه تهران، علاقه مند به تحلیل و ارائه راه حل برای مسائل و بهینه سازی راه حل ها هستم ...
0 0 رای
Article Rating
عضویت
به دوستتان خبر دهید.
guest
2 Comments
قدیمی ها
جدید ها بیشترین رای
Inline Feedbacks
View all comments
سجادیان
سجادیان
1 ماه گذشته

سلام مهندس جان ممنون ،خیلی مفید بود واقعا
👏👏👏

2
0
عالی میشه اگر نظر بدید.x
()
x