Forum Discussion
Return value if cell contains text in another column
Hi Experts,
How to return value if cell contains any text in another column in the same table ?
There are duplicates value in Group 1 & Group 2. Do not suggest split into two table and merge queries.
If Group 2 contain any value in Group 1, return value as "Yes" otherwise blank.
| Group 1 | Group 2 | Return value |
| AAAA | BBBB | Yes |
| BBBB | EEEE | |
| CCCC | DDDD | Yes |
| DDDD | ||
| AAAA | BBBB | Yes |
Thank you!
Hi CoronaAustralis ,
Here two solutions.
In Power Query:Here the code:
= Table.AddColumn(#"Changed Type", "Custom", each if List.Contains(#"Changed Type"[Group 1] , [Group 2]) = true then "Yes" else null)
For reference:
In DAX:Here the code:
Return Value = IF ( CONTAINS ( Table9, Table9[Group 1], Table9[Group 2] ), "Yes", BLANK() )
For reference:
Hope this helps!
/Tom
6 Replies
Hi CoronaAustralis ,
Here two solutions.
In Power Query:Here the code:
= Table.AddColumn(#"Changed Type", "Custom", each if List.Contains(#"Changed Type"[Group 1] , [Group 2]) = true then "Yes" else null)
For reference:
In DAX:Here the code:
Return Value = IF ( CONTAINS ( Table9, Table9[Group 1], Table9[Group 2] ), "Yes", BLANK() )
For reference:
Hope this helps!
/Tom
I have this error message in power query "Expression.Error: A cyclic reference was encountered during evaluation"
You can do a self-merge matching Group 2 with Group 1 without splitting the table and then check which rows have matches.
Here's a sample query you can paste into the Advanced Editor of a new blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCJR0lJyBQitWJhjB0lFyBAMx3BgIg3wUIwHwwQ0dJAcxB0RwLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Group 1" = _t, #"Group 2" = _t]), #"Merged Queries" = Table.NestedJoin(Source, {"Group 2"}, Source, {"Group 1"}, "Merge", JoinKind.LeftOuter), #"Counted Rows" = Table.TransformColumns(#"Merged Queries", {{"Merge", each if Table.RowCount(_) > 0 then "Yes" else null, type text}}) in #"Counted Rows"I am able to fix the issues however it took quite long time to run a query of 8000 line items in excel.
Hi CoronaAustralis ,
Is this any good...
let
fnContains_duplicates = (_list as list, _value as text) as text => if List.ContainsAny(_list,{_value}) then "Yes" else "No",
Source = #"Your table here",
column_asList = Source[Group 1],
#"Added Custom" = Table.AddColumn(Source, "Duplicate", each fnContains_duplicates(column_asList,[Group 2]))
in
#"Added Custom"Edit: Sorry, hadn't previously read to the bottom of the page so I've just seen that this is similar to a tackytechtom solution, don't know if this variation would resolve the cyclic reference?
