I recently had a requirement from a customer where they wanted a list of all Customers and the Primary Contact to be emailed to them every day.

The reason for this to be emailed daily is to ensure that when calling the customer, they know whom to speak to.

This got me thinking and I could use Power Automate to achieve this task which I detail in this blog post below.

Getting the DAX Query without having to write it by hand

As I have previously explained in the past it is far easier to let Power BI Desktop write the DAX query instead of me having to type it by hand.

I connected to my dataset in the Power BI Service, and then created the table below.

Create table

I then and opened Performance Analyzer which would allow me to get the DAX Query for the above table.

I then clicked on Start recording and Refresh visuals, after it completed, I could then see the option to copy the DAX query.

I clicked on Copy query, which allowed me to then get the DAX query as shown below.

// DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZE('Customer', 'Customer'[Customer], 'Customer'[Primary Contact])
  VAR __DS0PrimaryWindowed = 
    TOPN(501, __DS0Core, 'Customer'[Customer], 1, 'Customer'[Primary Contact], 1)
EVALUATE
  __DS0PrimaryWindowed
ORDER BY
  'Customer'[Customer], 'Customer'[Primary Contact]

I then copied and saved this query in notepad

Creating the Power Automate flow to send the table details via email

The final part for me was to now create a Power Automate flow in which I will create the schedule, put in the DAX query, create the CSV file and then send it out via email.

I created a new Scheduled cloud flow

I then gave it meaningful name and put in how often I wanted this flow to run and clicked on Create

Next step I needed to create a new step to run the DAX query, I selected the Run a query against a dataset

Then I selected my workspace, dataset and put in the DAX query I previously had created from Power BI Desktop.

Next, I created the step to create the CSV file as shown below.

In this step I then selected the First table rows from the dynamic content. This is the actual query results.

The final step was for me to email out the CSV file to the recipients. I did this by selecting the Send an email (V2)

I then put in the recipients I want to send it to, the subject, the body.

And then I had to put in the Attachments Name – 1 and the Attachments Content – 1 which is highlighted below.

NOTE: You will see in the Attachments Name – 1 I had to put in the extension of CSV, this will ensure that the file will come through with the valid extension.

I then clicked Save.

I then renamed my steps to something more meaningful.

As shown below I then got the email with the attachment.

Summary

In this blog post I have shown you how I created a Power Automate flow and send it out via email to users.

I Hope that you found this useful. I certainly did as it allows me to extract data quickly and easily from my Power BI dataset and use it in a meaningful way.

For me it also means using the Power BI Dataset as a single source of truth for not only reports but also for data extraction.

Have an awesome week!