Skip to main content

Notifications

Announcements

🌸You're Invited: Community Spring Festival Awards Celebration 🌸
Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id : fZ7BWCttQLsDDRMclSoCg1
Power Platform Community / Forums / Power Apps - Microsoft Dataverse / Efficient Use of DropC...
Power Apps - Microsoft Dataverse
Answered

Efficient Use of DropColumns or Alternative for Dataverse Tables

Like (0) ShareShare
ReportReport
Posted on 21 Oct 2022 07:31:47 by messengineer 36

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
  • messengineer Profile Picture
    messengineer 36 on 24 Oct 2022 at 00:00:53
    Re: Efficient Use of DropColumns or Alternative for Dataverse Tables

    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.

  • Verified answer
    Drew Poggemann Profile Picture
    Drew Poggemann 9,278 Most Valuable Professional on 23 Oct 2022 at 10:28:24
    Re: Efficient Use of DropColumns or Alternative for Dataverse Tables

    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:

    Screen Shot 2022-10-23 at 5.27.49 AM.png

  • messengineer Profile Picture
    messengineer 36 on 23 Oct 2022 at 01:24:40
    Re: Efficient Use of DropColumns or Alternative for Dataverse Tables

    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?

  • Drew Poggemann Profile Picture
    Drew Poggemann 9,278 Most Valuable Professional on 21 Oct 2022 at 10:38:30
    Re: Efficient Use of DropColumns or Alternative for Dataverse Tables

    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.

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

🌸You're Invited: Community Spring Festival Awards Celebration 🌸
Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,743 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,079 Most Valuable Professional

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans