cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisCC
Advocate II
Advocate II

Distinct Values From SP List

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

8 REPLIES 8
efialtes
Kudo Kingpin
Kudo Kingpin

@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
Advocate II
Advocate II

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

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

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
automaton
Advocate III
Advocate III

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

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

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

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

 

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (3,097)