۴ روش برای محاسبه مجموع مقادیر Null در Power Query M
آیا هنگام جمع‌زدن (SUM) ستون‌هایی که شامل مقادیر Null هستند در Power Query با نتایج غیرمنتظره روبرو می‌شوید؟
این یکی از مشکلات رایج بین کاربران تازه‌کار Power Query و Power BI است.
دلیل این موضوع به رفتار خاص مقادیر Null در Power Query برمی‌گردد. در واقع Null در Power Query به معنای «بدون مقدار» است و در بسیاری از توابع ریاضی می‌تواند باعث بروز خطا یا خروجی نامطلوب شود.
در این مقاله، به بررسی جزئیات رفتار مقادیر Null می‌پردازیم؛ از ویژگی‌های پایه Null شروع می‌کنیم و در پایان مقاله با ۳ روش مؤثر برای جمع‌زدن (SUM) مقادیر Null در Power Query آشنا می‌شوید.
فهرست مطالب
۱. مشکل جمع‌زدن مقادیر Null در Power Query
آیا تا به حال در Power Query با یک مقدار Null مواجه شده‌اید و سعی کرده‌اید آن را با یک عدد جمع بزنید؟
اگر این کار را انجام داده باشید، احتمالاً متوجه شده‌اید که نتیجه همیشه Null خواهد بود.
این رفتار می‌تواند گیج‌کننده باشد، چراکه اکثر کاربران انتظار چنین نتیجه‌ای ندارند. در واقع، بسیاری تصور می‌کنند که مقادیر Null باید مانند عدد صفر در محاسبات جمع در نظر گرفته شوند — اما در Power Query این‌طور نیست.
بیایید یک سناریوی رایج را بررسی کنیم:
شما یک مجموعه داده شامل سه ستون دارید: نام محصول، قیمت بدون مالیات (Price Excl VAT) و میزان مالیات (Tax Amount) — البته میزان مالیات در برخی ردیف‌ها ممکن است وجود نداشته باشد (Null باشد).
به عنوان کاربر، می‌خواهید قیمت با احتساب مالیات (Price Incl VAT) را محاسبه کنید. در ظاهر، این کار باید به‌سادگی جمع‌زدن ستون Tax Amount با ستون Price Excl VAT باشد، درست است؟
اما متأسفانه زمانی که برخی از ردیف‌ها در ستون Tax Amount مقدار Null دارند، قضیه پیچیده می‌شود.
در واقع، وقتی یک مقدار Null را با یک عدد جمع می‌زنید، نتیجه نهایی Null خواهد شد.
این موضوع می‌تواند به‌ویژه برای کاربرانی که تازه با Power Query کار می‌کنند، مبهم و آزاردهنده باشد.
قبل از آنکه به سراغ روش‌های رفع این مشکل برویم، ابتدا لازم است کمی عمیق‌تر به مفهوم مقادیر Null در Power Query بپردازیم و درک بهتری از آن‌ها پیدا کنیم. سپس می‌توانیم به بررسی روش‌های مختلف برای مدیریت این مقادیر برسیم.
۲. درک مقادیر Null در Power Query
در وهله اول، باید بدانید که مقدار Null نشان‌دهنده یک سلول خالی یا نبود داده است.
نکته مهم این است که Null در واقع یک مقدار محسوب نمی‌شود.
در حالی که عدد صفر (0) یک مقدار معتبر است، Null فقط نشانه‌ی «نبود مقدار» است.
همین ویژگی باعث می‌شود که مقادیر Null در Power Query رفتار خاصی از خود نشان دهند.
برای مثال، یک مقدار Null در یک فرمول با عدد صفر تفاوت دارد و حتی با مقدار خالی (Blank) هم یکسان نیست.
در واقع، هنگام مقایسه‌ی مقدار Null با سایر مقادیر، نتیجه‌ای که دریافت می‌کنید ممکن است برخلاف انتظارتان باشد و همین موضوع می‌تواند باعث سردرگمی شود.
null = null = true
null = 0 = false
null <> null = false
null = "" = false
نکته‌ی دیگری که باید به خاطر داشته باشید این است که در هنگام مقایسه مقدار Null با اکثر عملگرها، نتیجه نهایی خود Null خواهد بود.
به بیان ساده‌تر، اگر یک مقدار Null را با استفاده از عملگرهایی مانند =، >، < و غیره مقایسه کنید، خروجی معمولاً Null خواهد بود — نه True و نه False.
این رفتار خاص می‌تواند در زمان نوشتن شرط‌ها (Conditions) یا استفاده از توابع منطقی در Power Query باعث نتایج غیرمنتظره شود.
null < 1 = null
8 + null = null
2 * null = null
4 / null = null
"abc" & null = null
این نکته به‌ویژه هنگام نوشتن کد در Power Query اهمیت پیدا می‌کند؛ چراکه ممکن است بخواهید برنامه به شکلی خاص واکنش نشان دهد اگر با یک مقدار Null مواجه شد.
اکنون که با مفهوم مقادیر Null و رفتار آن‌ها در زبان M بیشتر آشنا شدید، شاید بپرسید:
چطور می‌توان مقادیر Null را در Power Query جمع زد؟
۳. روش‌های جمع‌زدن مقادیر Null در Power Query
در این بخش، به بررسی ۴ روش مختلف برای جمع‌زدن مقادیر Null در Power Query می‌پردازیم.
این تکنیک‌ها به شما کمک می‌کنند تا محاسبات جمع (SUM) را به‌درستی انجام دهید، حتی زمانی که داده‌های شما دارای مقادیر Null هستند.
۳.۱. جایگزینی Null با صفر (Replace Null by 0)
اولین و ساده‌ترین روش برای جمع‌زدن مقادیر Null این است که آن‌ها را با عدد صفر جایگزین کنید.
این روش کاملاً ساده و بدون نیاز به فرمول‌های پیچیده است.
با جایگزین کردن مقادیر Null با ۰، در واقع این مقادیر را از حالت «ناشناخته» به یک مقدار عددی تبدیل می‌کنید، و می‌توانید بدون هیچ مشکلی آن‌ها را در محاسبات جمع لحاظ کنید.
مراحل جایگزینی Null با صفر در Power Query:
۱. روی ستونی که شامل مقادیر Null است راست‌کلیک کنید.
۲. از منوی بازشده، گزینه‌ی Replace Values را انتخاب کنید.
3. در پنجره‌ی Replace Values مراحل زیر را انجام دهید:
در فیلد Value to Find مقدار
"null"(بدون علامت نقل‌قول) را وارد کنید.در فیلد Replace With مقدار
"0"(بدون علامت نقل‌قول) را وارد کنید.روی دکمه‌ی OK کلیک کنید تا تمام مقادیر Null در آن ستون با ۰ جایگزین شوند.
اکنون ستون شما فاقد مقدار Null است و می‌توانید با خیال راحت از تابع SUM یا سایر محاسبات عددی استفاده کنید.
۳.۲. بررسی Null با استفاده از دستور If
(Check Null with If Statement)
روش دیگر برای مدیریت مقادیر Null، استفاده از عبارت شرطی If است تا قبل از انجام محاسبه‌ی جمع، بررسی کنیم که آیا مقدار Null است یا خیر.
در Power Query می‌توانید با استفاده از عبارت if ... then ... else این شرط را پیاده‌سازی کنید.
در این روش، اگر مقدار یک سلول Null باشد، می‌توانید به جای آن مقدار ۰ برگردانید؛ یا می‌توانید تصمیم بگیرید که اصلاً آن مقدار را در جمع لحاظ نکنید.
به این ترتیب می‌توانید محاسبات خود را ایمن‌تر و قابل کنترل‌تر انجام دهید.
یک نمونه ساده از نوشتن چنین فرمولی به صورت زیر است:
if [Tax Amount] = null
then [Price Excl VAT]
else [Price Excl VAT] + [Tax Amount]
در این مثال، ما بررسی می‌کنیم که آیا ستون Tax Amount دارای مقدار Null است یا خیر.
اگر مقدار Null باشد، مقدار ستون Price Excl VAT را به عنوان نتیجه بازمی‌گردانیم.
اما اگر مقدار Null نباشد، مقدار Tax Amount را به Price Excl VAT اضافه می‌کنیم.
این روش در شرایطی که تنها با چند ستون دارای مقادیر Null کار می‌کنید، بسیار مؤثر است.
اما زمانی که تعداد ستون‌های دارای Null زیاد شود، استفاده از این رویکرد می‌تواند زمان‌بر و مستعد خطا باشد؛ چرا که باید برای هر ستون به صورت جداگانه یک عبارت if-then-else بنویسید.
در بخش بعدی، با یک راهکار کارآمدتر و مقیاس‌پذیرتر آشنا می‌شویم که امکان مدیریت Null در چندین ستون به صورت همزمان را فراهم می‌کند.
۳.۳. استفاده از تابع List.Sum
(Use the Function List.Sum)
برای جمع‌زدن مقادیر Null در Power Query، استفاده از تابع List.Sum می‌تواند یک راهکار قدرتمند باشد.
این تابع مجموع مقادیر غیر Null در یک لیست داده‌شده را محاسبه می‌کند.
اگر تمام مقادیر در لیست Null باشند، خروجی تابع Null خواهد بود.
برای استفاده از روش List.Sum، باید مقادیر خود را به صورت لیستی از مقادیر جداشده با کاما در داخل {} (براکت‌های باز و بسته) قرار دهید.
همچنین می‌توانید در داخل لیست از مقادیر محاسبه‌شده هم استفاده کنید.
یک مثال ساده از نحوه استفاده از تابع List.Sum در Power Query:
= List.Sum( {
[Price Excl VAT],
[Tax Amount]
} )
هنگامی که ستون‌های جدید به داده‌های شما اضافه می‌شوند، تنظیم مجدد فرمول List.Sum نیز بسیار ساده خواهد بود.
این روش به شما اجازه می‌دهد که مقادیر ستون‌ها را جمع بزنید در حالی که مقادیر Null را نادیده می‌گیرید.
۳.۴. استفاده از عملگر Coalesce
(Use the Coalesce Operator)
روش دیگری که می‌توانید برای جمع‌زدن مقادیر Null در Power Query استفاده کنید، بهره‌گیری از عملگر Coalesce (??) است.
این عملگر، اولین مقدار غیر Null را در بین آرگومان‌های ارائه‌شده باز می‌گرداند.
اگر با عملگر Coalesce در Power Query آشنا نیستید، این مقاله می‌تواند نقطه‌ی شروع خوبی برای یادگیری باشد.
برای استفاده از عملگر Coalesce در Power Query، کافی است ستون(ها)ی مدنظر برای جمع‌زدن را به عنوان آرگومان بنویسید.
سپس در ادامه‌ی این ستون، از عملگر Coalesce (??) استفاده کنید و مقدار جایگزین موردنظر خود را نیز مشخص کنید — یعنی مقداری که هنگام مواجهه با Null برگردانده شود.
Power Query در این حالت، اولین مقدار غیر Null را باز می‌گرداند.
برای مثال، فرض کنید جدولی دارید که شامل دو ستون Price Excl VAT و Tax Amount است.
اگر بخواهید این دو ستون را جمع بزنید و در صورتی که ستون [Tax Amount] مقدار Null داشت، مقدار صفر برگردانید، می‌توانید از عملگر Coalesce به شکل زیر استفاده کنید:
= [Price Excl VAT] + ( [Tax Amount]??0 )
این فرمول بررسی می‌کند که آیا مقدار ستون [Tax Amount] برابر با Null است یا خیر.
اگر مقدار Null باشد، مقدار ۰ برگردانده می‌شود، در غیر این صورت مقدار اصلی ستون [Tax Amount] بازگردانده می‌شود.
سپس این مقادیر با مقدار ستون [Price Excl VAT] جمع زده می‌شوند تا جمع نهایی دو ستون به دست آید، به طوری که مقادیر Null به عنوان صفر در نظر گرفته می‌شوند.
این روش به‌ویژه زمانی کاربردی است که تعداد زیادی ستون برای جمع‌زدن دارید، زیرا به شما امکان می‌دهد مقادیر Null را به صورت مختصر، انعطاف‌پذیر و تمیز مدیریت کنید.
فقط مراقب باشید که از براکت‌ها در اطراف عملگر Coalesce به درستی استفاده کنید، زیرا در برخی موارد اگر این کار انجام نشود، نتیجه نادرست خواهد بود.
۴. نتیجه‌گیری
مقادیر Null در Power Query می‌توانند یک چالش پیچیده باشند، اما خوشبختانه روش‌های متعددی برای حل این مشکل وجود دارد.
شما می‌توانید مقادیر Null را با صفر جایگزین کنید، از عبارات شرطی و خطاگیر استفاده کنید، تابع List.Sum را به کار بگیرید، یا از عملگر Coalesce بهره ببرید.
هر روشی که برای شما راحت‌تر است انتخاب کنید، اما به یاد داشته باشید که مقادیر Null برابر با صفر نیستند و رفتار خاص خود را دارند.
هر یک از این روش‌ها مزایا و محدودیت‌های خاص خود را دارند و بهترین گزینه بستگی به سناریوی کاری شما دارد.
چه با چند ستون سروکار داشته باشید و چه با تعداد زیادی ستون، قطعاً روشی وجود دارد که مناسب نیازهای شما باشد.
با داشتن این روش‌ها در دست، شما به خوبی آماده‌اید تا با مقادیر Null در Power Query مقابله کنید و نتایج دقیق و قابل اطمینانی به دست آورید.
دیدگاهتان را بنویسید