cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JR-BejeweledOne
Super User
Super User

Adding Distinct Excel rows to a collection

This is little strange but here goes.

 

I have about 38K rows of data.   In this data there are multiple columns that contain duplicate data.   One of these columns is Personnel Area.   What I am trying to do is create a collection of distinct personnel areas and their associated data.

 

I am starting with an empty collection and the goal is to end up with a collection containing one of each distinct personnel area along with it's accompanying row data.

 

I have a couple ideas rattling around on how to best accomplish this, but am looking for suggestions.



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
3 REPLIES 3
zmorek
Super User
Super User

My initial thought is a GroupBy(), so that you can access the rest of the columns (vs. Distinct() returning a one-column table that you have to start using .Result with)... Have you played around with any ideas yet?

Drrickryp
Super User
Super User

@JR-BejeweledOne 

It may be easier to work the data (Remove duplicates) in Excel using Power Query and then bring it back into PowerApps as a new table.   If your data is in Excel, you will run into delegation issues if you try to use Distinct() or GroupBy().

So I was able to get my distinct personnel areas (529) into a single collection.  Now I want to grab the details and put them into another collection.   I can't quite get it.   I am thinking that something like this SHOULD work, but it's getting all 14,998 rows instead of the 173 it should be getting.

 

colTemp contains 529 personnel areas.  Table2 is the first of 3 tables that contain the rest of the details and has 14,998 rows

ForAll(colTemp As PA,

If(
    !(PA.Result in colLocations.PersonnelArea),
    Collect(colLocations, Filter(Table2, PersonnelArea = PA.Result)
    )
)
)

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (1,398)