DropColumns that are empty from collection

[pacopepe82]

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!

[v-xiaochen-msft]

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]

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 · Accepted solution]

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]

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 

[pacopepe82]

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 · Accepted solution]

Hi @pacopepe82 ,

The sample for you:

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

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")))

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]

thanks again for your help!!!