Power BI: resolving the blank/empty cells that Power BI should not count them

Sometimes, in Power BI, after refreshing my table, I see that some empty cells are either:

  • Considered as blank cells -> blank
  • Or some as if they have values -> empty

Of course, all empty cells should be considered as blank cells, I mean, cells that don’t have any values because if some of them are counted as values, calculations that I will do in my measures will give me wrong results.

To give you an idea what I am talking about, do a filter and you will see the same thing as the picture below, the ones highlighted in red are those that Power BI considered that those cells have a value (space, invisible characters, etc.) in spite that they are empty.

power bi

Based on my experience, don’t try to fix your source, in my case, my source is my excel file and I tried many things, it didn’t work so the simple way I found to fix, it is to convert those empty cells into blank cells.

To do that, I have to go from the desktop to the editor interface by clicking on “home -> transform data -> transform data”.

power bi

Here, selecting the column on which I have this issue and do a filter again, Power BI considers:

  • Null as blank
  • And blank as empty
power bi

Now click on “transform -> replace values”.

power bi

A popup will appear and in the fields:

  • Value to find, let it blank
  • Replace with, put “null”

power bi

Once done, click on “OK” and if I do a filter again, I see that I have only “null”.

power bi

Now click on “home -> close & apply” to go back to the desktop interface. Let’s filter again and I will see that the empty ones have disappear and I have only “blank”, from now on, results will not be anymore incorrect.

power bi

Interesting Management