I have built two PowerAutomate workflows--the first workflow populates rows into Table A in an Excel Online workbook and the second workflow retrieves rows from Table B in the same workbook. Table B pulls data from Table A and runs a series of transformation calculations on the data. I implemented this 'two table' approach because the Excel Online connector doesn't allow you to push data into an Excel table that also includes computed columns.
The issue I have is that Excel Online doesn't seem to perform a background calculate of Table B. My second workflow successfully grabs rows from Table B, but they're empty. However, if I open the workbook in browser and force the calculation, then the second workflow runs fine and 'sees' the calculated data.
Has anyone else seen this? Is there a way to get Excel Online to force calculate the workbook without human intervention? I guess I could try using the new RPA features in Power Automate to open the workbook and perform the force calculate, but it seems like there should be an easier way.
Any suggestions welcome!
I did take a look at doing the transforms and math functions in Power Automate. I was hoping for an Excel implementation based on two things:
(1) Given the limited native function set in Power Automate, the functions implemented in Excel--esp the more math-based stuff--would be a pain to implement and maintain.
(2) The calculation logic implemented in Excel is occasionally updated, and I had hoped to expose the Excel to the business owners so they could self-maintain the logic.
Understood. The point is, as you mention "if I open the workbook in browser and force the calculation, then the second workflow runs fine and 'sees' the calculated data".
Meaning even if you update your Excel Table A data via the first flow, your Excel formulas only update Excel Table B when you physically open the file, right?
I would say it is unfortunately the result I would expect, but I am not an expert in the evolution of the Excel connector in Power Automate. Let's hope somebody else can confirm us the bad news or point us in the right direction
Features releasing from October 2020 through March 2021
We are excited to announce the launch of Power Virtual Agents Community. Check it out now!
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!