Forum Discussion

ZBO125's avatar
ZBO125
Frequent Visitor
2 years ago
Solved

Create a function to get value from other column by condition

Hello, i'm new in PBI and trying to create column by comparing values from other column.

idmaincodevaluesidcodenewcolumn
1234561aaa   
1234562bbb   
1234563ccc   
1234564ddd   
1234565eee   
1234566fff   
1234567ggg   
1234568hhh   
1234569iii   
12345610jjj   
789  123456  
789  123456  
789  123456  
789  123456  
789  123456  
789  1234565 
789  123456  
789  123456  
789  123456  
789  123456  


If in "maincode=code" then values in "newcolumn=eee" from "values". I tried many different ways. Tried DAX by measure, tried creating new calculated column and in Advanced editor, but i couldm't. Can someone tell how could i create the column, or give the correct direction how to do it. Thank you.

Need Help
  • ManuelBolz's avatar
    ManuelBolz
    2 years ago

    Okay ZBO125,

     

    i thing i get it:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zc65DYAwDIXhVVDqFCQkBGaJKHIf+w/Ag4JDcg/Fb8n6XNhaJuSk9Mw4E8g5hzmcbfyBEnnvaZxQCIFGhWKMNGqUUqLxKOdMo0GlFBoXVGulcUWtNRrFiNF7f6lZ1mvn9+3vUH/z0LYD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, maincode = _t, values = _t, id.1 = _t, code = _t]),
    Datatype = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"maincode", Int64.Type}, {"values", type text}, {"id.1", Int64.Type}, {"code", Int64.Type}, {"newcolumn", type text}}),
    tblMainCode = Source[[maincode], [values]],
    Type = Table.TransformColumnTypes(tblMainCode,{{"maincode", Int64.Type}}),
    Distinct = Table.Distinct(Type, {"maincode"}),
    Join = Table.NestedJoin(Datatype, {"code"}, Distinct, {"maincode"}, "Tabelle3", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Tabelle3", {"values"}, {"newvolumn"})
in
    Expand

     

    Best regards from Germany

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • ManuelBolz's avatar
    ManuelBolz
    Responsive Resident
    2 years ago

    Hello ZBO125,

    I would like to help, but I don't think I fully understand your logic yet.

    Here would be my first suggestion.

    if ([maincode] = [code]) and ([newcolumn] = "eee")
    then [values]
    else null

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zc47DoAgEEXRrRhqCxEQWIux4P/Z/wJ8JsYQk+kp7hRzppjzZHwXUh1sZRw55zCXN3atg+/Ie0+6QCEE0iWKMZKuUEqJ9KecM+kalVJIN6jWSrpFrTXS+YbRe/8faGOH1Xc+u6sp/rtu", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, maincode = _t, values = _t, id.1 = _t, code = _t, newcolumn = _t]),
    CustomColumnZOB125 = Table.AddColumn(Source, "ZOB125", each 
    if ([maincode] = [code]) and ([newcolumn] = "eee")
    then [values]
    else null),
    Datatype = Table.TransformColumnTypes(CustomColumnZOB125,{{"id", Int64.Type}, {"maincode", Int64.Type}, {"values", type text}, {"id.1", Int64.Type}, {"code", Int64.Type}, {"newcolumn", type text}})
in
    Datatype


    Best regards from Germany

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ZBO125's avatar
      ZBO125
      Frequent Visitor
      2 years ago

      Hello ManuelBolz,
      Sorry if i wrote the question was not fully understandable. I ment that i;m trying to make condition for if
      in column "code" value (for example in picture at the row 18 the value is equal to 5) is equal to in column "maincode" value 5, then in column "newcolumn" at row 18 must have the value from column "value" wich is "eee".




       

       

       

      • ZBO125's avatar
        ZBO125
        Frequent Visitor
        2 years ago

        if that kind of operations are possible in PBI, it would really helped me