Unanswered

Distinct Values From SP List

(0) Share

Report

Posted on by ChrisCC

Is it possible to generate an array with the Compose function based on a Sharepoint list, but only distinct values? I want the following code to run:

MyArray[item];

for each (item in MyArray){
 if (MyArray.contains(item)){ // note1
 do nothing;
 else
 (MyArray.push(item))
 }
}

I am getting hung up recreating the line note1 is on. I can't seem to use the contains condition on an array I am working on

I am using the compose feature to make the array which I think has to do with it...thanks for any help

Categories:

General topics

I have the same question (0)

All responses (9)

Answers (0)

efialtes 350 on at

Like (0)

Report

@ChrisCC
You can try with WDL union function (https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language)
According to the definition of this funtion, it should work even with one input parameter, so in theory you do not even need the foreach.

Also if you have the dictionary with all possible values in advance, you can try with WDL intersection function.

I am afraid I have never tried iany of these expressions before

Please note some days ago Flow team has completed the rollout of a new feature, so that you can use expressions in action blocks, Compose workaround is not mandatory anymore
(https://emea.flow.microsoft.com/en-us/blog/use-expressions-in-actions/)

Was this reply helpful? Yes No
ChrisCC 42 on at

Like (1)

Report

Ended up solving this by just dumping the data to a SQL table

Was this reply helpful? Yes No
v-micsh-msft on at

Like (0)

Report

Hi @ChrisCC,

To filter array, Flow has an Action call Filter Array, which allows to apply fitler under each item within the array, take a look at the blog below:

Build more advanced flows than ever

Checking the Filter Arrarys part.

Post back if you have any further questions.

Regards,

Michael

Was this reply helpful? Yes No
automaton 69 on at

Like (0)

Report

I can see you found a workaround for this but if the need comes up again I solved it like this.

https://powerusers.microsoft.com/t5/Flow-Cookbook/How-to-get-unique-column-values-from-a-SharePoint-List-in/m-p/365290#M278

Was this reply helpful? Yes No
hjaf 486 on at

Like (1)

Report

The post you linked to gets me a Access Denied error. any alternative? or can you reply the solution here?

Was this reply helpful? Yes No
BARD 40 on at

Like (0)

Report

That link shows Access Denied @automaton , Can you post your solution here?

Was this reply helpful? Yes No
automaton 69 on at

Like (2)

Report
Hi @BARD @hjaf, I'm not sure why the links dead, I can't access it either. They seem to have removed the previously approved post from that section in their forum.

Fortunately I had a copy. Hope it helps.

How to get unique column entries from a SharePoint List in Microsoft Flow? (using Select rather than Apply_to_each.)
One of the questions I see coming up quite a bit is how to extract a list of distinct (unique) values from a SharePoint List column. This can be achieved using Select rather than the much more resource intensive loop approach via the Apply_to_each action, which I so often see suggested in forums.

Where you have 1000s of rows to retrieve and iterate through, it is easier and less intensive to get a column value from each row by using the Select statement to produce a basic array rather than standard JSON pairs.
A simple example of this is shown here:

Get the list of items from the list.
Select the values from the column you are interested in by turning off the standard map on the select statement and just inserting the value of the column you are interested in.
Apply a union expression, with both operators being the output of the Select action, to make the list distinct. (this step is common across both methods).

Select and union to form a distinct list of values.

Using this approach on large lists over the apply_to_each loop approach will make the flow run ridiculously quick in comparison.

Was this reply helpful? Yes No
hjaf 486 on at

Like (0)

Report

Yes, i have thought about this too along with using a http response function. But the problem in my case (PowerApps) is that if the list is large i need to enable pagination with a 20000 item threshold. And if the process takes more than 2 minutes it will time out 😕

I have been testing with running several parallel rows in table actions from a large excel table, each of which taking its batch of 2000 items(all with top 2000 and skip 2000, 4000, 6000 etc.), then using union on all of the get items.
I was thinking it would not matter as I thought the background processes of PowerAutomate would optimise the flow with some kind of of parallelism. using a threshold of 8000 items in one action takes longer than several requests running at the same time.
workaround
I guess this "hack" is not something one should rely on, as I imagine PowerAutomate and sharepoint will optimise to reduce its loads. And it is quite possible a similar workaround is not possible with the Get Items from a sharepoint list. I have experienced errors dues to to high request frequencies before, and although this workaround will look like fewer requests because of $Batch / pagination, the throttling is perhaps considering the load of each request as well?

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (0)

Report

@ChrisCC @hjaf @automaton @BARD @v-micsh-msft

This template will quickly get you distinct records based on selected columns: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Get-Unique-Records/td-p/2191533

Was this reply helpful? Yes No