Power Query (actually the execution language “M” for Monkey) is a killer component of Power BI Desktop – a solid Extract, Transform and Load (ETL) tool. So it was with some excitement this is also now in Microsoft Azure’s Data Factory where we could use it instead of proprietary ETL workflows. This powerful code would be very re-usable and worth investing even more time in getting it to perform tricks (from data loading to pivoting and a lot in between). It could even load up our Data Warehouse analytic solutions.
Even better, within Data Factory (DF) it could be included within Pipeline runs for full workflow management and monitoring – after all, that’s the whole point of DF. And sure enough, rather than an end user hitting an error whilst refreshing a Power BI Desktop analytics file, we can be alerted of an error on the server first:
Bingo! Albeit not the news we like – an error!
So, we find a “UserQuery: Expression.Error. Unsupported constant null”.
Hmm, odd, because we deliberately put M code in to transform (the “T” in ETL) some special characters in the source data to zero. Funnily enough, we’d prefer to use Null itself as that is more specific within the target SQL Server database, but that’s another story – for now, using zeros is fine. So we don’t expect even that to cause such an error.
Debugging further shows there’s not much we can do – it is what it is, an unexpected error on expected data scenario. Fortunately, we have a partner paid support contract with Microsoft, so time to give them the details. Which perplexes their data engineer in agreement. So escalated up to the product team. That takes a while to be seen to, no surprise they’re busy, and some to-ing and fro-ing. But persistence pays off and eventually they concede it is a bug and needs a fix. A few weeks and it’s done – and the error cleared on import!
The moral of this story is that:
