Forum Discussion

DanielPennacchia's avatar
DanielPennacchia
Copper Contributor
Apr 17, 2022

PowerBI data refresh from script returning #VALUE! error

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. 

  • jonlake's avatar
    jonlake
    Iron Contributor

    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:

     

    • DanielPennacchia's avatar
      DanielPennacchia
      Copper Contributor

      Hi jonlake 

      Playing around a bit more I think Excel online doesn't like the data it's getting from PowerBI, I will give your advice a go though,

      I tried entering the table name as an expression which tells me it's invalid, would you mind sharing the full expression you're using for this please.

      Regards

      • jonlake's avatar
        jonlake
        Iron Contributor

        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.

Resources