Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JWO16
Frequent Visitor

Compare column query generating error (Custom Column)

Must admit, I'm extremely confused by this but since I only dabble with Power Query, I'm hoping to learn something. 

 

I've tried adding an additional custom field to my data (code below)

if([RemainsOpen] = 1) then "Ignore" 
else if ([ClosedDate] is null and [ResolvedDate] is null) then "No End Dates" 
else if ([Iteration.EndDate] is null) then "No Sprint End Date" 
else if ([ClosedDate] <= [Iteration.EndDate]) then "C_In Time" 
else if ([ClosedDate] > [Iteration.EndDate]) then "C_Late" 
else if ([ResolvedDate] <= [Iteration.EndDate]) then "R_In Time" 
else if ([ResolvedDate] > [Iteration.EndDate]) then "R_Late" 
else "Anything else"

I'm trying to work out whether tasks have been completed in time or not.  Unfortunately the data isn't great so what should be a couple of lines of code has multiplied owing to the Nulls across the sample.

 

Basically all I'm after is the ability to compare closed date with end date, and if closed date is null to use resolved date instead.  When I try and run it I get this error message:

 

[Expression.Error] We cannot convert the value null to type Logical.

 

I've spent a bit of time looking at this and set the null values to 01/01/1900 just in case but that didn't solve it (so I abandoned that and put it back as it was), and amendments to the query have only increased the number of errors. I tried to set up another column but swapped the Resolved Date comparison so that came before the Closed Date one.  It still errored but it was a different number of errors.

 

Looking back at my first column (the query posted) - it seems to be having trouble with the R_In Time and R_Late results only.  Is there a max number of IFs/Else IFs you're allowed to use at once? What's the best way to resolve this??

1 ACCEPTED SOLUTION

Hi @JWO16 ,

 

Please give this a go:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiIwNdQyNdIwNkZqxOtJIRDjmgJpC0CZoYmm5DnLpRlKFr1VEyMNQFIiNDok2JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RemainsOpen = _t, ClosedDate = _t, ResolvedDate = _t, Iteration.EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Iteration.EndDate", type date}, {"ResolvedDate", type date}, {"ClosedDate", type date}},"en-gb"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"RemainsOpen", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [RemainsOpen] = 1 then "Ignore" 
else if [ClosedDate] =  null and [ResolvedDate] = null then "No End Dates" 
else if [Iteration.EndDate] = null then "No Sprint End Date" 
else if [ClosedDate] <> null and [ClosedDate] <= [Iteration.EndDate] then "C_In Time" 
else if [ClosedDate] <> null and [ClosedDate] > [Iteration.EndDate] then "C_Late" 
else if [ResolvedDate] <= [Iteration.EndDate] then "R_In Time" 
else if [ResolvedDate] > [Iteration.EndDate] then "R_Late" 
else "Anything else")
in
    #"Added Custom"

 

This version correctly identifies your error value as 'R_In Time'.

The issue the previous code had was that it could not progress far enough down the conditions list to evaluate this specific row due to previous conditions evaluating a null [ClosedDate] value. I've bypassed this by adding '[ClosedDate] <> null and...' conditions where [ClosedDate] must be evaluated.

 

I would also recommend that you apply a similar method to your [ResolvedDate] conditions as, although the test data does not test the evaluation, I think you would get a similar error if one of the previous conditions does not evaluate to true before one of the [ResolvedDate] conditions are evaluated near the end of the conditions list.

 

Hope this makes sense.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

6 REPLIES 6
Fowmy
Super User
Super User

@JWO16 

It works for me after some minor changes:, Please check your data formats
You can paste the code below in the advanced editor of a Blank Query and check,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiIwNdQyNdIwNkZqxOtJIRDjmgJpC0CZoYmm5DnLoxlGExnBjdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RemainsOpen = _t, ClosedDate = _t, ResolvedDate = _t, Iteration.EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Iteration.EndDate", type date}, {"ResolvedDate", type date}, {"ClosedDate", type date}},"en-gb"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"RemainsOpen", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [RemainsOpen] = 1 then "Ignore" 
else if [ClosedDate] =  null and [ResolvedDate] = null then "No End Dates" 
else if [Iteration.EndDate] = null then "No Sprint End Date" 
else if [ClosedDate] <= [Iteration.EndDate] then "C_In Time" 
else if [ClosedDate] > [Iteration.EndDate] then "C_Late" 
else if [ResolvedDate] <= [Iteration.EndDate] then "R_In Time" 
else if [ResolvedDate] > [Iteration.EndDate] then "R_Late" 
else "Anything else")
in
    #"Added Custom"

 

