DAX · Power BI

The Is Nullable Column Property In Power BI

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]

11 thoughts on “The Is Nullable Column Property In Power BI

  1. DavidBenaim – Chartered accountant David is a Microsoft MVP for Excel, PowerPoint & other Office Apps who also appreciates where competitor apps (such as Google Sheets or Zoom are stronger). David's YouTube provides weekly videos on these apps plus he regularly writes articles and gives webinars on them. David has trained over 2000 people in Excel and runs Xlconsulting, a Cambodia based technology consulting firm.
    David benaim says:

    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?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      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.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      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.

  2. 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.

  3. 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!

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.