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. id maincode values id code newcolumn 123456 1 aaa       123456 2 bbb       123...
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.

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

      • ManuelBolz's avatar
        ManuelBolz
        Responsive Resident
        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.