Hi all,
I'm currently trying to build a PA flow that refreshes an Excel sheet I have on SharePoint and refreshes the data from PowerBI connection - using the start of the flow to run an automate script on the sheet.
Currently I have a table next my to PBI PivotTable to interrupt data to allow List rows present in table in flow.
However every time a refresh is triggered from script from flow the table returns an #VALUE! error;
I'm currently using =DATEVALUE() in the table column which I know works when first setting up the sheet.
The #VALUE! error only comes up after a refresh from script. Doesn't happen if refreshing manually either, only when in background.
This seems like it could be more of a bug than anything else as testing all the steps manually works, and I also had the entire Flow working perfectly the other day and this started happening.
Any help is greatly appreciated.
Thank you.
Hi @DanielPennacchia, it's possible that Power Automate is loosing the table reference. It may be of no relevance in this case but here is some guidance I've shared previously.
PA automation which references spreadsheets relies on there being a named table of data in the spreadsheet. That's simple enough but if you have cause to replace or over right the spreadsheet with another, newer version, the flow which refers to it will loose it's reference to the table name in the spreadsheet, causing the flow to fail the next time it tries to run.
To avoid the inconvenience of having to adjust your flow each time, you can create a string variable with an appropriate name (e.g. table_name) and insert that variable into the flow where it asks for the table name. Something like this:
Hi @DanielPennacchia , my Flow is replacing a spreadsheet with a new one of the same name, which is why without forcing the table name via a variable the Flow would loose it's connection.
Then I can use the table_name variable in a Get items action.
I don't need to use an expression.