Efficient Use of DropColumns or Alternative for Dataverse Tables

(0) Share

Report

Posted on by messengineer

Hi,

I have a number of Dataverse tables where in the App OnStart I make local Collections of them so the App can run properly with no network available onsite.
When tables are created in Dataverse it adds 15 to 20 columns that I won't ever use in PowerApps like Created by, Created on etc and these cannot be deleted.
When building the Collections PowerApps recognises I never use the columns so leaves them blank in the Collection so it is not like memory is being wasted.
However I want to eliminate them completely ... is there a way I can just specify the columns I want to keep? Or iterate through the columns somehow?
I am just trying to avoid repeating the same code over and over for multiple collections 10 or 15 times.
Also sometimes depending on whether data was imported from Excel into DataVerse it adds other columns as well.

ClearCollect(col_Controls_Apps,
DropColumns(
BPHC_Controls_Apps,
"_ownerid_value", "createdby", "createdonbehalfby", "createdon", "importsequencenumber", "modifiedby", "modifiedonbehalfby", "modifiedon", "owningbusinessunit",
"owningteam", "owninguser", "overriddencreatedon","statecode", "statuscode", "timezoneruleversionnumber", "utcconversiontimezonecode", "versionnumber")
);

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @messengineer ,

Utilize Dataverse views to accomplish this filter for you in the collection. Example:

ClearCollect(AccountCollection,Filter(Accounts,'Accounts (Views)'.'Active Accounts'))

The above will populate the AccountCollection with only the fields in my Active Accounts view.

Was this reply helpful? Yes No
messengineer 36 on at

Like (0)

Report
Copy link

Link copied!
Hi,
Thanks whilst that is a handy feature I was not aware of being new to Dataverse ... it only sorts/filters the data to the view ... all the unwanted columns still appear in the Collection.
ClearCollect(col_Controls_Apps,AddColumns(Filter(BPHC_Controls_Apps,'BPHC_Controls_Apps (Views)'.col_Controls_App), "txtSelect",""));
It won't allow me to build a Collection directly from the View
ClearCollect(col_Controls_Apps,AddColumns('BPHC_Controls_Apps (Views)'.col_Controls_App, "txtSelect",""));
Or am I still missing something?

Was this reply helpful? Yes No
Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @messengineer ,

Sorry try this...

ClearCollect(col_Controls_Apps,ShowColumns(BPHC_Controls_Apps,"<column 1>","<column 2>")) Example from Accounts: ClearCollect(AccountCollection,ShowColumns(Accounts,"name","numberofemployees"))

This created a collection with the following:

Was this reply helpful? Yes No
messengineer 36 on at

Like (0)

Report
Copy link

Link copied!

Hi,
Thank you. I can't believe I missed this as I have read this help page before for AddColumns, DropColumns and RenameColumns as I have used all these before.
AddColumns, DropColumns, RenameColumns, and ShowColumns functions in Power Apps - Power Platform | Microsoft Learn

However after trying today I have given up on this idea. When using ShowColumns it forces you to do so by the Schema name rather than the Display name when listing them out. It then appears the resulting Collection no longer can refer to the Display Name and everything else in my App breaks.

Was this reply helpful? Yes No