Power Query - If statement to return date

[KasperSSI]

Hi,

I want my if statment to return a date from a date column. However it returns Error. format of the date column dd-mm-yyyy.

I tried
if [Movement] = "opening or "counting" then "" else [date]

any one has a solution for this?

 

[jimrward]

Try with or [movement]=“counting”

 

[Alex Blakenburg]

Further to Jim's comment:
1) Date.ToText is only going to work if you have applied a Changed Type step and made sure the Date column data type is Date
2) Your formula as Jim mentioned should look like this:

if ([Movement] = "opening" or [Movement] = "counting") then "" else Date.ToText([Date])

3) Although in Excel your only option is to insert "" in Power Query you could have used "null" (without the quotes).
This leaves the cell blank rather than having it filled with an empty string.

PS: Do you really want the resulting Date to be a Text String ? That is bound to cause you issues later on.

 

[KasperSSI]

Further to Jim's comment:
1) Date.ToText is only going to work if you have applied a Changed Type step and made sure the Date column data type is Date
2) Your formula as Jim mentioned should look like this:

if ([Movement] = "opening" or [Movement] = "counting") then "" else Date.ToText([Date])

3) Although in Excel your only option is to insert "" in Power Query you could have used "null" (without the quotes).
This leaves the cell blank rather than having it filled with an empty string.

PS: Do you really want the resulting Date to be a Text String ? That is bound to cause you issues later on.

Thanks for the help. It worked - Also found a solution via. Conditional column.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.