Trying to use Split and the In function

(1) Share

Report

Posted on by bobgodin

380

I'm trying to split a text field that has multiple values per row delimited by "; "

Intially I am creating a collection of distinct values but then I would like to Split those values so I only have 1 value per row and I can then check to see if a value from a different List is In this list

My code so far is: ClearCollect(colDistinctReads,Distinct(colMRs,auth));

so colDistinctReads is then the datasource for a HTMLText screen where I have the following code:

SortByColumns(Filter(colDistinctReads, Not(Value in colAuthsNew.auth) ),"Value")) - this is testing to see if the auth value in colDistinctReads is IN colAuthsNew.auth but I want it the other way around so that after the split it has seperated the auths into 1 value per row

any help greatly appreciated thank you :)

Categories:

Building Power Apps

All responses (5)

Answers (2)

Sort by

Verified answer

WarrenBelz 156,732 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi bobgodin

Assuming you want Distinct value of all Auth items after splitting - try this
ClearCollect( colDistinctReads, Distinct( Ungroup( AddColumns( colMRs, AuthVal, Split( Auth, ";" ) ), AuthVal ), Value ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee
bobgodin 380 on at

Like
a
(0)

Report
Copy link

Link copied!

Warren - thanks a lot for the solution to the Split issue I was having, however I am now finding it difficult to achieve the following:

I want to run a report that checks the auth values in colDistinctReads and list those that are Not IN the colAuthsNew collection

I have the following code:

SortByColumns(Filter(colDistinctReads, Not(Value in colAuthsNew.auth) ),"Value")) - this is testing to see if the auth value in colDistinctReads is IN colAuthsNew.auth but it doesn't seem to be working

I must be missing something here...

thanks
WarrenBelz 156,732 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi bobgodin

Apart from the extra bracket on the end, that filter will show all records not in the second collection - is this what you are trying to do ?
SortByColumns( Filter( colDistinctReads, Not(Value in colAuthsNew.auth) ), "Value" )
Suggested answer

bobgodin 380 on at

Like
a
(0)

Report
Copy link

Link copied!

I have solved the problem - I had to split the auth on "; " not on ";" the leading space was causing the issue!

thanks again Warren
Verified answer

WarrenBelz 156,732 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

OK - that makes sense - you would have retrieved values with a leading space.