Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Alexa1104
Frequent Visitor

check if text values from a column are present in another column and return True/False

Hello,

I am struggling with a difficult situation (at least for me) and I have been searching for this in various topics but couldn't manage to find a solution. I will explain the topic below:

I have 2 text columns with a lot of values (some of them are present only in a column, some in the other column and some in both). 

A sample picture is below:

Alexa1104_0-1746771999600.png

So basically I need to know which req have ONLY released TCs...(so they are not present AT ALL in the column Req with NOT released TCs).

So I should eventually have a column with TRUE for req which are present only in the first column (the above case of 0GHIV-10025). For the rest of them it should be FALSE.

I further say that it is not an option to split the column in 2 (1 column with 0GHIV and another one with the number) because I have a lot of values and the nr may repeat itself but with a different starting ID (instead of 0GHIV it is something else) so that's why I need to keep the column values as text. 

I would much appreciate if someone could help me..or at least drive me to an idea. 

Thank you!

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @Alexa1104 ,

 

Try this in a new custom column:

not List.Contains(Source[Req with NOT Released TCs], [Req with Released TCs])

 

Gives this output:

BA_Pete_0-1746787744810.png

 

Pastable example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjQyVtJRUorViVZKSU1D4gFpqDSIl56RiSoHFQDxsrJzLC0tkeRy8/INDQ3BPCgTJBcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Req with Released TCs" = _t, #"Req with NOT Released TCs" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each not List.Contains(Source[Req with NOT Released TCs], [Req with Released TCs]))
in
    #"Added Custom"

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

14 REPLIES 14
v-sdhruv
Community Support
Community Support

Hi @Alexa1104 ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

v-sdhruv
Community Support
Community Support

Hi @Alexa1104 ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

Hello, yes thank you for everyone's support !! 

I am sorry I answered now, after a few days, but I did it as soon as I could. 

Again thank you 🙂 I appreciate the effort for my question

SundarRaj
Solution Supplier
Solution Supplier

Hi @Alexa1104 , source used is the one mentioned above, you could try this:

SundarRaj_0-1746799977071.png
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Req with Released TCs", type text}, {"Req with NOT Released TCs", type text}}),
List = List.RemoveNulls ( #"Changed Type"[#"Req with NOT Released TCs"] ),
#"T/F" = Table.AddColumn ( #"Changed Type" , "T/F" , each List.Contains ( List, _[#"Req with Released TCs"] ) )
in
#"T/F"

 

 

Sundar Rajagopalan
BA_Pete
Super User
Super User

Hi @Alexa1104 ,

 

Try this in a new custom column:

not List.Contains(Source[Req with NOT Released TCs], [Req with Released TCs])

 

Gives this output:

BA_Pete_0-1746787744810.png

 

Pastable example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNjQyVtJRUorViVZKSU1D4gFpqDSIl56RiSoHFQDxsrJzLC0tkeRy8/INDQ3BPCgTJBcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Req with Released TCs" = _t, #"Req with NOT Released TCs" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each not List.Contains(Source[Req with NOT Released TCs], [Req with Released TCs]))
in
    #"Added Custom"

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




it is working, thank you!! 🙂

Hello @BA_Pete ,

Alexa1104_1-1746795396400.png

And the result is a column full of errors because of:

Alexa1104_2-1746795445824.png

Do you know how I could fix it ? 

Thank you in advance

Hi @Alexa1104 ,

 

You need to put the name of your previous query step here:

BA_Pete_0-1747028568178.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




I didn;t mention that my database is huge (a lot of columns), I have only posted here the 2 columns important in the topic..I need to keep them all for further analysis.

 

Sorry, looks like @slorin came back as I was typing up my answer.

Their solution will probably be faster than mine due to the List.Buffer so try that one first 👍

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




slorin
Super User
Super User

let
Prev_Step = Your_Source,
List_Buffer = List.Buffer(List.Distinct(Prev_Step[Req with NOT Released TCs])),
Test = Table.AddColumn(Prev_Step, "TRUE/FALSE", each not List.Contains(List_Buffer, [Req with Released TCs]))
in
Test

 Stéphane

Hello again @slorin ,

Unfortunately I get this error.. Do you know how I could solve it? 

Thank you!

Alexa1104_0-1746790990835.png

 

slorin
Super User
Super User

Hi @Alexa1104 

 

= List.Difference(
List.Distinct(Your_Source[Req with Released TCs]),
List.Distinct(Your_Source[Req with NOT Released TCs])
)

 Stéphane

Hello @slorin ,

The result of the formula is not the expected one..(TRUE/FALSE)

But instead it is a long concatenated list of values (on each row)

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.