Admin If you’re working with large data or complex queries that take a long time refresh, cancelling one of those refreshes can even take longer time, especially, if the query has run for quite some time already.
Luckily, there is an easy trick to cancel refresh without loosing the work you’ve done already:
In a previous post I’ve described how to use a custom connector to retrieve data from the Microsoft Graph API. But this requires to register an App and adjusting the M-code in the connector itself requires some M-knowledge. So I thought it might be a good idea to share an alternative method to retrieve data from Teams for example, that works out-of-the-box. There are a couple of endpoints supported currently:
If you query databases who support query folding, you’re probably very aware of every step you take and check if folding happens with every new step like so:
Folding will (usually) happen as long as “View Native Query” isn’t greyed out.
So when doing an inner join on tables whithin the same database, I was a bit surprised to see this greyed out actually. As according to the literature, it should fold.
But guess what? After I expanded a column from it, the folding was back again:
Edit: Microsoft has announced that they don’t support querying MS Graph in Power BI: Lack of Support for Microsoft Graph in Power Query – Power Query | Microsoft Learn
The Graph API can deliver a huge amount of interesting data from your Microsoft 365-universe, but the Graph API custom connector for Power BI is not able to retrieve everything from it in its current shape. So I’ve modified it a bit to squeeze out a bit more of its sweet juice.
When trying to get the details for planner tasks, the following error-message appears:
Edit 5th May 2019: Unfortunately this method will not work in the Power BI service!
Edit 23rd August 2021: Pedro del Melo Cabral Sousa has found a workaround how to make this method work in the service. I haven’t had the chance to test this out, but you might want to give it a try: “if you migrate the necessary queries to power bi dataflows and then create one big query in your report dataset with all the necessary references to the power bi dataflows together with the POST request, it will work in Power BI Service”
In my last 2 posts I’ve described a way to automatically validate attachments from incoming E-mails. Microsoft Flow would watch for incoming E-mails, that match certain criteria and move their attachments to a dedicated folder. Then it would trigger a refresh of a Power BI dataset, that has been designed to check for errors in those attachments. Data driven alerts in Power BI would indicate if there are errors and trigger a Flow that sends an E-mail back to the sender, informing him that his attachments didn’t meet the agreed criteria.
In this article I will now explain how not just a trigger about the existence of a faulty attachment could be passed back to Flow, but also the corresponding data itself. Therefore I write a query that exports data from Power BI to Flow. But watch out: This is not suitable for very big tables. I experienced timeouts at tables with 300k rows already. Also, you need a paid Power Automate license for the connector (Pricing | Microsoft Power Automate).
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: