Scheduled Refresh From Excel Online Zeroes Issue

[CoG_Analytics]

I am quite confused by this issue. I have a Scheduled Refresh that runs at midnight, pulling sales data from an Excel Online file saved to SharePoint. The problem is, every morning, when I check my report, many values are just 0 (as if they were overwritten). I get no errors and other data is updating from that spreadsheet correctly. My current fix for the problem is to manually refresh the data model every morning. I do not need to do anything else, and everything works just fine after that.

The sheet being loaded is a table, and my current guess is that the issue is related to vlookups that I have to a second sheet in the same file (since those columns seem to become 0's), but I'm not sure if that's the entirety of the problem, and I definitely don't know why the Scheduled Refresh generates a different result than the manual refresh. Any assistance would be greatly appreciated.

[CoG_Analytics]

Thanks for reaching out! That is a decent thought, but unfortunately not. The data is static, except for the second sheet (which the vlookups reference), and that second sheet is also overwritten on a daily basis by me and only me. There is never a time when the data in the excel workbook is $0.

[v-yajiewan-msft · Accepted solution]

Hi @CoG_Analytics , GilbertQ , thank you for your prompt reply!

Consider using M language in power query or Dax in power bi to replace vlookup in excel to compare the result.

More information for your reference:
Merge queries overview - Power Query | Microsoft Learn
RELATED function (DAX) - DAX | Microsoft Learn

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CoG_Analytics]

Thank you! While the heart of my question remains unanswered (i.e. I do not understand the root cause of the issue), pulling in all raw data and using Power Query is sufficient to resolve the issue at this point.

[GilbertQ]

Hi @CoG_Analytics 

Could it be that you have got some data that relies on the local date time? If this is the case, all the power BI servers are in UTC, so if you are refreshing at midnight your time, it might not be midnight or the next day. And that is why your refresh happens successfully when you do it manually, try and schedule the refresh for after Midnight UTC and see if that works.