Answered

Collecting SQL data with a filter

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello PowerApps commmunity,

I am fairly new to PowerApps and am having difficulties understanding where I go wrong in my logic here.

Case:

I have a table of ~100 rows and I would like to collect some of these rows and place them in a collection OnStart.

In SQL terms what I am trying to achieve is as simple as:

SELECT *
FROM DB.Table
WHERE [ID] LIKE 'Default%'

But I am having an embarrassing amount of difficulties in achieving this.

I tried this simple code:

ClearCollect(col_DataDefaults,
 Filter('DB.TableName', "Default" in ID) 
);

This return only the rows I need - so far so good.
I also get all of the columns in the table - great.
BUT, all columns except for the ID columns that I am filtering on contains blank values.

From what I understand about delegation, using something like AddColumns() is not sustainable because of delegation, is this correct?

I need to pull these specific rows to use as default values in various locations in my app.

Thank you in advance for any help

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
I tried to step the query like this:

ClearCollect(col_DataDefaults, 'PowerAppDev.Konfig_tags'); Filter(col_DataDefaults, "Default" in ID)

But I still end up with the same result where I the correct data in the ID column but the rest of the columns remain blank.

I must be missing something simple and obvious here.
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

You could do the filtering in Sql and create a view to use in PowerApps.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Drrickryp,

Thank you for your message.

Writing a query that can be delegated would also result in data being filtered in SQL.

I realize I can create views but am afraid of the amount of views I will end up with ultimately.

There must be a way to pass this simple query along to the DB? I can do it without any issues in PBI or Automate. It's a super simple filter it makes no sense if I cannot delegate this to backend. 😞
Verified answer

timl 37,287 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
@Anonymous

'Explicit column selection' is the reason why the columns other than ID contain blank values. This is a performance optimisation by Power Apps, which is a good thing. You can retrieve the other columns that you require by calling ShowColumns:

ClearCollect(col_DataDefaults, ShowColumns(Filter('DB.TableName', "Default" in ID), "ID", "ColumnYouWant1", "ColumnYouWant2", "ColumnYouWant3" ) );
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you @timl for both the example and taking the time to explain this to me.
It is now working and the behaviour is both logical and preferred now that I understand it.
timl 37,287 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

You're welcome @Anonymous

Just in case it might be of help to you, the Power Apps StartsWith function enables you to do the equivilent of LIKE 'Default%' in SQL.

The in operator that you're currently using will be doing a LIKE '%Default%'