Sheduled updates fail... but manual succed

[TorbenL]

Hi all,

For some reason all my scheduled update fails... but when I try to update manually it succed. I guess all data connections must be valid since I am able to update manually without any troubles. But why does it fail constantly when sheduled?

Background information:

• I have 6 reports stored in OneDrive for Business (generated in the desktop and loaded in the Service from OneDrive)
• I've scheduled one daily refresh pr. report... and they all fails
• All reports draw on the same data
  • SQL server on AWS
  • Website data from Google Anaytics
  • Simple Excel-files stored on OneDrive for Business
  • One Excel file with two Power Queries, which loads data form two CSV-files also stored on OneDrive for Business. The Queries loads data into Excel tables - not into the datamodel.
  • All credentials and data gateways are working fine

The error message points towards the single Excel-file with the Power Query... but there is no problem updating this file through manual refresh. Have any of you experienced the same issue? Any ideas for solutions or work-a-rounds?

I really enjoy setting up reports in the Desktop... but I must admit that scheduling a stable update in the service is close to a nightmare 😞

I have attashed a few visuals below:

Error messageScheduled updates fails... manual succedsOneDrive updates succeds

Best regards,

Torben

[v-caliao-msft]

Hi @TorbenL,

For local file data source, we need to install personal gateway to enable scheduled refresh. Besides, scheduled refresh cannot occur if it’s asleep. Please be sure to set your scheduled refresh times for when the laptop will be running. 

Reference
https://powerbi.microsoft.com/en-us/documentation/powerbi-personal-gateway/

Regards,

Charlie Liao

[TorbenL]

Hi @v-caliao-msft,

Thank you for your reply.

I am running the scheduled updates through the personal gateway (installed on a virtuel server), and it indicates to be running fine. The server has been running non stop. I tried to un- and re-installing the personal gateway yesterday, but it did not solve the issue. It was allready the latest update.

For some reason the first scheduled update came through once after re-installing the gateway... but now all scheduled updates fails again 😞 They error messages still point at the Excel-file with the power query within.

I can't quite grasp why the manuel update passes fine through, but the scheduled update fails. Could it be caused by something in the Excel-file?

Best,

Torben

Gateway is running fine

First Scheduled update succeded... but the next fails again

[TorbenL · Accepted solution]

Hi again,

I ended up opening a support ticket in order to narrow down the core reason for the issue. And the great guys at Microsoft provided a solution, which seems to have addressed and solved the issue. It was caused by incorrect date values in the data source.

Please find the response from Microsoft Suppert Team below:

----------------------------------------------------

   According to the error message you mentioned, it can be caused by that there are incorrect date values in data source.

   In your scenario, you will need to correct these data values to make schedule refresh work properly.

   Can you please follow the below steps :

     1. Go to the query designer

     2. Transform the Date field(s) to a number field

     3. Add another step in the Query editor

     4. Set the number field back to the desired Date field.

-----------------------------------------------------------

I implemented these steps in the two queries, which loaded data from the CSV-files the error message highlighted => problem solved 🙂 All scheduled refreshes has completed fine afterwards.

For reference please find a screen dump of my implementation below + the two extra code lines from the Advanced Editor:

Best,

Torben

Solution to my issue...

The two extra code lines added in the Advanced Editor:

    #"Changed DateTime col to Number" = Table.TransformColumnTypes(#"Changed Type",{{"[Order Detail Start]", Int64.Type}, {"[Order Detail End]", Int64.Type}}),
    #"Changed Number back to DateTime" = Table.TransformColumnTypes(#"Changed DateTime col to Number",{{"[Order Detail End]", type datetime}, {"[Order Detail Start]", type datetime}}),