Forum Discussion
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:
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!
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
14 Replies
slorinSuper 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
TestStéphane
- BA_Pete
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
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.
it is working, thank you!! 🙂
- 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" 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