Screenshot 2020-12-20 173903.png

 

 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂


Website YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

JWO16
Frequent Visitor

JWO16_0-1608482352313.png

@Fowmy Thanks for your reply. It looks as though your solution has the same issue as mine though unfortunately.  Is it down to the number of IFs / Elses in the query??

 

Anonymous
Not applicable

Hi @JWO16,

 

The code provided by @Fowmy  looks right, and the number of ifs should not be an issue. I think what is happening that due to lazy calc model PQ may be bringing some issues from upstream. I would suggest simplifying the formula. If this is practicable, delete conditions one by one and check when the error disappears. This will give you a narrower path to trace. At least, you will know what condition/fields contribute to the error.

Then you can investigate the code that generates those values/fields.

 

Kind regards,

JB

 

 

Hey @Anonymous,

 

Thanks for your reply. I think the only difference (and the one to cause the error) between the query that @Fowmy provided and my latest attempt was the change made to that row, changing some of the data. 

 

I'm going to see if I can split it across a couple of fields which is probably not the most efficient way but if it gets it over the line then I might stumble on the cause in the future. 😃

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiIwNdQyNdIwNkZqxOtJIRDjmgJpC0CZoYmm5DnLpRlKFr1VEyMNQFIiNDok2JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RemainsOpen = _t, ClosedDate = _t, ResolvedDate = _t, Iteration.EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Iteration.EndDate", type date}, {"ResolvedDate", type date}, {"ClosedDate", type date}},"en-gb"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"RemainsOpen", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [RemainsOpen] = 1 then "Ignore" 
else if [ClosedDate] =  null and [ResolvedDate] = null then "No End Dates" 
else if [Iteration.EndDate] = null then "No Sprint End Date" 
else if [ClosedDate] <= [Iteration.EndDate] then "C_In Time" 
else if [ClosedDate] > [Iteration.EndDate] then "C_Late" 
else if [ResolvedDate] <= [Iteration.EndDate] then "R_In Time" 
else if [ResolvedDate] > [Iteration.EndDate] then "R_Late" 
else "Anything else")
in
    #"Added Custom"

 

Hi @JWO16 ,

 

Please give this a go:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiIwNdQyNdIwNkZqxOtJIRDjmgJpC0CZoYmm5DnLpRlKFr1VEyMNQFIiNDok2JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RemainsOpen = _t, ClosedDate = _t, ResolvedDate = _t, Iteration.EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Iteration.EndDate", type date}, {"ResolvedDate", type date}, {"ClosedDate", type date}},"en-gb"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"RemainsOpen", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [RemainsOpen] = 1 then "Ignore" 
else if [ClosedDate] =  null and [ResolvedDate] = null then "No End Dates" 
else if [Iteration.EndDate] = null then "No Sprint End Date" 
else if [ClosedDate] <> null and [ClosedDate] <= [Iteration.EndDate] then "C_In Time" 
else if [ClosedDate] <> null and [ClosedDate] > [Iteration.EndDate] then "C_Late" 
else if [ResolvedDate] <= [Iteration.EndDate] then "R_In Time" 
else if [ResolvedDate] > [Iteration.EndDate] then "R_Late" 
else "Anything else")
in
    #"Added Custom"

 

This version correctly identifies your error value as 'R_In Time'.

The issue the previous code had was that it could not progress far enough down the conditions list to evaluate this specific row due to previous conditions evaluating a null [ClosedDate] value. I've bypassed this by adding '[ClosedDate] <> null and...' conditions where [ClosedDate] must be evaluated.

 

I would also recommend that you apply a similar method to your [ResolvedDate] conditions as, although the test data does not test the evaluation, I think you would get a similar error if one of the previous conditions does not evaluate to true before one of the [ResolvedDate] conditions are evaluated near the end of the conditions list.

 

Hope this makes sense.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




JWO16
Frequent Visitor

Thanks @BA_Pete - that's amazing!

 

I'd managed to fudge it but it meant creating multiple fields just to do the background working out. This will keep things together though which is ideal.  What you've said makes total sense too, and appreciate you taking the time to explain as well.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors