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:
Receiving files from business partners that don’t match the agreed requirements/formats causes all sorts of problems in daily business. I found it particularly disturbing during month-end closing when time is really tight: You have a strict rule in which order each process has to run and there are many dependencies between them. So when then one import doesn’t work, many other processes will come to a halt as well. Fortunately, today there is a simple remedy for it: Automatically validate E-mail attachments with Flow and Power BI
You can create a Flow that “listens” for incoming emails in a mailbox that match certain criteria and contain attachments. Flow can then extract these attachments and save them to an online-folder. After that, Flow triggers a refresh of a Power BI dataset, that imports these attachments and checks for the data quality-criteria that you have defined. Then you create measures for the data quality that trigger data driven alerts from Power BI service. Flow then listens for these alerts and sends an email back to the sender, requesting for a corrected file.
This not just saves crucial time, but also your nerves (and those of your team-mates).
Some time ago I published a function that extracts all M-code from Power BI (.pbix)-files. Today I publish the pendant to Bulk-extract Power Query M-code from multiple Excel-files at once. The code contains many elements from the before mentioned, so please refer to that article for reference.
The function below has just one parameter where you either fill in a full filename (incl. path) of an Excel file, or a folder path where multiple files reside. The function will automatically detect the right modus and spit out the M-code.