Send email in Power BI based on a Power automate flow

[Redworf]

I have created a power automate flow within Power BI and I can’t figure out how to send an email to the competition participants.

The pbix file contains fictional data.
But the file does not include the flow.

Date: Completion dates
City: Fictional city name
Score: Number
Email: Fictional email of the competition participants


Basic plot:
User selects from one or more slicers a table I filtered and when user presses the flow button an email sends a csv file  (with the values from the table) to everyone that is in the table.

If there is only one person in the table on one mail sends to that person.
If there are multiple persons in the table everybody gets a mail with a csv with all data from the table.

Example
When user press flow button. Truman and Gianna receive an email with csv data based the data from the table.
(if user wants to send a separate email to each, user just selects one name)

The competition participants have no access to the Power BI report and therefore must have data sent to them.

In my current flow I have created a csv fil and save it in my SharePoint site.
(The competition participants have no access to the SharePoint site)
The user that’s push the flow button has access to SharePoint and could easily choose the csv file from SharePoint site and send to whoever they want. But what’s the fun in that.

But I can’t figure out how to get the flow to send out the email.

If I use apply to each, I’m risking sending 6 emails to Truman and 5 emails to Gianna.
They should only receive one email each.


I’ve tried various things like build an array of data (competition participants email) from the table.
But some reason the array doesn’t fill all rows from the table. The array just fills the first 1000 rows, but the filtered table have 25000 rows.

(This is a similar post on this form that addresses almost the same issue that I have.
And I’ve searched the cyberspace for answers. And many of the tips and tricks refer to
Step1: Use select action to get a list of emails
Step2: Use Union to get unique email addresses, as :union(outputs('XXX'),outputs('XXX'))


So I just end up with one email address instead of multiple email of the competition participants.


the last step gives me an error

Is there anyone out there in cyberspace who has any idea how this can be solved?
How to build an array based on thousands rows and filter out so there only unique emails adresses.


If this forum is not the right place for this question so please direct me to the right place.

Thanks

[lbendlin · Accepted solution]

Trigger a cloud flow from any Power BI report - Power Automate | Microsoft Learn

Clearly states the 1000 item limit.

[Redworf]

After some more digging I realised that the Power Automate visual is limited to process a maximum of 1000 records. Somehow, I totally missed this information.

Thanks Ibendelin for the information site on learn.microsoft.com.

Now I'm going to crawl back under my rock and wait until they drop the 1000 row limitation.
I guess I can change my post to “solved”.