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
ClearCollect( colAreas, Distinct( 'Strategies, Plans, and Policies', 'Responsible Area ' ) )
Sort( colAreas, Result, Ascending )
This has got me stumped
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 ) )
Please check below threads related to distinct collections
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
In my experience, this is usually due to a following space. Trim() the column before using the Distinct() function.
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) ) )
Check out the News & Announcements to learn more.
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.