cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lowdmt
Regular Visitor

Filter array multiple values from a string?

Help please.. (noob in training).

 

I have a large excel table and I want to filter the table and retrieve specific rows to upload into a SharePoint list. The large excel table (12,000+ rows) has a column with unique values (column is called Items).

 

I have another Excel table that is manually updated by users that are interested in retrieving details from the larger list, they update the 2nd Excel table with the "items" they want to query. For example a user wants to query "Items" 50, 120, 506, 1205, 7890 from the large list so they enter those "items" in the smaller excel. I want my flow to read the users requirements in the smaller excel and then filter the large excel file by those "items", then retrieve the data from the row and then upload to a Sharepoint list. 

 

So far I have used 'List rows present' in the small user defined list and created a string example "50, 120, 506, 1205, 7890". I've then set that string as a variable thinking I could use it in a 'filter array'? 

 

With the filter array I've learnt that I can filter the large excel list by 'List rows present in table" then Filter array by 'value' contains 7890 but I want to filter by more than just 1 unique value..

 

@contains(item()?['Item'], '7890')

 

 

Instead of '7890' I would like the filter to use the small excel variable '50', '120', '506', '1205', '7890'.

 

Beginning to think I'm going around this the wrong way?! 

 

How do you filter multiple unique values? 

 

Any help appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
ekarim2020
Community Champion
Community Champion

You could try the contains function with the filter array:

ekarim2020_0-1633985241288.png

As the data set is so large, you will need to change some settings in the List rows present in a table action or only the first 256 or so will be read:

2021-10-11_21-48-25.jpg

In the List rows present in a table, click the three dots, then select Settings:

2021-10-11_21-53-15.jpg

Set Pagination to On, and set the threshold (limit) to your requirements:

2021-10-11_22-04-12.jpg

Save your flow and test it. You should see that more than 256 rows of data have been retrieved from Excel.

This is the output of my demo flow:

2021-10-11_22-15-51.jpg
Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

2 REPLIES 2
ekarim2020
Community Champion
Community Champion

You could try the contains function with the filter array:

ekarim2020_0-1633985241288.png

As the data set is so large, you will need to change some settings in the List rows present in a table action or only the first 256 or so will be read:

2021-10-11_21-48-25.jpg

In the List rows present in a table, click the three dots, then select Settings:

2021-10-11_21-53-15.jpg

Set Pagination to On, and set the threshold (limit) to your requirements:

2021-10-11_22-04-12.jpg

Save your flow and test it. You should see that more than 256 rows of data have been retrieved from Excel.

This is the output of my demo flow:

2021-10-11_22-15-51.jpg
Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

lowdmt
Regular Visitor

 It took me a little while to create the filter array automatically and get it to work. I was getting zero results back on my filter and couldn't figure out why! (not being able to see the array once it was created doesn't help). I used compose to actually see what was happening with the array and realised that my mistake (my serial numbers were stored with 'single quotes' which ended up being double quotes by the time the array was built). 

 

Thanks for the direction @ekarim2020 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,842)