intermittent refresh errors - under further investigation by user

[ericleigh007]

Hello,

{I want to put this one on hold for a bit until I do some other investigation.  This could be related to our complex methods that all have to be successful before the Power BI refresh happens.  i'll remove this edit when I'm sure our comlexities don't contribute to this{

PowerBI and the service is awesome.   Our company loves to have constantly refreshed information ready at our fingertips.  We also count on that information to be taken care of without intervention most of the time.  We do understand there are sometimes glitches, but we count on the problems to be few and far between.

We use mainly Sharepoint Lists (for smaller snippets of information like Employee lists), and SQL Azure databases (for larger data like Accounting data).

Towards the end of overall reliability, we unfortunately have intermittent errors with the data refresh.

The most common was an error where the ID column that the service wanted to rename to ID.1, didn't exist.  Unfortunately, the default setting is used in the Power Query, so if that field doesn't exist,, the refresh fails.  The error was:

The column 'ID' of the table wasn't found.

We are today seeing another refresh error where, in accessing in using Sharepoint lists as input, we get an intermittent missing field.  I can only guess that in removing 'all but' fields, the software actually makes a list of all fields, and then it creates a command to remove fields, leaving out the fields that the user wished to keep.  

The missing field errors today were:

The column 'FileSystemObjectType' of the table wasn't found.

and

The column 'GUID' of the table wasn't found.

One suggestion for the team in removing fields and renaming fields might be to use the non-default MissingField.Ignore option in the template PQML code that gets created based on the user's GUI input.   That's what I did for the first problem, and I'll do soemthing similar for the new ones.

But WHY is the service not consistent in the first place?  If I refresh manually, things seem to work nicely, but on the scheduled nightly refresh, problems exist, and having to detect, than fix them is contrary to my tenet of a set-and-forget service.

As a test, this morning I was greeted with a refresh error:

The column 'FileSystemObjectType' of the table wasn't found.

All I did was do a manual refresh, WITHOUT CHANGING ANY DATA.   

The refresh succeeded.

One complexity I can think of is that both reports use sharepoint lists from our Office 365 subscription.  Perhaps a problem because more than one report is being refreshed at a time?

Anything I can do to help, I'm available.

-e

[v-sihou-msft]

@ericleigh007

It's quite strange, since you can refresh it successfully via manual. I suggest you create a support ticket for deep analysis.

Regards,