Comparision with null value

[Anonymous]

Hello,

I have a table like this:

Index	a1	b1	a2	b2	a3	b3
1	1	3	2	1	3	3
2	2	4	3	5	null	null
3	6	1	7	2	null	null
4	3	null	null	null	null	null

The business logic need comparing each a with b, if a1>b1 or a2>2b or a3>b3 then return TRUE else FALSE. My code is:

= Table.AddColumn(#"Changed Type", "Result", each [a1]>[b1] or [a2]>[b2] or [a3]>[b3])

However, the data source always have some null values and I'm not able to replace null because I need keep it for other logics. It made me got result like this:

Index	a1	b1	a2	b2	a3	b3	Result
1	1	3	2	1	3	3	TRUE
2	2	4	3	5	null	null	null
3	6	1	2	7	null	null	TRUE
4	3	null	null	null	null	null	null

What I want is ignore those null value during comparing. For example:

1. row 2,  for 'a1>b1 or a2>2b' return FALSE, ignore a3 compare with b3. 
2. row 3,  for 'a1>b1 or a2>2b' return TRUE, ignore a3 compare with b3. 
3. row 4, I'm not sure does my data source will have this situation but I'm concern about what if only a or b is null but another have value? for row 4, I hope when a1=3, b1=null when compare a1>b1 return TRUE, ignore a2 compare with b2 and a3 compare with b3

I hope the final result is:

Index	a1	b1	a2	b2	a3	b3	Result
1	1	3	2	1	3	3	TRUE
2	2	4	3	5	null	null	FALSE
3	6	1	2	7	null	null	TRUE
4	3	null	null	null	null	null	TRUE

Is there any easy way to implement the logic?

Thanks!

[edhans · Accepted solution]

@Anonymous take a look at this code. I cannot get the +0, or *1, or any other Excel trick as @lbendlin has suggested to work, so I may be doing it wrong.

I took a different approach. This is the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNjIDZCYhsrxepEg0VA2AQqagrEeaU5OTAKpAYkbgbVaQ5Vj64Gph9ZHCsVGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, a1 = _t, b1 = _t, a2 = _t, b2 = _t, a3 = _t, b3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"a1", Int64.Type}, {"b1", Int64.Type}, {"a2", Int64.Type}, {"b2", Int64.Type}, {"a3", Int64.Type}, {"b3", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"a1", "b1", "a2", "b2", "a3", "b3"}),
    #"Added Result" = Table.AddColumn(#"Replaced Value", "Result", each [a1]>[b1] or [a2]>[b2] or [a3]>[b3]),
    #"Final Result" = 
        Table.AddColumn(
            #"Changed Type",
            "Final Result",
            each
                let
                    varCurrentIndex = [Index]
                in
                #"Added Result"{[Index = varCurrentIndex]}[Result],
            type logical
        )
in
    #"Final Result"

After the #Changed Type step, it does this:

1. Replaces null with zero (bear with me, this is temporary)
2. Adds your column with your comparisons returning true/false
3. The final result refers back to #"Changed Step" but using the [Index Column] gets the true/false value from the #"Added Result" step. Since #"Changed Step" has your nulls, your results should be ok.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[ziying35 · Accepted solution]

@Anonymous 

The code can be written like this, the code is dynamic, no matter how many columns of your data can be, it just judge the logic of the time to convert the null value to 0 value, does not affect your later references to the original data column

Table.AddColumn(Source,"Result",(r)=>List.AnyTrue(List.Transform(List.Split(List.Transform(List.Skip(Record.ToList(r)),(y)=>if y = null then 0 else y),2),(x)=>x{0}>x{1})))

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you can copy paste this function into a new column

let 
            CleanRow = Record.FromTable(Table.ReplaceValue( Record.ToTable(_),null,0,Replacer.ReplaceValue,{"Value"})),
            Result = CleanRow[a1]>CleanRow[b1] or CleanRow[a2]>CleanRow[b2] or CleanRow[a3]>CleanRow[b3]
        in 
            Result

This cleans the null with 0 and then make the comparison as you described

Here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNjIDZCYhsrxepEg0VA2AQqagrEeaU5OTAKpAYkbgbVaQ5Vj64Gph9ZHCsVGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, a1 = _t, b1 = _t, a2 = _t, b2 = _t, a3 = _t, b3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"a1", Int64.Type}, {"b1", Int64.Type}, {"a2", Int64.Type}, {"b2", Int64.Type}, {"a3", Int64.Type}, {"b3", Int64.Type}}),
    AddColum = Table.AddColumn(#"Changed Type", "Result", each
        let 
            CleanRow = Record.FromTable(Table.ReplaceValue( Record.ToTable(_),null,0,Replacer.ReplaceValue,{"Value"})),
            Result = CleanRow[a1]>CleanRow[b1] or CleanRow[a2]>CleanRow[b2] or CleanRow[a3]>CleanRow[b3]
        in 
            Result)
in
    AddColum

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ziying35 · Accepted solution]

@Anonymous 

The code can be written like this, the code is dynamic, no matter how many columns of your data can be, it just judge the logic of the time to convert the null value to 0 value, does not affect your later references to the original data column

Table.AddColumn(Source,"Result",(r)=>List.AnyTrue(List.Transform(List.Split(List.Transform(List.Skip(Record.ToList(r)),(y)=>if y = null then 0 else y),2),(x)=>x{0}>x{1})))

[edhans · Accepted solution]

@Anonymous take a look at this code. I cannot get the +0, or *1, or any other Excel trick as @lbendlin has suggested to work, so I may be doing it wrong.

I took a different approach. This is the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNjIDZCYhsrxepEg0VA2AQqagrEeaU5OTAKpAYkbgbVaQ5Vj64Gph9ZHCsVGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, a1 = _t, b1 = _t, a2 = _t, b2 = _t, a3 = _t, b3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"a1", Int64.Type}, {"b1", Int64.Type}, {"a2", Int64.Type}, {"b2", Int64.Type}, {"a3", Int64.Type}, {"b3", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"a1", "b1", "a2", "b2", "a3", "b3"}),
    #"Added Result" = Table.AddColumn(#"Replaced Value", "Result", each [a1]>[b1] or [a2]>[b2] or [a3]>[b3]),
    #"Final Result" = 
        Table.AddColumn(
            #"Changed Type",
            "Final Result",
            each
                let
                    varCurrentIndex = [Index]
                in
                #"Added Result"{[Index = varCurrentIndex]}[Result],
            type logical
        )
in
    #"Final Result"

After the #Changed Type step, it does this:

1. Replaces null with zero (bear with me, this is temporary)
2. Adds your column with your comparisons returning true/false
3. The final result refers back to #"Changed Step" but using the [Index Column] gets the true/false value from the #"Added Result" step. Since #"Changed Step" has your nulls, your results should be ok.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[lbendlin]

Add zero to each value

Table.AddColumn(#"Changed Type", "Result", each [a1]+0>[b1]+0 or [a2]+0>[b2]+0 or [a3]+0>[b3]+0)