DISTINCT shows blank value, but source data does not

csotstot

New Member
Joined
Jun 1, 2017
Messages
23
I'm having a really strange issue in Power BI Desktop (64-bit, March 2019 release) that is totally baffling me...
I have a dimension table to find cost centers, with the following columns:
[Fund], [Cost Center], [Cost Center Name] which have been retained in Power Query from an Excel spreadsheet range with 5 extra columns.

Creating slicers from the table shows a blank value even with no other visuals in a test report, so I began trying to find blank values in the table. Power Query filters don't find any blanks in any rows and a calculated column looking for any blanks in any columns returns all FALSE responses.

Relationships all appear to be ok (one-to-many Dimension table to Fact table and single directional)...
I also verified that there are no FactTable.[Cost Center] values that are not present in the DimensionTable[Cost Center] column to produce a blank result anywhere.

I then created a single-column table in DAX using DISTINCT on the [Cost Center] column and the resulting table shows a blank row.
Since DISTINCT shows a blank row, I went back to the source spreadsheet itself and verified that there are no blank cells anywhere in any of the retained columns.

I can create a table in DAX that will filter the blank value, but I'd really like to understand why I'm seeing the blank in the first place since I don't show any blanks in the source data and table visuals of the source data don't show any blank values, even when placed in table visuals from related tables.
If anyone has any thoughts (even if it's that I'm completely off-base with my thinking/methodology), I would be extremely grateful...
 
Last edited:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,234
Look at the foreign keys in your fact table and the primary keys in your dim table. If you have a foreign key and no matching primary key, you will get a blank.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,989
Messages
5,526,095
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top