Delete "phantom" blank in slizer. Workaround doesn't work.

[Talvien]

I read in some threads about phantom blanks that appear in slizers due to some crazy relationships. The typical workaround of transforming and excluding blanks doesn't work. How can I get rid of it?

The blanks appear in slizers with values from the Period table.

[v-yuezhe-msft]

Hi @Talvien,

Have you checked the following similar thread?
https://community.powerbi.com/t5/Developer/Table-Relations-and-Blanks/td-p/19559

The phantom-blank issue is caused by that the relationships between your tables don't match up correctly. As mentioned by greggyb in above thread, when you have a row in the many side table that does not have an associated key in the one side table, the storage engine will implicitly create a phantom-blank row. In this case, add an explicit row to the one side table for all possible values in the many side table, or alter the many side table to exclude those rows.


Thanks,
Lydia Zhang

[Talvien]

Thanks, for your answer, yes, I know this thread.

My problem ist that I don't know how to setup a dummy keyfield for all datasets that doesn't match.

I would prefer altering the table. I guess I need to delete all rows in tables "Budget" and "Actuals" that have no matching key in "Periods". But how can I do this?

Edit: I looked around in Power Query Library and found something:

Table.RemoveMatchingRows()

Now I need to find out which rows these are. Therefor I need to check for every row of "Budget" and "Actuals" if the the PeriodKey is also in the "Periods" table. Every row where this isn't the case has to be part of the rowlist that is given to the Table.RemoveMatchingRows()-Function. Then it should work. I just need to know how to get this list...

[Talvien · Accepted solution]

I solved the problem!

Using Power Query I selected all rows in which the PeriodKey contained one of the years I use in the period table.

    #"Remove Rows" = Table.SelectRows(#"Changed Type", each if Text.Contains(Text.From([PeriodKey]),Text.From(Date.Year(DateTime.Date(DateTime.LocalNow()))+1)) then true else if Text.Contains(Text.From([PeriodKey]),Text.From(Date.Year(DateTime.Date(DateTime.LocalNow())))) then true else if Text.Contains(Text.From([PeriodKey]),Text.From(Date.Year(DateTime.Date(DateTime.LocalNow()))-1)) then true else false) 

Maybe it can be done in a shorter statement but basically all it does is checking the value of each for containing 2015, 2016 or 2017 (in current year). This is the year range I build up my Period Table with.

[tjd]

Have you tried changing the slicer to a table, then "filter the visual" to exclude blank values, apply filter, then convert table back to a slicer?  It works for me.

[Talvien]

Yes, as written in the first entry and the topic title I tried this.