Return value if cell contains text in another column

[CoronaAustralis]

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!

[Alex_T]

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?

[tackytechtom]

@CoronaAustralis, Could you post a screenshot of the error message and your code?

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[CoronaAustralis]

I am able to fix the issues however it took quite long time to run a query of 8000 line items in excel. 

[AlexisOlson]

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"

[tackytechtom · Accepted solution]

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:

https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#4_Check_if_values_of_one_column_exi...

In DAX:

Here the code:

Return Value =  IF ( CONTAINS ( Table9, Table9[Group 1], Table9[Group 2] ), "Yes", BLANK() )

For reference:

https://www.tackytech.blog/how-to-crack-the-mystery-of-the-mighty-dax/#check-if-values-of-one-column...

Hope this helps!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[CoronaAustralis]

I have this error message in power query "Expression.Error: A cyclic reference was encountered during evaluation"