Calculated Column for Power Query Refresh for Power Automate alert

[Csalinas144]

List of Queries with an added (refresh date column)

Hey Team, 

I need to create a 2 custom column which assesses if these queries failed to refresh or not. They are scheduled to refresh every 12 hours. first refresh at 4AM then 4PM and repeat.. Here I have made my own refresh table. 

I need to know if the power query failed with a custom column. 

I then am going to use a card visual to set up an alert. if "failed" then send an email via power automate.

Thank You, 

If my question does not make any sense then I am ok with rephrasing it... or perhaps yall have an even better idea to identify if a power query has failed to refresh. 

@amitchandak @AllisonKennedy 

@mahoneypat @Greg_Deckler 

@edhans @KNP @AlexisOlson @mwegener @Kiran1234 

[v-henryk-mstf · Accepted solution]

Hi @Csalinas144 ,

It seems that the Power Query does not trigger the sending of the corresponding email when the refresh fails. Why not publish it to service and then configure a scheduled refresh through the gateway. You can check the status of the refresh in the history and set up an email alert to the specified user when the refresh fails.

If the problem is still not resolved, please point it out. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Csalinas144]

Good morning  @v-henryk-mstf ,
Let me be clear of what I have done this far and what I am trying to do. 

Response Question: If I publish these 11 queries to the service, then that will not be evaluating the automatic refresh times which I have set up inside the excel power queries themselves. But, rather It will be evaluating the refresh time the Excel Queries are refreshing to the Service. Do you agree? Or have I misunderstood your solution?
Some Background of what I have done this far.
1.) I have created 11 queries through excel power query, because I wanted to divide up the data processing efforts to transform a total of 500,000 records. So instead of 1/2 a million records to process at one time. I am processing roughly 45K records
2.) Next Step: I am appending those 11 queries into a .pbix document and creating my visuals. 
After that I am bringing these queries into my .pbix file and appending the files and adding one more transform and several other calculated columns.
My Intention: To evaluate the refresh time for the Excel Power Queries by leveraging some Microsoft Esque solution of which I have not identified. 
I have identifed several Refresh Solutions from a Guy In a Cube such as
OneDrive/Share Point
Schedule Refresh
On Demand
Power Shell (On Demand)
and now I am reviewing Microsoft Flow. 

Thank you for your effort in trying to help me come up with a solution, 

Chris

[AllisonKennedy]

@Csalinas144 

What part are you still struggling with?

You don't need an entire column for LastRefresh Date/Time - can do this with a custom query, but if your refresh schedule table is small it may work ok. https://www.excelguru.ca/blog/2016/06/08/display-last-refreshed-date-in-power-bi/ 

You'll need to use DAX to create the Failed/Refreshed column, as if the refresh fails it will not update the Power Query Custom column. I think a simple IF statement should work here, in combo with the TODAY() or https://dax.guide/utcnow/ functions and your calculated columns. Let us know which parts of your question are solved and what you're still struggling with and we can guide with more detailed help.

What's wrong with the built in refresh failure notification emails? https://powerbi.microsoft.com/en-za/blog/sending-refresh-notifications-to-others/ 

[Csalinas144]

Good Morning @AllisonKennedy , 

I am going to try your solution later today after lunch.. I will keep you updated. I appreciate your time and effort to help me on this. 

I will keep you updated on my goal. 

Thank You, 

Chris