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.

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

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

Now click on “transform -> replace values”.

A popup will appear and in the fields:
- Value to find, let it blank
- Replace with, put “null”

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

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.

Interesting Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...
-
Refresh Power BI
From the Power BI Service, I can set refresh but, for instance, there is no option to do it monthly or each time a change is made...
-
Power BI alerts to be sent by email from an excel file based on condition
I will explain how to send a list of emails from an excel file after creating alerts...