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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,260
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,128,074
Messages
5,628,493
Members
416,321
Latest member
tomazik123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top