Forum Discussion

DataGuy2016's avatar
DataGuy2016
Advocate IV
9 years ago
Solved

DISTINCT command pulls BLANKS

Hi there,   We are creating DISTINCT tables.  We are getting distinct rows, but we are also getting a BLANK row.   How do we remove the BLANK row so we only have non blank rows?   We looked at:...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    DataGuy2016

     

    Just change the column instead of all table

     

    Table = DISTINCT(FILTER(VALUES(Transactions[CheckInLoc]),LEN(Transactions[CheckInLoc])>0))

Vvelarde's avatar
Vvelarde
Community Champion
7 years ago

 

MarkPalmberg 

 

Hi, maybe there are duplicates in the Union.

 

Also i prefer to filter the blank rows instead of Len > 0

 

DISTINCT(FILTER(VALUES(Table1[Unit]),Table1[Unit]<>BLANK()))

Regards

 

Victor

MarkPalmberg's avatar
MarkPalmberg
Kudo Commander
7 years ago

Thanks for your reply, Vvelarde . 

 

I tried another method to generate my table of distinct values, but I'm still getting the same duplicate errors value.

 

I created two queries, one from each "Unit" column in my two source tables. I then removed dupes from each and converted them to tables, then did an append query of those two tables and removed duplicates from *that* query. No love. I could generate this list of values in a SQL query, but I'm trying to learn some DAX and M within Power BI so would like to get it working there, if for no other reason than to discover what I've done wrong.