Create a function to get value from other column by condition

[ZBO125]

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
123456	3	ccc
123456	4	ddd
123456	5	eee
123456	6	fff
123456	7	ggg
123456	8	hhh
123456	9	iii
123456	10	jjj
789			123456
789			123456
789			123456
789			123456
789			123456
789			123456	5
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.

[ManuelBolz]

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]

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]

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

[ManuelBolz · Accepted solution]

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.

[ZBO125]

Truly helped me ManuelBolz, Thank you