Slicer indicates blank field despite there is none

[Anonymous]

Hi all,

I know it's a rather small issue but still a thing.

I made slicers and they show indicate that the tables they are based on contain blank values. However, I've gone through the raw table as well as through what has been uploaded to PowerBi. I even removed all blank rows in the PowerQuery Editor.

Nevertheless, the Slicer still gives me the possibility to select "Blank" (with no values assigned). Why is that and how can I get rid of it?

I know its only of cosmetical nature but it just looks bad in the finished reports and could foster uncertainity amongst users who are not familiar with the report. 

EDIT: Ok just gut a lot more relevant to me cause I just realised that this blank field tends to led to a chain reaction (that I don't understand) that leads distorted values in many visuals. I can still apply a report filter and exclude specifically the blank values from the report but would prefer a cleaner solution

[v-stephen-msft]

Hi @Anonymous ,

It may be caused by the following circumstances.

As for the problem of what causes (blank) values to appear first, when the values in the multilateral table of a one to many relationship are not included in the corresponding columns of the table of 1, this will certainly happen - side (slicer is located in one column of the table of 1 side).
For example, if my sales table contains 2014 and is related to a year table that does not contain 2014, the (blank) value is automatically inserted into the year table (behind the scenes) to handle any value that exists in sales but does not exist in the year.
Another way to think about it is the "extended" sales table, which includes a left outer join to the year table, which results in the generation of (blank) rows, where "sales" contains years that do not exist in the year table.

The example is here showing one slicer with (Blank) from an 'incomplete' Year table, and one without (Blank) from a 'complete' Year table.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , Tables join can give that.

Why there is blank -https://www.youtube.com/watch?v=23qemkDqMVY

[Anonymous]

Didn't use the join function tho... it's a descrptive table to assign dates to fiscal years

I also watched the video but the problem that I got is that the data where the error is in comes directly from SAP... so I can't just tell the people I get it from to fix it... would a solution be to delete all blank values in the tables via PowerQuery --> Delete blank rows? That should do the job isn't it?

[amitchandak]

@Anonymous , you deleted blank rows. Does this mean no blank data in FY column?

[Anonymous]

Nah so I got two tables:

1. The FY table which definitely has no blank values.

2. My sales table that contains all the transactions. 

As far as I understood, the Blank slicer means that there is some broken data/blanks in the sales table. I actually know where but I can't delete it by hand so I was wondering if I should either delete the blanks via PowerQuery or if there is a formula for that.

Problem is, the Sales table was created using the UNION-function and I don't know if I can somehow put a formula in there that deletes the blank rows

[Anonymous]

I think I finally found the source of the problem... it was neither of what was mentioned above, long story short: My dataset is really huge but I figured out there was one single column in which SAP (where I get my data from) aggregates all values of the whole column (Net Value). All other fields in this row are blank.

That caused a lot of values to show utterly high numbers as well as to cause a blank field in the slicer- wrapping it up: Had bad data, fixed it (by ignoring all Rows where Fiscal year is blank), works like a charm now 🙂