Somewhere in a busy workflow, a user is watching a pivot table refresh and wondering why the data beneath it will not cooperate. The scenario is familiar: Microsoft Power Automate triggers an OfficeScript, `workbook.refreshAllDataConnections()` runs, and the pivot updates. But the Power Query table, the actual foundation of the report, stays stubbornly static. The user has even disabled background refresh and enabled refresh on file open. None of it matters. The machine does what it does, and the human is left to ask: what is the best way to make Power Automate refresh Excel's Power Query?
This question is more than a troubleshooting plea. It is a window into the gap between automation's promise and its current reality. We often talk about tools that unlock ChatGPT for work or systems that bridge retrieval and action, but here we have a far more mundane friction. The script runs. The script works. And yet the orchestration fails at the exact point where data becomes useful. That is not a failure of effort; it is a failure of design.
Our honest take is this: the problem is not the script, and it is not the user. It is the assumption that a refresh command in one layer automatically propagates to every connected layer beneath it. Power Query tables live in a different state than pivot tables, even when they occupy the same workbook. The script can trigger a refresh event, but the table's own properties, including its connection settings and load behavior, govern whether that event actually changes anything. The user has already discovered the hard way that "refresh all" does not mean "refresh everything that matters."
What we would tell someone in this situation is to stop treating the script as the final answer and start treating it as one step in a chain. The script works; the pivot proves that. So the issue is likely that the Power Query table is not listening to the same command. A more reliable path is to bypass the table's internal refresh entirely and rebuild the connection logic so that Power Automate either triggers a full data refresh through the Excel connector's native actions or restructures the query to load directly into a format that respects external triggers. That is not elegant, but it is practical. And practicality matters more than purity when you have a report waiting.
There is a deeper lesson here about how we approach automation in general. We see this in other domains too, like monitoring test suites with persistent observability, where the tooling only helps if the data pipeline is built for it. The same principle applies here: automation is not a magic wand. It is a contract between layers, and if one layer refuses to sign, the whole workflow stalls. The user's pivot table is a decoy. It works, so the script appears functional. But the real test is whether the underlying data source, the Salesforce report, the query, the table, all move together. They do not. And until they do, the automation will keep producing the illusion of progress.
So what should you do? Start by questioning the relationship between your script and your data source. Do not assume that because a refresh command executes, it executes completely. Test the table in isolation. Check whether the connection is set to manual or automatic. And if you cannot get the table to refresh reliably through the script, restructure the workflow so that Power Automate handles the refresh directly, not through the script. It is less elegant. It is more explicit. But it will work. And in the end, the goal is not to write the cleverest script. It is to get the data into the hands of the person who needs it, on time, without a second thought. That is the only metric that matters.