Dyota's blog

Scheduled Refresh through Power Automate

Scheduled refresh by another way

Power BI has a built in scheduled refresh feature. It can do these things:

But what if you don't want to use it? (Don't ask why - this is the premise of this article).

You can make up a Flow in Power Automate to hit the refresh for you! Power Automate has a built in trigger block that goes on a schedule. However, it can only be set to trigger every X intervals (e.g. every 1 day, every 3 hours, every week, etc). What if you need variable timings?

Not as easy as it sounds

I needed the Flow to run at 5 specific times per day. The general flow goes like this:

Run every hour
    |
Is it one of the hours of the day to trigger on?
    |
If yes, then trigger

Answer the question in the middle was way harder than I thought.

This is code block you need to get Power Automate to recognise a HHMM (24-hour) time format (e.g. 0900, 1300, etc). You have to parse out the hours and minutes, and join then up by adding them together. THIS IS NOT EVEN THE END OF IT because just because you "told" Power Automate to fire every hour, it might fire at 7:59 instead of 8:00, or at 8:01. When you parse for time, it will show 07;59 and not match the condition you set to run, and it will not run. So what to do? YOU HAVE TO ROUND IT. This is what int does.

formatNumber( 
    mul( 
        int(
            formatNumber(
                add(
                    decimal( body('Hour') ), 
                    decimal(
                    div(
                        decimal( body('Minutes') ), 
                        60 
                    ) 
                    )
                ),
                '#0'
            )
        )    
    , 100 ), 
    '0000' 
)

#powerautomate #powerbi