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

[Alexa1104]

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:

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!

[v-sdhruv]

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]

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

[Alexa1104]

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]

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

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"

[BA_Pete · Accepted solution]

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:

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

[Alexa1104]

it is working, thank you!! 🙂

[Alexa1104]

Hello @BA_Pete ,

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

Do you know how I could fix it ? 

Thank you in advance

[BA_Pete]

Hi @Alexa1104 ,

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

Pete

[Alexa1104]

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.

[BA_Pete]

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

[slorin]

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

[Alexa1104]

Hello again @slorin ,

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

Thank you!

[slorin]

Hi @Alexa1104 

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

 Stéphane

[Alexa1104]

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)