If column (type decimal number) is blank then replace with text (Calculated Column)

[murphm6]

Hi all,

Has anyone ever ran into this before? I have a calculated column that rounds to the nearest whole number and i want to create a new column which basically says if there are any blank rows then replace the blank with "No MOQ Listed". When I try to do this, i get an error that i can't use variant data types. If i change the number column to text - it no longer rounds the number to the nearest whole number

When I use a measure and try and add this to my matrix visual, it doesn't load and PowerBI runs out of memory.. any solutions?

[Sahir_Maharaj · Accepted solution]

Hello @murphm6,

1. To address the issue of rounding and replacing blank rows with "No MOQ Listed" in a calculated column, you can use a combination of IF and ISBLANK functions.

NewColumn =
IF (
    ISBLANK ( Table[NumberColumn] ),
    "No MOQ Listed",
    ROUND ( Table[NumberColumn], 0 )
)

2. If changing the number column to text prevents the rounding functionality, you can try converting the rounded number to text explicitly within the formula.

NewColumn =
IF (
    ISBLANK ( Table[NumberColumn] ),
    "No MOQ Listed",
    FORMAT ( ROUND ( Table[NumberColumn], 0 ), "0" )
)

If your measure is causing Power BI to run out of memory, you can consider optimizing it. Aggregating large volumes of data or performing complex calculations in measures can consume significant memory resources. Consider filtering the data in your measure to reduce the volume of data being processed.
Evaluate whether you can pre-calculate and store some values in a calculated column instead of using a measure.

Should you require further details or assistance please do not hesitate to reach out to me.