web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Show unique values in ...
Power Apps
Answered

Show unique values in a data table

(0) ShareShare
ReportReport
Posted on by PJBruen 195

Hi all

 

I have a Sharepoint list with the following values:

Year   Test       Scenario

2019    Test A      Scen-A

2019    Test A      Scen-B

2020    Test B      Scen-C

 

In Powerapps, I have a dropdown that filters the Year column to show only distinct values (2019 & 2020). This is fine.

 

What I then need though is a data table, displaying only 2 columns (Year & Test), where the Year is equal to the dropdown value selected, but only shows the unique combinations of Year and Test.

 

So, selecting 2019 from the dropdown I need to display a datable with only one row, showing:

 

Year   Test    

2019    Test A 

 

If that can't be done - then actually just a table showing unique Test values would be fine (so just showing Test A in this example - as the dropdown would show the year already).

 

Can anyone help with how I need to do this please? I've been looking at GroupBy, but can't seem to work out the syntax required for the Data Table Items property.

 

Many thanks...

Paul

 

 

Categories:
Building Power Apps
I have the same question (0)
  • mdevaney Profile Picture
    mdevaney 29,989 Moderator on at

    @PJBruen 

    My idea is to do this and put the data into a collection called colDataTable


    ClearCollect(colDataTable, ForAll(Distinct(ShowColumns(Filter(your_datasource_name, Year=Dropdown_Year.Selected,Value), “Year”, “Test”), ThisRecord), ThisRecord.Result)

     

     

    Here’s a simpler example of how to get the unique records in a collection.

     

    https://matthewdevaney.com/powerapps-collections-cookbook/unique-records-in-a-collection/

  • timl Profile Picture
    timl 36,415 Super User 2025 Season 2 on at

    Hi @PJBruen 

    Just to add to @mdevaney's  reply and to suggest something else without using ForAll, you should just be able to return a single column table with the distinct tests like so:

     

    Distinct(Filter(Scenarios, ThisRecord.Year= cboYear.Selected.Value),Test)

     

    If you want to add the year column back in, here's the formula you would use:

     

     

    AddColumns(Distinct(Filter(Scenarios, ThisRecord.Year= cboYear.Selected.Value),Test),
 "Year",
 cboYear.Selected.Value
)

     

  • PJBruen Profile Picture
    PJBruen 195 on at

    Hi,

     

    Many thanks for your reply, unfortunately I can't seem to get it to work though......are you able to see where I'm going wrong?

     

    Firstly, the ClearCollect statement. Should that go on a button or on the change of the dropdown selection.....or does it go on the Items property of the Data Table?

     

    Sorry - I'll ask some silly questions as I'm going around in circles with this one.

     

    Thanks,

    Paul

     

  • mdevaney Profile Picture
    mdevaney 29,989 Moderator on at

    @PJBruen 

    There are no silly questions here my friend.  Ask away!  Please use my code in the OnChange property of the DataTable.  

     

    Then put the collection in the Items property of the data table like this.

     

    colDataTable

     

    ---
    Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

  • Verified answer
    PJBruen Profile Picture
    PJBruen 195 on at

    Hi

     

    Thanks for your help. It's much appreciated, as this has had me spinning for hours.

     

    I actually came across another solution which should work well enough for now, which didn't require the creation of a collection in this case.

     

    On the Items property of the Data Table, simply put: First(Filter(DataSource, Title=Dropdown.selectedText.value))

     

    Thanks again.

     

  • PJBruen Profile Picture
    PJBruen 195 on at

    Hi,

     

    Many thanks for your suggestion. I've now found an alternative solution which was: 

    On the Items property of the Data Table, simply put: First(Filter(DataSource, Title=Dropdown.selectedText.value))

     

    For the scenario I had, this suits the requirement.

     

    Thanks again though.

    Paul

  • ffabdon Profile Picture
    ffabdon 4 on at

    Hi,

    I am desperately in need of your help please. can you please advise how i can use distinct for below?

     

    CURRENT TABLE

     

    HU-01-2023-Insr

    		ABCD
    HU-01-2023-InsrABCD
    HU-01-2023-PAsrABCD
    WI-O1-2023-InsrABCD
    WI-O1-2023-OAsrABCD
    SC-09-2023-InsrABCD
    SC-09-2023-BAsrABCD

     

    I wanted to have as a RESULT,

    HU-01-2023ABCD
    WI-O1-2023ABCD
    SC-09-2023ABCD


    Looking forward to your help please

  • WarrenBelz Profile Picture
    WarrenBelz 153,117 Most Valuable Professional on at

    @ffabdon ,

    You have not included any field names - so use yours where I have first and second

    AddColumns(
 GroupBy(
 AddColumns(
 DataSource,
 "NewField",
 Left(FirstFieldName, 10)
 ),
 "NewField",
 "Data"
 ),
 "OtherField",
 First(Data).SecondFieldName"
)

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

    MVP (Business Applications)   Visit my blog Practical Power Apps

  • ffabdon Profile Picture
    ffabdon 4 on at

    Thank you @WarrenBelz but it didnt work for me? 😞 maybe i did it wrong?

    My current table was the result of this code:

    Filter(
    tblInsurance,
    ASSET=SearchFld_1.Selected.Value
    )

    The value of my SearchFld_1.Selected.Value = "ABCD"

     

    The name of the first column

    CURRENT TABLE

     

    INSURANCE_ID

    		ASSET

    HU-01-2023-Insr

    		ABCD
    HU-01-2023-InsrABCD
    HU-01-2023-PAsrABCD
    WI-O1-2023-InsrABCD
    WI-O1-2023-OAsrABCD
    SC-09-2023-InsrABCD
    SC-09-2023-BAsrABCD
  • WarrenBelz Profile Picture
    WarrenBelz 153,117 Most Valuable Professional on at

    @ffabdon ,

    You would need something like

    AddColumns(
 GroupBy(
 AddColumns(
 Filter(
 DataSource,
 Asset = SearchFld_1.Selected.Value
 ),
 "InsuranceID",
 Left(INSURANCE_ID, 10)
 ),
 "InsuranceID",
 "Data"
 ),
 "Asset",
 First(Data).ASSET"
)

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

    MVP (Business Applications)   Visit my blog Practical Power Apps

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

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 765 Most Valuable Professional

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 343 Super User 2025 Season 2

#3
Power Platform 1919 Profile Picture

Power Platform 1919 272

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans