The new Relationships view (or Modeling view – it seems to have two names) in Power BI Desktop that has been in preview since November 2018 not only makes it easier to work with complex models and set properties more easily, it also exposes a brand new property on a column: the “Is nullable” property. It’s visible at the bottom of the new Properties pane when you click on a column:
I was, naturally, curious about what it did and I couldn’t find any documentation so I did a bit of investigation of my own and asked a few people at Microsoft.
It turns out that it is primarily intended for validation purposes, so that if you know a column should never contain a null value and then, at a later date, a null value does appear in that column then you’ll get the following error when you try to refresh a table in Import mode:
Column ‘MyColumn’ in Table ‘TestTable’ contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table
The only other use I have found for it is when it is used in combination with the DAX CombineValues() function in DirectQuery mode. In his excellent article on this subject, Marco notes that the SQL generated when you use CombineValues() to concatenate values from multiple columns and create a calculated column that can be used in a relationship includes a check for null values in those columns. I can confirm that if you turn off the ‘is nullable’ property on all the columns used by CombineValues(), there are no checks for null values in the SQL queries.
There may be a few other things that it does, but I’ve been told they are likely to be there to ensure correctness rather than performance optimisations. Nonetheless if you do find other scenarios where it has an effect please let me know in a comment.
[Thanks to Akshai Mirchandani and Jeffrey Wang for providing much of the information used in this post]
Interesting… this partly helps with one of the biggest annoyances for me in powerbi. I always have to clean (blanks) from appearing in slicers, the source for that is usually values that appear in the many side but not the one side of one to many relationships, but when those relationships can be be 3-4 levels deep it gets very complicated to clean them! Any tips for that?
That may be happening because you have (non-null) values in the column on the many side of the relationship that don’t exist in the column on the one side of the relationship.
Nice article. So is the indicator for NULL values, empty string, or blank? The error message seems to make it sound like NULL and blank are the same thing. Thanks.
Good question. It definitely works with nulls coming from the data source; I need to do some testing around all the different scenarios where you can get blanks.
Thanks for the insight.
Still undocumented sadly. I’ve asked for this to be done.
https://docs.microsoft.com/en-us/power-bi/desktop-modeling-view
IMO this is feature bloat – a feature with a techie label and a really unlikely use case. If you were interested in managing null data, would you want that obscure error message to kill your refresh?
This option was surreptitiously turned on for date columns in a fact table. I don’t understand how it happened, but it’s nonsense. And it has been a real annoyance, until I finally found your blog post. Thanks.
I am getting the same error but since performance pane is eliminated how I am supposed to tackle this error.??
Hi, I’m facing the same issue too. So what should i do?
What’s the difference to the optional keyword then?
Usually I would not react to blog posts… but man you have saved me after so much frustration. I tried to convert a properly functioning direct query model to an import model and kept getting an error about null values. There were no relationships in the model and I couldn’t figure out what the issue was. Nowhere Microsoft indicates the impact of this button, but thanks to your post I could get the model to work!