Azure Data Factory – with Power Query?

Power Query (actually the execution language “M” for Monkey) is a killer app within Power BI Desktop – it brings a solid and far reaching Extract, Transform and Load (ETL) tool into immediate use. So it was with some excitement that this was brought into Microsoft Azure’s Data Factory where we could use it instead of proprietary ETL workflows. Basically, 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).

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:

A: There’s always another edge case!
B: Parts of Data Factory are not quite as polished as we may think.
C: We can contribute to Microsoft Product and get things done!

This website stores cookies on your computer. Cookie Policy