Data Flows with null in boolean

Data Flows with null in boolean

Working with Data Flows (Power Platform Dataflows) is a useful method of getting a variety of types of data into Customer Insight and also other places. It is very easy to work with but troubleshoot it can be rather tricky as I havn’t gotten any good error messages. In this case, that null-values in booleans aren’t supported.

Recently I was using Data Flows to pull data from the datalake which was synchronized from dataverse with Azure Synapse Link. However, when I tried to publish the data flow, I just got generic errors. This is of course rather frustrating and if you have any good ways of troubleshooting this, please leave a comment.

However, after working with this and trying different angles, I did not that some people on some forums mentioned that there could be problems if boolean columns had null values. How is this possible? Booleans shouln’t they be either true or false? I have even seen that in official documentation. That is not the case in the real world. The most common scenario when you will get null-values in booleans in dataverse, is if you for instance have a table with 1M records, then add a boolean field. You will now have 1M records with null in the new field.
It was a simple fix. Just did a simple replace of the values in Power Query/M. I also checked the actual M code to make sure that it was ok, which I think can be hard to do from just the UI.

Table.ReplaceValue(#"Step 4", null, false, Replacer.ReplaceValue, {"<fieldname>"})

The important thing to notice is that it says “null” without any quotation marks. This is important as we do not want to replace the string “null” but the cells with no data.

I hope this will help you out a small bit in your data flow quest!