Skip to main content

Notifications

Power Automate - General Discussion
Unanswered

Distinct Values From SP List

(0) ShareShare
ReportReport
Posted on by 42

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:
  • takolota1 Profile Picture
    takolota1 4,777 on at
    Re: Distinct Values From SP List

    @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

  • hjaf Profile Picture
    hjaf 486 on at
    Re: Distinct Values From SP List

    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? 

  • automaton Profile Picture
    automaton 69 on at
    Re: Distinct Values From SP List

    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.

     

  • BARD Profile Picture
    BARD 40 on at
    Re: Distinct Values From SP List

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

  • hjaf Profile Picture
    hjaf 486 on at
    Re: Distinct Values From SP List

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

  • automaton Profile Picture
    automaton 69 on at
    Re: Distinct Values From SP List

    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

  • v-micsh-msft Profile Picture
    v-micsh-msft on at
    Re: Distinct Values From SP List

    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

  • ChrisCC Profile Picture
    ChrisCC 42 on at
    Re: Distinct Values From SP List

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

  • efialtes Profile Picture
    efialtes 350 on at
    Re: Distinct Values From SP List

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,422

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,711

Leaderboard