Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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:
In DAX:
Here the code:
Return Value = IF ( CONTAINS ( Table9, Table9[Group 1], Table9[Group 2] ), "Yes", BLANK() )
For reference:
Hope this helps!
/Tom
https://www.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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?
@CoronaAustralis, Could you post a screenshot of the error message and your code?
/Tom
https://www.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
I am able to fix the issues however it took quite long time to run a query of 8000 line items in excel.
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"
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
https://www.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
I have this error message in power query "Expression.Error: A cyclic reference was encountered during evaluation"