Nested if conditions with NULL values

Power Query
1

Hi

I have been trying to calculate value for my dataset base on conditions but no success:

image
image964×140 7.73 KB

image
image1133×209 17 KB

Query is : Table.AddColumn(#“Inserted Merged Column2”, “Custom”, each if [L1] <> NULL and [L2] = NULL and [L3]= NULL and [Sign Off Time]= “00.00” then [End Time] else if [L1] <> NULL and [L2] = NULL and [L3]= NULL and [Sign Off Time]<> “00.00” then [Sign Off Time] else if [L1]<> NULL and [L2] <>NULL and [L3]=NULL and [#“Sign Off Time - 2”]= “00.00” then [#“End Location - 2”] else if [L1]<> NULL and [L2] <>NULL and [L3]=NULL and [#“Sign Off Time - 2”] <> “00.00” then [#“Sign Off Time - 2”] else if [L1]<> NULL and [L2] <>NULL and [L3] <> NULL and [#“Sign Off Time - 3”] = “00.00” then [#“End Time- 3”] else if [L1]<> NULL and [L2]<> NULL and [L3] <> NULL and [#“Sign Off Time - 3”] <> “00.00” then [#“Sign Off Time - 3”] else “unknown”)

Where am I wrong ? Could you please help. I am not sure what I should use power query or Dax and how. I am attaching dataset below:

sample - dui.pbix (100.9 KB)

Iwona

2

Hi @Iwona ,

Could you please share the sample input file as well ?

Regards,
Rajender

3

Hi @rajender1984
This is sample of dataset:
sample - dui.pbix (100.9 KB)

This works:

= Table.AddColumn(#“Inserted Merged Column2”, “diff”, each if [L1] <> null and [L2] = null and [L3]= null and [Sign Off Time] = “00.00” then [End Time] else if [L1] <> null and [L2] = null and [L3]= null and [Sign Off Time]<>“00.00” then [Sign Off Time] else if [L1]<>null and [L2] <>null and [L3]=null and [#“Sign Off Time - 2”]= “00.00” then [#“End Time -2”] else if [L1]<> null and [L2] <>null and [L3]=null and [#“Sign Off Time - 2”] <> “00.00” then [#“Sign Off Time - 2”] else if [L1]<> null and [L2] <>null and [L3] <> null and [#“Sign Off Time - 3”] = “00.00” then [#“End Time- 3”] else if [L1]<>null and [L2]<> null and [L3] <> null and [#“Sign Off Time - 3”] <> “00.00” then [#“Sign Off Time - 3”] else “unknown”)

1 Like