Distinct Values From SP List

[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

[efialtes]

@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/)

[ChrisCC]

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

[v-micsh-msft]

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

[automaton]

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-...

[hjaf]

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

[BARD]

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

[automaton]

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:

1. Get the list of items from the list.
2. 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.
3. 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.

[hjaf]

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? 

[takolota]

@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