Part 2: Automatically validate E-mail attachments with Flow 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:

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:

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:

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:

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:

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.

Want more?

Or are you even in the Flow-fever already and think: “Well, I’d actually like to attach a table with all faulty rows back to the sender, so that he can check more easily what went wrong.” ? Then make sure to tune in for the next article where I will cover exactly that

Enjoy and stay queryious 😉