SlicerTable not showing distinct values

[mrhooki]

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?

[jdbuchanan71]

@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] ) )
)

[mrhooki]

@jdbuchanan71 thanks, but it doesn't do the trick I'm afraid. 

[jdbuchanan71]

Can you share the two lists?

[mrhooki]

@jdbuchanan71 I don't know if this image makes any sense to you, but here you see the two columns in the middle and the slicer table supposed to extract the distinct values from these on the left:

[jdbuchanan71]

Sorry, I meant can you share a .pbix file that has the lists you are working with.  I am not able to figure out what the problem is without working on your actual data.

You can upload a file to OneDrive or DropBox and share the link here.

[mrhooki]

@jdbuchanan71 hope this works:

https://www.dropbox.com/s/ypsaj15sa8ro6fu/PowerBI%20issue.pbix?dl=0 

[jdbuchanan71 · Accepted solution]

@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())
)
)

[mrhooki]

@jdbuchanan71 TRIM and CLEAN removes leading spaces and new CALCULATETABLE excludes blank rows. I now have an unique table, much appreciated!