The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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.
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.
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".
if that kind of operations are possible in PBI, it would really helped me
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.
Truly helped me ManuelBolz, Thank you
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
30 | |
23 | |
19 | |
18 | |
13 |