Is there a way to convert nulls to blanks in a date data type column in PowerBI/Power Query?

Question

I'm utilizing a combined SharePoint list into a single merged table in power BI. There are three columns related to resting dates for various diseases: [TestDate1], [TestDate2], [TestDate3].

I have this logic in a custom column:

if [TestDate1] <= [TestDate2] then [TestDate1] else if [TestDate1] <= [TestDate3] then [TestDate1] else if [TestDate2] <= [TestDate1] then [TestDate2] else if [TestDate2] <= [TestDate3] then [TestDate2] else if [TestDate3] <= [TestDate1] then [TestDate3] else if [TestDate3] <= [TestDate2] then [TestDate3] else "" 

However, all of the TestDates are null. This is causing an error in the column because nulls cannot be used in a logical function in PowerQuery (from my understanding), so is there a way I can test this logic to see if it would produce the expected result?

Thanks!

Answer 1

You could explicitly test for nulls in your formula such as

= if [TestDate1] = null then XXX else YYY

but your best bet might be to wrap the whole thing in try .. otherwise in case an error is returned

= try if a then b else c otherwise ZZZ

For your code:

= try if [TestDate1] <= [TestDate2] then [TestDate1] else if [TestDate1] <= [TestDate3] then [TestDate1] else if [TestDate2] <= [TestDate1] then [TestDate2] else if [TestDate2] <= [TestDate3] then [TestDate2] else if [TestDate3] <= [TestDate1] then [TestDate3] else if [TestDate3] <= [TestDate2] then [TestDate3] else "" otherwise ZZZZ

where ZZZ is the thing you'd like to return (like null or [TestDate1] or "") in place of returning an error