cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
freedumz
Level: Powered On

Make a distinct with many columns (like in SQL)

Hi everyone

I'd like to do a distinct with many columns like we can do in SQL so to do simple, this a small example:

 

I'have this table

 

USA             Max
USA             Max
USA            Bob

Canada      Luc

Canada     Max

Canada     Bob

Canada     Bob

 

And I'd like to get:

 

USA        Max

USA        Bob

Canada  Luc

Canada  Bob

Canada Max

 

Any idea of Could I do that on Powerapps?

 

Thanks for your help

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Make a distinct with many columns (like in SQL)

Hi @freedumz,

 

Could you please share a bit more about your table?

 

I have made a test on my side, please take a try with the following wortkaround:1.JPG

 

 

Set the OnVisbile property of the first screen to following formula:

ClearCollect(
    BaseTable,
    {Country:"USA",Name:"Max"},
    {Country:"USA",Name:"Max"},
    {Country:"USA",Name:"Bob"},
    {Country:"Canada",Name:"Luc"},
    {Country:"Canada",Name:"Max"},
    {Country:"Canada",Name:"Bob"},
    {Country:"Canada",Name:"Bob"}
)

Note: The BaseTable represents the source table that you mentioned. On your side, you could use ShowColumns function to get the columns that you want to get (strips out all the unneeded columns).

 

 

Set the OnSelect property of the "Modify" button (Button control) to following formula:

ClearCollect(Collection1,RemoveIf(RenameColumns(RenameColumns(BaseTable,"Country","Country1"),"Name","Name1"),CountRows(Filter(BaseTable,Name=Name1&&Country=Country1))>1));
ForAll( RenameColumns(RenameColumns(BaseTable,"Country","Country1"),"Name","Name1"), If( CountRows(Filter(BaseTable,Country=Country1&&Name=Name1))>1&&IsEmpty(Filter(Collection2,Name=Name1&&Country=Country1)), Collect(Collection2,LookUp(BaseTable,Country=Country1&&Name=Name1)) ) ); ForAll(RenameColumns(RenameColumns(Collection2,"Name","Name2"),"Country","Country2"),Patch(Collection1,Defaults(Collection1),{Name1:Name2,Country1:Country2}))

 

Set the Items property of the Data table control to following formula:

RenameColumns(RenameColumns(Collection1,"Name1","Name"),"Country1","Country")

Note: The Collection1 represents the data table that you want to get. 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Make a distinct with many columns (like in SQL)

Add a column to the table that concatenates the two original columns and apply the distinct function to get the result.

Distinct(AddColumns(DataSource,"country_name",Trim(Country)&" "&Trim(FName)),country_name). This will give you a single column table with the column name "Result".

nickduxfield
Level 8

Re: Make a distinct with many columns (like in SQL)

Depending on your needs,

 

  • Bring the source in to a collection with ShowColumns() function which strips out all the unneeded columns
  • Or Create another collection using the source collection with ShowColumns
  • You may even add a useful Distinct combined with Filter and ShowColumns
Community Support Team
Community Support Team

Re: Make a distinct with many columns (like in SQL)

Hi @freedumz,

 

Could you please share a bit more about your table?

 

I have made a test on my side, please take a try with the following wortkaround:1.JPG

 

 

Set the OnVisbile property of the first screen to following formula:

ClearCollect(
    BaseTable,
    {Country:"USA",Name:"Max"},
    {Country:"USA",Name:"Max"},
    {Country:"USA",Name:"Bob"},
    {Country:"Canada",Name:"Luc"},
    {Country:"Canada",Name:"Max"},
    {Country:"Canada",Name:"Bob"},
    {Country:"Canada",Name:"Bob"}
)

Note: The BaseTable represents the source table that you mentioned. On your side, you could use ShowColumns function to get the columns that you want to get (strips out all the unneeded columns).

 

 

Set the OnSelect property of the "Modify" button (Button control) to following formula:

ClearCollect(Collection1,RemoveIf(RenameColumns(RenameColumns(BaseTable,"Country","Country1"),"Name","Name1"),CountRows(Filter(BaseTable,Name=Name1&&Country=Country1))>1));
ForAll( RenameColumns(RenameColumns(BaseTable,"Country","Country1"),"Name","Name1"), If( CountRows(Filter(BaseTable,Country=Country1&&Name=Name1))>1&&IsEmpty(Filter(Collection2,Name=Name1&&Country=Country1)), Collect(Collection2,LookUp(BaseTable,Country=Country1&&Name=Name1)) ) ); ForAll(RenameColumns(RenameColumns(Collection2,"Name","Name2"),"Country","Country2"),Patch(Collection1,Defaults(Collection1),{Name1:Name2,Country1:Country2}))

 

Set the Items property of the Data table control to following formula:

RenameColumns(RenameColumns(Collection1,"Name1","Name"),"Country1","Country")

Note: The Collection1 represents the data table that you want to get. 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User
Super User

Re: Make a distinct with many columns (like in SQL)

Hello,


Combining the answers of v-xida-msft and Drrickryp

You can also try this:

If BaseTable is your initial collection..
ClearCollect( BaseTable, {Country:"USA",Name:"Max"}, {Country:"USA",Name:"Max"}, {Country:"USA",Name:"Bob"}, {Country:"Canada",Name:"Luc"}, {Country:"Canada",Name:"Max"}, {Country:"Canada",Name:"Bob"}, {Country:"Canada",Name:"Bob"} )

Using this expression you have the data that you want.
Clear(BaseTable1);
ForAll(BaseTable,
If(
!LookUp(BaseTable1,BaseTable1[@Country]&BaseTable1[@Name]=BaseTable[@Country]&BaseTable[@Name],true),
Collect(BaseTable1,{Country:Country,Name:Name})))
and use the Basetable1

Or add to the expression above ClearCollect(BaseTable,BaseTable1) if you don't need BaseTable anymore and use the BaseTable collection.

 

Super User
Super User

Re: Make a distinct with many columns (like in SQL)

Wow, these other solutions may work but seem unnecessarily complicated. I used your original data and added the trim functions because there were spaces in the data but otherwise achieved the result  you requested.

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (2,750)