Forum Discussion

Post Patron

1 year ago

Solved

If true then else

Hello everybody, I'm stuck on a problem that looks easy but I can't find it. I'm posting a screenshot with the explanations Thank you in advance Best regards A B 2 0 2 2 1 0 0...

Need Help

m_dekorte

1 year ago

Hi Mederic,

You can use lbendlin's solution and create a record that can be expanded, to illustrate:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTJQitWBsIzALEO4mAFczAAuBpI1hOuAsIxRdCDUmSjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each 
        [Both = null, Not Both = null, Clean = null] &
        (
            if [A]>0 and [B]>0 then [Both = 1] else 
            if [A]=0 and [B]=0 then [Clean = 3] else 
            [Not Both = 2]
        ), type [Both = Int64.Type, Not Both = Int64.Type, Clean = Int64.Type]
)
in
    #"Added Custom"

once expanded, it will yield this result

m_dekorte

Resident Rockstar

1 year ago

Hi Mederic,

You can use lbendlin's solution and create a record that can be expanded, to illustrate:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTJQitWBsIzALEO4mAFczAAuBpI1hOuAsIxRdCDUmSjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each 
        [Both = null, Not Both = null, Clean = null] &
        (
            if [A]>0 and [B]>0 then [Both = 1] else 
            if [A]=0 and [B]=0 then [Clean = 3] else 
            [Not Both = 2]
        ), type [Both = Int64.Type, Not Both = Int64.Type, Clean = Int64.Type]
)
in
    #"Added Custom"

once expanded, it will yield this result