using if function for current month to return text value

[shadysalman]

I have status cloumn that has  the status of each rpocess I review on monthly basis. however, since we are in the begining of the year, the status would result to NULL because the relvant processes have not been yet submitted either because they are late or because it is not due yet. Therefore, I was trying to make the culumn replace the NULL based on the current month, where if the table shows the current month and it was empty it should replacle it to "Not submitted yet" otherwise it should return it as "Not due yet". 

When trying thre below formua the synatx is right, but it return in Error 

I would appreciate the support to fix this.

if[Request Status] = "" and [#"Month - Date"] = Date.IsInCurrentMonth then [Request Status] = "Not submitted" else [Request Status] = "Not due yet"

[BA_Pete · Accepted solution]

Hi @shadysalman ,

Without seeing an example of your table I can't be sure that what you are trying to do is actually correct, but I think your formula should read like this:

// Add this as a new CUSTOM COLUMN
if [Request Status] <> null then [Request Status]
else if [Request Status] = null and Date.IsInCurrentMonth([#"Month - Date"]) then "Not Submitted"
else "Not due yet"

You're mixing up functions in your formula by trying to set the value of [Request Status] to your output text.

The formula above will create a new column containing your desired output.

If you want this to replace values in the [Request Status] column, you will need to use Table.ReplaceValue in a custom step, something like this:

// Add this as a CUSTOM STEP
= Table.ReplaceValue(
    previousStepName,
    each [Request Status],
    each if [Request Status] <> null then [Request Status]
    else if [Request Status] = null and Date.IsInCurrentMonth([#"Month - Date"]) then "Not Submitted"
    else "Not due yet",
    Replacer.ReplaceText,{"Request Status"}
)

Pete

[shadysalman]

I want to replace the blank cells in Request status with wither Not submitted or not due based on the current month. 

I hope it is clear and thanks for the support.

[BA_Pete]

Hi @shadysalman ,

I explained how to do this in my post.

Let me know which bit you are getting stuck at and I'll help you through it.

Pete