Forum Discussion

mrhooki's avatar
mrhooki
Regular Visitor
6 years ago
Solved

SlicerTable not showing distinct values

In order to extract values imported from SharePoint list with multiple-value columns in Power BI (data shown as table) I have used “Field Values As Text” column, getting values in these “text” columns now displayed in single rows, delineated by semicolon. I then split the column by semicolon delimiter into to new columns with single values. Problem now is that the Slicer Table (Slicer Table = DISTINCT(UNION(VALUES(query[column 1); VALUES (query[column 2)))) I put in to display distinct values is not able to return distinct values for some reasons- it's in fact displaying all the distinct values in column 1 AND all the distinct values in column 2, which means I get duplicate values. Have checked excel table (source of lookup SharePoint list), SharePoint list export to excel, thinking some hidden characters might have been included when I split the column by semicolon (e.g. line space).  Help anyone?

 

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    mrhooki 

    You have some leading spaces on fields which makes them different to PowerBI.  

    You can use the TRIM and CLEAN functions in PowerQuery to get rid of that.  Then, to build the slicer table we can exclude empty rows (both "" and null) like so.

    Slicer = 
DISTINCT(
UNION (
    CALCULATETABLE( DISTINCT ( ValuesAsText[Addperspnew1] ),ValuesAsText,ValuesAsText[Addperspnew1] <> BLANK()),
    CALCULATETABLE( DISTINCT ( ValuesAsText[Addperpnew2] ),ValuesAsText,ValuesAsText[Addperpnew2] <> BLANK())
)
)

8 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    6 years ago

    mrhooki 

    I don't see anything wrong with your code but maybe try this instead (DISTINCT instead of VALUES).

    Slicer Table =
DISTINCT (
    UNION ( DISTINCT ( query[column 1] ); DISTINCT ( query[column 2] ) )
)