Unanswered

Power Automate and Excel

(0) Share

Report

Posted on by Aldin

Hello. I'm working with Power Automate and inserting data into an Excel table (new table created in rows with heading). Everything works great.

But when I try to get a sum from a column it doesn't work.

For me it always shows €0. Not even with =SUM(Tab[net price]) which unfortunately also comes out to €0.

With a right click - format the cell and click on NUMBER or CURRENCY, unfortunately there is also € 0.-.

Only when I click into the row in one table, i.e. open exactly one row, and click ENTER, only then does =SUMME(Tab[net price]) calculate the sum.

But doing that with 10,000 lines that are updated every day is really annoying.

I also tried to tick the "Result row" box in the "Table design" tab and then changed the affected row to "Sum".

Unfortunately, that also comes out to €0.

Please help.

Thanks.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Shaheer Ahmad 2,194 Moderator on at

Like (0)

Report

It sounds like you're experiencing a common issue when working with Excel tables in Power Automate. When you insert data into a table in Excel using Power Automate, sometimes the table's calculated columns, like the sum of a column, might not update automatically. This is because the table might not be refreshed or calculated until you manually open and update a cell within the table.
To automatically calculate the sum of a column in an Excel table created using Power Automate, you can try the following steps:
1. Data Refresh: Ensure that your Excel table is set to refresh automatically when the data changes. To do this:
- Go to the "Data" tab in Excel.
- Click on "Connections" to open the "Workbook Connections" dialog.
- Select your data source connection and click "Properties."
- In the "Connection Properties" dialog, under the "Usage" tab, make sure "Refresh data when opening the file" is checked.
2. Excel Calculation Options:
- Ensure that Excel's calculation mode is set to "Automatic." To check this, go to the "Formulas" tab, and in the "Calculation" group, make sure "Automatic" is selected.
3. Power Automate Flow: If you are using Power Automate to insert data into Excel, make sure your flow includes the steps to refresh the table after data insertion. You can add a step to refresh the Excel table using the "Excel Online (Business)" connector.
- In your Power Automate flow, after inserting data into the Excel table, add an action using the "Excel Online (Business)" connector.
- Use the "Refresh table" action to refresh the table you are working with.
4. Ensure Data Types: Ensure that the data type for the "net price" column in the Excel table is set as a number. If it's not recognized as a number, the SUM function may not work as expected.
By following these steps, you should be able to make sure that the sum of the "net price" column is calculated automatically without manually opening rows. If you're still facing issues, please check if there are any specific configurations or limitations related to your environment or Excel version that may be affecting the automatic calculation.

Was this reply helpful? Yes No
Aldin 2 on at

Like (0)

Report

Hello and thanks!

I tried your steps and still couldn't get it done. I encountered the following problems:

1. Data Refresh: Unfortunately I can't find this option for me, do you have a screenshot please?
2. Excel Calculation options: I tried this, but it didn't help.
3. Power Automate Flow: Is this also available for the normal (i.e. non-business) model, or do I have to buy the business model? I can´t find it.
4. Ensure Data Types: I checked this too. Unfortunately it didn't help either. I'm still at 0.

I did the following until I couldn't find a solution:

I created another table with the same values.
There I got the line with “=Table1!J2”.
Then I created another column with "=value(I15)" and I finally took the sum from the "I" column.
Now I have a table with the data, a table with the copied data and I then use the third table as the real table for my overview.

I also tried this with zapier.com. I have the same problem there (with even a new Excel file).

PS: I tried the whole thing with Google Sheets, where it works with Zapier without any problems. But I would rather use Excel since that's what I always do.

KR
Aldin

Was this reply helpful? Yes No