Forum Discussion

Mederic's avatar
Mederic
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

AB
20
22
10
02
00
11
21
30
00
14

 

Need Help
  • m_dekorte's avatar
    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

     

     

     

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    wrong order of checks.

     

     

    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 

if [A]>0 and [B]>0 then 1 else 
if [A]=0 and [B]=0 then 3 else 
2
)
in
    #"Added Custom"

     

    • Mederic's avatar
      Mederic
      Post Patron
      1 year ago

      Hello lbendlin 
      Thank you for your reply,
      Your result is in a single column whereas I wanted it in 3 additional columns.

      Best regards

      • m_dekorte's avatar
        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