Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ImkeF
Super User
Super User

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

In Part 1 of this little series I described the core-Flow on how to automatically validate E-mail attachments with Flow and Power BI. It automatically sends an e-mail to a business partner who sent an attachment, that didn't meet the agreed specifications:

 

Automatically validate e-mail attachments - Part1Automatically validate e-mail attachments - Part1

 

But before going live with this Flow, you should consider the following aspects:

 

Caveats

 

Refresh limitations in Power BI Service

 

If you run on a Power BI Pro-license, the refresh can only be triggered 8 times a day (and 48 times with Premium). So it might not be a good idea to use this Flow for multiple senders or events.

 

The "When a data driven alert is triggered"-step will be ignored

 

Yes: That steps returns true if the last refresh of the dataset has triggered an alert and false if not. BUT: You need an action to check that value and determine what shall be done in each case. A condition will do that:

 

Condition to check the result of the data driven alertCondition to check the result of the data driven alert

 

The data driven alert might show the status from the previous refresh

 

After the refresh of the dataset has been triggered, the data refresh might take while. But the check for the data driven alert will start immediately after the previous step. So it will actually most likely be checked before the dataset has been refreshed. So I need a step that checks if the refresh has been finished. Therefore I can call the Power BI API again, using the endpoint that returns the refresh history of the dataset. But if this tells me that the refresh is not finished yet, I want to wait some minutes and ask again … until the status says "Completed".

 

Therefore I'm using a "Do until"-control:

 

Wait for the dataset to be refreshedWait for the dataset to be refreshed

 

The actions in this module will be executed until the condition (Status if the last refresh is equal to "Completed") is matched. To fetch the value for the status from the output of the API call, I have to navigate to it according to the structure of the returned JSON:

 

Refresh History OutputRefresh History Output

 

In Flow, the square brackets are used to select fields from a record as well as positions within an array, to these commands select the Body from the Output, then grab the value field from the outer record, select the first (and only) item from the list and then select the status field from the inner record:

 

actionOutputs('Returns_the_refresh_history_of_the_specified_dataset_from_specified_workspace')['body']['value'][0]['status']

(So Flow uses a zero-based-indexing as well).

 

I don't want to remove the checked files from my folder manually

 

So I have to add some actions that move the file(s) for me:

 

Move files from Transit-folder to destination-folderMove files from Transit-folder to destination-folder

 

So first I have to check which files are in the folder ("List files in folder") and for each item found ("value"), I move them to my destination folder. (If there is a risk, that you receive 2 or more files with the same name in one email, you might want to replace the "Received Time" by the actual time).

 

Now the Flow is ready to flow and I hope you enjoyed this sample of how Flow and Power BI together can automate your business processes.

 

The full Flow in all its gloryThe full Flow in all its glory

 

 

Enjoy & stay queryious 😉

 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors