The native Table.Contains-function in Power Query tells you if one or more strings are included in one or more of its columns. But you have to be specific about which strings you search in which column. But what to do if you want to search a string in all of its columns instead? Use my new Table.ContainsAnywhere function.
In the native function, you have to pass in a record with search term and column name. So if you search for “blue” in column “Description”, your formula would look like so:
Table.Contains( YourTableName, [Description = "blue"] )
But that’s not what I want in this case. I want the formula to search through all columns within the table for the occurrence of “blue”.
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:
“View Native Query” shows folding query that’s send back to the server (if not greyed out)
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: Read more
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:
Error in Graph API custom connector when retrieving details from planner tasks
Edit 5th May 2019: Unfortunately this method will not work in the 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. Read more