PowerBi - Source Excel File @ One Drive - Returning zeros on every other Scheduled refresh issue

[pointer_pbi]

Hello PBI Community,

Hope someone can help me with the issue I'm about to describe.

Setup:

- PowerBI Desktp Pulling Excel File from OneDrive

- Excel file at OneDrive has daily, hour, minute, manual inputs from 08:00 - 22:00 hrs (live document)

- Some of the Scheduled refresh work ok, but others show me zero values (image1 attached), even if the Excel file has other value than 0's. And Scheduled refresh runs w/o errors, this reviewed at Dataset > Refresh History. 🤔

- Scheduled Refresh runs at 09:00 - 11:00 - 13:00 - 15:00 - 16:00 - 17:00 - 18:00 - 19:30 (originally 19:00, but the error was showing up at this time of refresh so moved it to 19:30) 

****** Million Dollar Question is why Scheduled refresh fetches, grabs or shows all of the information and why sometimes not? and How can this be fixed *****

Extra Notes/Diags:

I've made some tests on a separate Excel local and OneDrive File, such as, filtering the Excel file(does not alters the whole data to be shown in PBI), saving and refreshing at the same time and same no issue, I can't recreate the issue🙃.

I would mean the world to me if I get some support/guidance to have possible solution(s).

** Already searched google and PBI community with no luck.

** The issue above only happens with PBI on Web Workspace, not on desktop version.

Green markings - Are Ok

Red marking - 0's

[V-lianl-msft]

Hi @pointer_pbi ,

How do you connect to Excel files in onedrive?Excel?web?

If use excel connetor, onedrive refresh is different as you thought. It only trigger when that source file(pbix) updated. It should be the reason you mentioned why it not refresh when data source(csv files) updated.

In this case, you need to configure a gateway and refresh with the basic scheduled refresh.

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-desktop-file-onedrive 

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

[pointer_pbi]

Hello @V-lianl-msft ,

I got connected linking the file copying the web address (File > Info > Copy path and delete the ..web... on the address) of the file into PowerBI Desktop, this way it works great on Desktop Version with no 0's showing, everything happens when is online.

I followed the steps to get connected the way described above and have organization credentials logged to connect to the OneDrive excel file via Web credentials and per same documentation stated that no Gateway is needed if connected with Organizational credentials.

What I've done is:

1. I've made a copy of the file to another folder and copied/renamed the original pbix with this new source.

2. Scheduled automated task to run every hour at the 50'th minute to copy the excel file at OneDrive original folder to the New folder (where no one has acces to).

3. Scheduled the dataset scheduled refresh to every 00 min, I'm testing this possible solution during the weekend and next week.

Cross my fingers this work although is not the best or most efficient solution. But my guts say that this happens when the live(original) file is being saved when it shows 0's.

[pranit828]

HI @pointer_pbi 

Can you confirm your scheduled refreshes are all completed and none of them have failed.

Even if everything is right the schedule refresh can fail.

Can you post a screen shot of the refresh schedul from PBI service.

If all refresh says complete then...

There is no other explanation other than your source on OneDrive has partial data when you refresh schedule refresh is executed.

[pointer_pbi]

Hello @pranit828 , thanks for your reply.

Attached is the scheduled refresh, without any fail shown as yours, been there an is because an extra column is added or renamed commonly on my end/experience.

On your last comment, how come at the time it fails to show all data, who does it shows it complete after a manual refresh? this is what I can't conceive in my head.

[pranit828]

HI @pointer_pbi 

Please share one drive refresh schedule

[pointer_pbi]

Hello @pranit828 ,

Everyone of them show as complete, and none has error msg.