Unanswered

Remove duplicate rows from Gallery querying from a SQL database

(0) Share

Report

Posted on by MicroMarc365

101

I want to filter data in a gallery connected to a SQL database by the City column using a text input. Then I want to enter the City in the text input and then populate all the Zip Codes in that City, along with data from the remaining columns.

The columns that are querying from a SQL database are: Zip Code, House Number, Street Name, and City. This is what I'm seeing when I enter this code:

Sort(Filter(sqlDatabase,StartsWith('City',searchInput.Text)),Zip Code,SortOrder.Descending)

Zip Code	House Number	Street Name	City
78451	456	Holmes Ave	Chicago
78451	456	Holmes Ave	Chicago
78451	456	Holmes Ave	Chicago
78469	4895	Richard St.	Chicago
78469	4895	Richard St.	Chicago
78466	2387	Harwich Ave	Chicago
78466	2387	Harwich Ave	Chicago
78466	2387	Harwich Ave	Chicago
78466	2387	Harwich Ave	Chicago
78481	65	Birdeye Lane	Chicago
78481	65	Birdeye Lane	Chicago
78435	562	24 W 14th	Chicago

It's pulling in duplicates. I want to remove the duplicates.

I would like the gallery in my Power Apps form to show the following when I filter by City:

Zip Code	House Number	Street Name	City
78451	456	Holmes Ave	Chicago
78469	4895	Richard St.	Chicago
78466	2387	Harwich Ave	Chicago
78481	65	Birdeye Lane	Chicago
78435	562	24 W 14th	Chicago

As you can see the duplicates are not there.

I know that I cannot use the Distinct function because it will only return the values of one column.

Is there a formula I can use to remove the duplicates when filtering by city as shown in the second table?

Any advice can help. Thanks!

Categories:

Building Power Apps

All responses (5)

Answers (0)

Sort by

WarrenBelz 156,530 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @MicroMarc365 ,

Use the GroupBy function - the last grouped column Data needs to be there but is not relevant here

Sort( GroupBy( Filter( sqlDatabase, StartsWith( 'City', searchInput.Text ) ), "Zip Code", "House Number", "Street Name", "City", "Data" ), Zip Code, SortOrder.Descending )

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
MicroMarc365 101 on at

Like
a
(0)

Report
Copy link

Link copied!

Unfortunately, it's still populating duplicate rows. I did notice when I type in the column_name the data type is returning as text.
WarrenBelz 156,530 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@MicroMarc365 ,

Are the duplicates across all 4 columns (they should not be with GroupBy)
MicroMarc365 101 on at

Like
a
(0)

Report
Copy link

Link copied!

They are duplicates across all 4 columns
WarrenBelz 156,530 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@MicroMarc365

I will rephase that for clarity - you have records that are identical in all column values and when you use GroupBy as I posted, you have duplicates in the output ? If so, that is contrary to the purpose of the function and should not happen. I just ran a test here and received the expected result. Can you please post (in Text) the code you are using and a screenshot of the duplicates.