use a button in powerapps to Sum every row in my excel table

[DaweiLi2333]

In my Excel table, I have three columns like this

This idea is I just need to push the button in power apps and in my excel, the column3 will display the number of column1+column2.

Just like the formula in Excel

How to write the code in OnSelect in the Button

[WarrenBelz]

Hi @DaweiLi2333 ,

Doing this on a PowerApps form - using the names Column1Txt and Column2Txt for the controls containing these and TotalTxt for the control you want the total in the code on the OnSelect on the button would be

UpdateContext({vTotal: Value(Column1Txt.Text) + Value(Column2Txt.Text)});
Reset(TotalTxt)

The Default of TotalTxt

If(
  vTotal >0,
  vTotal,
  Parent.Default
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[DaweiLi2333]

I still cannot figure it out.

Column1 and Column2 have number, and Column3 is empty.

and I need write the sum of Column 1 and 2 into 3.

I don't need to show any information my my Powerapps.

Just need update my excel

[WarrenBelz]

Ok @DaweiLi2333 ,

You need to display it somewhere (in a Form as described in my last post or a Gallery) to get the item you want to action.

Are you wanting to update a selected item or all items that you can see?

For a single item using a Gallery as an example with the button in the gallery (you will see a button for every item), you could write back to the data source with something like this (you will also need a unique identifier in your table)

UpdateIf(
   YourExcelTableName,
   Lookup(
      YourExcelTableName,
      YourUniqueIDName = ThisItem.YourUniqueIDName
   ),
   {Column3: ThisItem.Column1 + ThisItem.Column2}
);
Refresh(YourExcelTableName)

You would then see the updated total in the gallery if it were displayed there.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[v-xida-msft]

Hi @DaweiLi2333 ,

Do you want to calculate the Column3 value based on the Column1 and Column2 in your Excel table when you press the button in canvas app?

Where do you store your Excel table? In OneDrive or OneDrive for Business folder?

Based on the needs that you mentioned, I think the UpdateIf function could achieve your needs. Please consider set the OnSelect property of the "Button" to following:

UpdateIf(
         YourExcelTable,
         true,
         {
           column3: column1 + column2
         }
)

or

UpdateIf(
         YourExcelTable,
         true,
         {
           column3: Value(column1) + Value(column2)
         }
)

Note: When executing above formula, please make sure your Excel is not opened in your browser. Otherwise, you may face a File Lock Issue while executing above formula

Best regards,

[WarrenBelz]

Hi @DaweiLi2333 ,

@v-xida-msft 's response is the next bit it I asked if you want to update all the items.

The Value option would be applied if you were storing the fields as text (I assumed they were numeric).

[DaweiLi2333]

It still cannot work

I store my excel file in OneDrive

And using connection in PowerApps to connect OneDrive for Business

Please look at the attachments.

[DaweiLi2333]

I just need this 

see attachment

put the button and the excel will show price *number in total

I store this file on my OneDrive, and use PowerApps to connect this table.

[WarrenBelz]

Hi @DaweiLi2333 ,

Try

UpdateIf(
   YourExcelTableName,
   true,
   {total: price * number}
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up. 

[v-xida-msft · Accepted solution]

Hi @DaweiLi2333 ,

Based on the needs that you mentioned, I have made a test on my side, and don't have the issue that you mentioned.

Please consider re-generate a new app based on your Excel table data source, then within this generated app, add a button, set the OnSelect property to following:

UpdateIf(
         product,
         true,
         {
           total: Value(price) * Value(number)
         }
)

Note: In default, when you create a connection to your Excel table from your canvas app, it would generate a new column called "__PowerAppsId__" within your Excel table as below:

Please check if the "__PowerAppsId__" field is generated in your Excel table already.

Best regards,