Blank() equivalent in Power Query Editor

Power Query
1

Hi All

Wondering if you can help me out here …

Trying to add a column in Power Query which ultimately says

(1) if the Estimated Completion Date “Is Less Than” Todays Date then return the start date plus 1 month

(2) if the Estimated Completion Date “Is equal to Blank” then return the start date plus 1 month

(3) if Estimated Completion Date “Is Less Than” the start date then return the start date plus 1 month

if ([Est Comp Date] < DateTime.Date(DateTime.FixedLocalNow()) or ([Est Comp Date] = BLANK() ) or ([Est Comp Date] < [#"*Start Date"])) then Date.AddMonths([#"*Start Date"],1) else [Est Comp Date]

Majority of it seems to work except for the handling of Blank() which returns an error.

I’ve tried type.nullable that didnt work either

any help much appreciated

Thanks

Martyn

Start - End Date error
Start - End Date error354×516 9.08 KB

2

@martynross85,

Try replacing BLANK() with null. That should work, but if not please post your PBIX so that I can test alternatives.

Thanks.

  • Brian
3

Hi @BrianJ

Thanks for that - i had previously tried using the “Null” value but was still receiving errors.

I didn’t realise that it was case sensitive and should be “null” rather than “Null” - really annoying tbh.

Thanks for your help

Much appreciated

Martyn

4

@martynross85,

Glad to help. I’ve been burned by that “DAX is case insensitive, M is not” inconsistency many times myself. I would definitely like to have a word with whoever made that decision…

  • Brian
1 Like