Joke’s Over – send Email to users when refresh fails

By Stuart Box

Microsoft Power BI in Practice

I do a lot of Power BI Training, and have been doing it since Power BI came out over three years ago. So to keep myself fresh, amuse myself and hopefully my delegates, I've built several "Jokes" into my Power BI training. But now with the latest update to the Power BI service one of my favourite gags has been stopped. The joke used to go like this.

The Tale

I'm an Office 365 users, that means my Email, Diary, Contacts and Task are effectively held in the cloud. (Oh sure it's replicated down to Outlook, but the original data is in the Office 365 cloud- right). So I can connect to my Office 365 email box via "get data" in Power BI desktop. Now Power BI can read my Email, Calendar, whatever. With a little work in Power Query, I can generate a list of all my Power BI Training bookings with the date they take place. (Connecting to Exchange Online via Power Query is awesome and enable so many insights from my data). Now it's a trivial step to use a Custom Visual to plot my bookings in any number of ways, one I really like is the simple "Calendar Visual" from Elastacloud, but there are lots to pick from.

The Set-Up

Eh Voila, I get a Power BI report showing how hard I'm working (or not as the case may be)

Now I've got my Report published to the cloud, I want to set up automatic refreshing on my Calendar Dataset. Well, it's going to be easy, because my data is already held in the cloud, so I won't need a gateway, I can go straight to the Calendar Dataset and configure refresh. "Cloud to Cloud" refresh means I don't need a Gateway, and I can schedule up to 8 refreshes per day on my Pro licence.

The Hook

And there, below the refresh schedule was the punchline "Send refresh failure notification email to me" I'd explain that if Microsoft Exchange was down and Power BI couldn't refresh my Diary data, it would send an email to me. Then I'd stand back and see who got it. In my delegates one or two would grin and a few would give an eyeroll as they realised that the email would not get sent, because the email transport wasn't working! We'd laugh and note that this probably wouldn't be a problem for 99% of the refresh scenarios, but that it was a limitation.

Over the years, various MVP's have solved this problem with clever use of Microsoft Flow but it remained a bit of a limitation. Without some clever workings around you couldn't send multiple emails or emails to anyone other than the dataset owner.

The Sting

Now Microsoft have fixed this small annoyance, and incidentally deprived me of a harmless geeky joke. Now underneath the renamed box "Send refresh failure notifications to the dataset owner" we have "Email these users when the refresh fails" and an area for a list of email addresses or groups. Now a refresh failure can email a group or a list of other users to let them know, without having to do clever things in Flow!

It doesn't quite kill my "Joke", these email addresses must be tenants in the Power BI Organisation. You can set up a guest account in the tenant for external users, which should work. However I haven't been able to test this yet, as Office 365 Exchange has never been down long enough to check!

Burningsuit train on all aspects of Power BI, we run regular Public Courses in London, and can train on-site anywhere there is a requirement. Contact us for more details.

"I can teach you with a quip, if I've a mind;
I can trick you into learning with a laugh;
Oh, winnow all my folly, folly, folly, and you'll find
A grain or two of truth among the chaff!"

Leave a Reply