Skip to main content

Notifications

Announcements

Join us on April 2nd for the Power Platform AMA - Copilot Studio Edition
🌸 Community Spring Festival 2025 Challenge 🌸
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps - Microsoft Dataverse / PowerApps - How to Get...
Power Apps - Microsoft Dataverse
Unanswered

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

Like (2) ShareShare
ReportReport
Posted on 27 Mar 2025 13:12:51 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
  • Michael E. Gernaey Profile Picture
    Michael E. Gernaey 37,875 Super User 2025 Season 1 on 30 Mar 2025 at 16:24:14
    PowerApps - How to Get the First Unique Row for Each Value in a Column (Dataverse)
     
    As you have not responded to the initial request for help, or the posts after, we will have to assume you are good.
  • ronaldwalcott Profile Picture
    ronaldwalcott 1,863 on 29 Mar 2025 at 23:24:24
    PowerApps - How to Get the First Unique Row for Each Value in a Column (Dataverse)
    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 ) );
     
  • CU17120711-0 Profile Picture
    CU17120711-0 11 on 29 Mar 2025 at 14:11:18
    PowerApps - How to Get the First Unique Row for Each Value in a Column (Dataverse)
    Power Automate can help you to remove duplicates and keep first occurrence using expressions like concat with ColumnA and Created
  • DJ_Jamba Profile Picture
    DJ_Jamba 2,549 Super User 2025 Season 1 on 29 Mar 2025 at 12:09:47
    PowerApps - How to Get the First Unique Row for Each Value in a Column (Dataverse)
    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
            )
        )
    )
)
     
  • Michael E. Gernaey Profile Picture
    Michael E. Gernaey 37,875 Super User 2025 Season 1 on 27 Mar 2025 at 16:16:30
    PowerApps - How to Get the First Unique Row for Each Value in a Column (Dataverse)
     
    how do you know which is the first occurrence? Are you just wanting to go off the Created DateTime?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join us on April 2nd for the Power Platform AMA - Copilot Studio Edition
🌸 Community Spring Festival 2025 Challenge 🌸
Welcome to the Power Platform Communities

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,513 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,636 Most Valuable Professional

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans