cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeterDonohue
Advocate II
Advocate II

Distinct Collection Still Showing Duplicates

Hey folks,

I have a collection that ius generated from a sharepoint list using a DISTINCT function. I'm calling this collection to populate a dropdown list but here's the kicker - I'm still getting duplicates? I checked the source and ensured there were no spaces etc

 

On Start

 

ClearCollect(
    colAreas,
    Distinct(
        'Strategies, Plans, and Policies',
        'Responsible Area '
    )
)

 

Dropdown

 

Sort(
    colAreas,
    Result,
    Ascending
)

 

 

This has got me stumped

6 REPLIES 6
timl
Super User III
Super User III

Hi @PeterDonohue 

This sounds very strange. I guess there must be some small or subtle differences in the non-distinct values that you're seeing.

When you say that you checked the spaces in the source, did you check for additional spaces between words, in addition to leading and trailing spaces?

To diagnose this further, what do you see when you call the distinct function on colAreas? For example, with this formula, do you still see non-distinct results in colAreas2?

ClearCollect(
            colAreas2,
            Distinct(
                     colAreas,
                     Result
           )
)

 

ham
Community Support
Community Support

Hey @timl I had excactly the same thought process 🙂

 

I actually click and dragged the source data to ensure it was identical so there should be no issue there. 

 

I also tried calling a DISTINCT function from colAreas and i still see duplicates. 

Got me baffled

@ham I checked those sites but they are not what I'm looking for. The Sharepains site is pretty woeful user expereince and extremely hard to understand

Hi @PeterDonohue 

In my experience, this is usually due to a following space. Trim() the column before using the Distinct() function.

Hi @PeterDonohue 

Great minds think alike!

To diagnose this further, maybe you could also return the length of the items in colAreas? Is there a discrepancy in length between the items that look the same?

ClearCollect(
            colAreas2,
            AddColumns(
                       Distinct(
                                colAreas,
                                Result
                        ),
                        "ResultLen",
                        Len(Result)
             )

)

 

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,857)