cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ImkeF
Super User II
Super User II

Part 1: Automatically validate email attachments with a flow in Power Automate and Power BI

Receiving files from business partners that don't match the agreed requirements/formats causes all sorts of problems in daily business. I found it particularly disturbing during month-end closing when time is really tight: You have a strict rule in which order each process has to run and there are many dependencies between them. So when then one import doesn't work, many other processes will come to a halt as well. Fortunately, today there is a simple remedy for it: Automatically validate E-mail attachments with Flow and Power BI

 

Process Automation with Power Automate and Power BI

 

You can create a flow that "listens" for incoming emails in a mailbox that match certain criteria and contain attachments. The flow can then extract these attachments and save them to an online-folder. After that, Power Automate triggers a refresh of a Power BI dataset, that imports these attachments and checks for the data quality-criteria that you have defined. Then you create measures for the data quality that trigger data driven alerts from Power BI service. Flow then listens for these alerts and sends an email back to the sender, requesting for a corrected file.

 

This not just saves crucial time, but also your nerves (and those of your team-mates).

 

The Details

 

Trigger

 

Start a flow with a trigger that searches your mailbox for ingoing messages. There are different triggers available (also shared mailboxes or Gmail). Specify the criteria for which the attachments shall be checked to prevent unwanted emails being sent out to senders who are sending different attachments.

 

0_TriggerEmail.jpg

 

Actions

After this trigger, the first action has to be defined. The "Get email" action will just fetch the content of the email that triggered the flow. Only thing to do in this step is to choose the "Message Id" in the specified field and select to include attachments. 

 

1_GetEmail.jpg

The next action will be to extract the attachment(s) and save them in a dedicated folder. Therefore I'm using a "Apply to each"-action. I select "Attachments" as the output from the previous step and define the action that shall be performed on them:

 

Apply to each attachmentApply to each attachment

 

"Create file" saves each file to the folder path I'm defining in the first field. I select "Attachments Name" for the the File Name and the "Attachment Content" for File Content.

 

Create FileCreate File

 

Power BI

 

Now that the files are stored, I import them to Power BI and create one or more queries that check the critical elements. These could be:

  • column headers match a defined list
  • dates are valid
  • only agreed product codes
  • …. basically everything

In this example I just check for valid dates: Convert the column "Dates" to dates, check it and keep rows with errors.

 

In Power BI: Select rows with errorsIn Power BI: Select rows with errors

 

I load the table to the datamodel and create a measure that counts the rows.

 

Measure that indicates errorsMeasure that indicates errors

 

 

This measure is placed in a card visual (as this is one of the few visuals, that is suitable to trigger a data driven alert in the service). This will be published to the Power BI service.

 

Power BI Service

 

In the service, I pin the card visual to a dashboard. From there I can configure a data driven alert: 

 

Data driven alert in Power BIData driven alert in Power BI

 

Back to Flow

Back in Flow, I create a trigger to refresh the dataset that we just created in the Power BI service. This is possible through a custom connector (Chris Webb describes here how to create one using an open API-definition. But if you're not so familiar with setting up custom connectors yet, I highly recommend Ida Bergums excellent walkthrough of how to do it manually. )

 

Trigger refresh of Power BI data set from FlowTrigger refresh of Power BI data set from Flow

 

By navigating to the dataset, I get the URL that displays the Id/Key for this task:

 

Fetch group- and dataset-IDFetch group- and dataset-ID

 

After the dataset is refreshed, the Flow now gets a trigger that listens to data driven alert from Power BI. I have to select in the "Alert Id" from Power BI in there:

 

Data driven alert trigger from Power BIData driven alert trigger from Power BI

In the last action I define the details of the email that shall be sent to the sender of the faulty attachments:

 

Return E-mailReturn E-mail

Looks very straightforward, doesn't it?

 

Caveats?

 

Think again and check Part 2 of this series where I show the major error in this Flow and the missing elements that make this Flow really flow in a corporate environment.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

0 REPLIES 0

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors