Dataflow scheduled refresh fails with Expression Error Connected to ADLSGen2

[smjzahid]

I have a dataflow connected to ADLS Gen2 as the connector. This dataflow has many transformation steps applied for example, 

converting date to type text, replacing * with nothing, replacing (space) etc, followed my merging queries, so far the dataflow has worked fine, but I noticed in the Power Bi Service it failed and downloading the status report suggest below cause for the error.

My questions is how to I figure out as to which column (or which step) is causing this error, How to fix this, any help is appreciated.

[edhans · Accepted solution]

You will need to provide us some data or screenshots of your actual data and error to see what is going on, but you probably have some sort of error where you are making a comparison and there is a null in there. For example, if you add the following to a custom column, it will work:

= null = null and null = 1

That will return false, which is ok. If you do this:

= null = null and null that returns null, which is also ok. But... if you do this:

= if (null = null and null) > 1 then true else false

That returns an error. the null the formula in parens returns cannot be compared to 1 in a logical way, so it doesn't return true or false, but error.

So either fix your nulls to 0 if that is what you need (replace values, or replace the above logic with this:

 = try if (null = null and null) > 1 then true else false otherwise false

That returns false. It says "Try the if/then/else and return that result, but if it return an error, just return false."

try/otherwise is like IFERROR() in Excel.

[smpa01]

@smjzahidhard to comment without looking at the real query, but one of the reasons could be flawed logic.

 Error reproduced

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MFCK1YlWAjKNoEwjIFspNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, val = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"val", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if ([Index]=1 and [val]<>null) then 1 else if ([Index]>1 and [val]<>null) then 2 else 0)
in
    #"Added Custom"