Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
Solved! Go to 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
Proud to be a Datanaut!
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
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
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"
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
Proud to be a Datanaut!
it is working, thank you!! 🙂
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
Hi @Alexa1104 ,
You need to put the name of your previous query step here:
Pete
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
Proud to be a Datanaut!
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
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)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |