Make a distinct with many columns (like in SQL)

Like (0) Share

Report

Posted on 8 Aug 2018 13:55:33 by freedumz

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

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

Categories:

General Questions

All responses (6)

Answers (1)

mLarenas 7 on 06 Apr 2022 at 21:01:11

Like (1)

Report

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

HI, what an old post! but i have the same issue and that solution worked for me:
You must use the function GroupBy( Table, ColumnName1 [, ColumnName2, ... ], GroupColumnName )
This function group the data like SQL....
Drrickryp Super User 2024 Season 1 on 10 Aug 2018 at 15:06:01

Like (0)

Report

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.
Mike8 1,328 on 10 Aug 2018 at 09:14:52

Like (1)

Report

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.
Verified answer

v-xida-msft on 10 Aug 2018 at 04:06:57

Like (0)

Report
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:

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
nickduxfield 497 on 08 Aug 2018 at 23:01:40

Like (0)

Report
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
Drrickryp Super User 2024 Season 1 on 08 Aug 2018 at 15:15:58

Like (1)

Report

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".