Unanswered

PowerApps - How to Get the First Unique Row for Each Value in a Column (Dataverse)

(2) Share

Report

Posted on by CU27031310-0

I have a Dataverse table with multiple columns. One of the columns (ColumnA) contains duplicate values. I need to filter the table so that only the first occurrence of each unique value in ColumnA is retained while keeping all other columns intact.

For example, my table looks like this:

ColumnA	ColumnB	ColumnC
Alpha	X	123
Alpha	Y	456
Beta	Z	789
Beta	W	012

Expected Output:

ColumnA	ColumnB	ColumnC
Alpha	X	123
Beta	Z	789

I have tried using Distinct(), but it only returns ColumnA and removes other columns. First() also gives errors when used incorrectly.

What is the best approach to achieve this in PowerApps (Dataverse) while keeping all columns?

Any help would be appreciated!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Michael E. Gernaey 53,974 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @CU27031310-0

how do you know which is the first occurrence? Are you just wanting to go off the Created DateTime?

Was this reply helpful? Yes No
DJ_Jamba 2,837 Moderator on at

Like (0)

Report
Copy link

Link copied!
Not sure how many records you're dealing with, but have you tried a collection?

Example:
Clear(col_DVData); With( { DV_Data: Filter( YOUR_DATAVERSE_TABLE, QUERY ) }, ForAll( DV_Data As theData, If( IsBlank( LookUp( col_DVData, ColumnToCheck = theData.ColumnToCheck ) ), Collect( col_DVData, theData ) ) ) )

Was this reply helpful? Yes No
CU17120711-0 11 on at

Like (0)

Report
Copy link

Link copied!

Power Automate can help you to remove duplicates and keep first occurrence using expressions like concat with ColumnA and Created

Was this reply helpful? Yes No
ronaldwalcott 3,862 Moderator on at

Like (0)

Report
Copy link

Link copied!
You really haven't provided sufficient details to propose a method to definitively satisfy your requirements but here is a limited method using a collection as an example showing the individual steps which you could use.

The first two lines are used to create the collection structure to be used in the ForAll

ClearCollect( NewCity, { City: "Milan", Country: "Italy", Population: 1344000, DateCreated: DateTimeValue("10/11/2014 9:50:24.765 PM")} ); ClearCollect( NewCityGrouped, GroupBy( NewCity, Country, Cities ) ); ClearCollect( CityPopulations, { City: "London", Country: "United Kingdom", Population: 8615000, DateCreated: DateTimeValue("10/11/2014 1:50:24.765 PM")}, { City: "Berlin", Country: "Germany", Population: 3562000, DateCreated: DateTimeValue("10/11/2014 2:50:24.765 PM")}, { City: "Madrid", Country: "Spain", Population: 3165000, DateCreated: DateTimeValue("10/11/2014 3:50:24.765 PM")}, { City: "Rome", Country: "Italy", Population: 2874000, DateCreated: DateTimeValue("10/11/2014 4:50:24.765 PM")}, { City: "Paris", Country: "France", Population: 2273000, DateCreated: DateTimeValue("10/11/2014 5:50:24.765 PM")}, { City: "Hamburg", Country: "Germany", Population: 1760000, DateCreated: DateTimeValue("10/11/2014 6:50:24.765 PM")}, { City: "Barcelona", Country: "Spain", Population: 1602000, DateCreated: DateTimeValue("10/11/2014 7:50:24.765 PM")}, { City: "Munich", Country: "Germany", Population: 1494000, DateCreated: DateTimeValue("10/11/2014 7:50:24.765 PM")}, { City: "Milan", Country: "Italy", Population: 1344000, DateCreated: DateTimeValue("10/11/2014 9:50:24.765 PM")} ); ClearCollect( CitiesByCountry, GroupBy( CityPopulations, Country, Cities ) ); Clear(NewCityGrouped); ForAll(CitiesByCountry, Collect(NewCityGrouped, {Country: Country, Cities: Table(First(Sort(Cities,DateCreated)))})); ClearCollect( NewCityGroupedUngrouped, Ungroup( NewCityGrouped, Cities ) );

Was this reply helpful? Yes No
Michael E. Gernaey 53,974 Moderator on at

Like (0)

Report
Copy link

Link copied!

@CU27031310-0

As you have not responded to the initial request for help, or the posts after, we will have to assume you are good.

Was this reply helpful? Yes No