cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pacopepe82
Frequent Visitor

DropColumns that are empty from collection

Hi all, 

This is the first time I do an app and I need some assistance. 

In a summary I have a PowerApps that fills a Sharepoint list with more than 200 columns, but each job will only use 25-30 of them. In order to do a report I would want to dynamically collect the columns that contain data (using a button). I was wondering if there is a way to:

 

1) Ideally do a collection with columns containing data only 

2) If not possible, do a collection with ALL fields and do a second collection showing only the columns that are not empty (or dropping those columns that are empty, etc). I have tried this but I am stuck with the IF statements and multiple dropcolumns syntax.

For option 2 I have tried to create a label that will show if the column is empty or not: 

 

Label: If(CountA(obsTable.Adrenaline) = 0, "false", "true")

 

And then I am stuck with the following syntax to drop the False columns:

ClearCollect(obsTable2,
If(
Text(lb_APTT) = "false", DropColumns(obsTable, "APTT"),
Text(lb_adrenaline) = "false", DropColumns(obsTable, "Adrenaline")
))

This works for a single column but not for multiple and I clearly dont have a clue of what I am doing.

 

Thanks in advance for your help!

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @pacopepe82 ,

 

Yes . So If you have 200+ columns , I don't think you can remove blank columns because the formula is too long...

I don't think powerapps support this behavior now.

 

Best Regards,

Wearsky

View solution in original post

v-xiaochen-msft
Community Support
Community Support

Hi @pacopepe82 ,

 

The sample for you:

vxiaochenmsft_0-1653039315711.png

ClearCollect(Col,{Col1:Blank(),Col2:Blank(),Col3:"A"},{Col1:Blank(),Col2:Blank(),Col3:Blank()})

vxiaochenmsft_1-1653039322678.png

vxiaochenmsft_2-1653039335907.png

ClearCollect(Col2, If( CountA(Col.Col1)=0&&CountA(Col.Col2)=0,DropColumns(Col,"Col1","Col2"), CountA(Col.Col1)=0,DropColumns(Col,"Col1"),CountA(Col.Col2)=0,DropColumns(Col,"Col2")))

vxiaochenmsft_3-1653039343124.png

 

You know that I'm hardcoding the formula. So If you have many columns , this method is unreliable.

Powerapps is 'static'. What you can do is to optimize your table.

 

Best Regards,

Wearsky

View solution in original post

7 REPLIES 7
v-xiaochen-msft
Community Support
Community Support

Hi @pacopepe82 ,

 

There is no a dynamic method to remove the blank column.

If you use dropcolumns() function and If() function ,please try the formula like this:

 

 

ClearCollect(obsTable2,
If(
Text(lb_APTT) = "false" && Text(lb_adrenaline) = "false", DropColumns(obsTable, "APTT","Adrenaline"),
Text(lb_APTT) = "false", DropColumns(obsTable, "APTT"),
Text(lb_adrenaline) = "false", DropColumns(obsTable, "Adrenaline")
))

 

 

Best Regards,

Wearsky

pacopepe82
Frequent Visitor

Thank you for the reply Wearsky. Just to clarify, I understand from your reply that I would need to do individually each of the 200 columns + all the combinations between them using &&?

v-xiaochen-msft
Community Support
Community Support

Hi @pacopepe82 ,

 

Yes . So If you have 200+ columns , I don't think you can remove blank columns because the formula is too long...

I don't think powerapps support this behavior now.

 

Best Regards,

Wearsky

pacopepe82
Frequent Visitor

Thank you very much for the information. I will still try to break the table in smaller ones to minimise the impact of all the columns dropping 

by the way, the formula didn't work, as it will drop columns even with TRUE result in label. I really  appreciate the help !!! 

v-xiaochen-msft
Community Support
Community Support

Hi @pacopepe82 ,

 

The sample for you:

vxiaochenmsft_0-1653039315711.png

ClearCollect(Col,{Col1:Blank(),Col2:Blank(),Col3:"A"},{Col1:Blank(),Col2:Blank(),Col3:Blank()})

vxiaochenmsft_1-1653039322678.png

vxiaochenmsft_2-1653039335907.png

ClearCollect(Col2, If( CountA(Col.Col1)=0&&CountA(Col.Col2)=0,DropColumns(Col,"Col1","Col2"), CountA(Col.Col1)=0,DropColumns(Col,"Col1"),CountA(Col.Col2)=0,DropColumns(Col,"Col2")))

vxiaochenmsft_3-1653039343124.png

 

You know that I'm hardcoding the formula. So If you have many columns , this method is unreliable.

Powerapps is 'static'. What you can do is to optimize your table.

 

Best Regards,

Wearsky

pacopepe82
Frequent Visitor

thanks again for your help!!!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,474)