Receiving Automated/Scheduled Refresh Error on dataset that works for manually invoked refresh

[DTaraboletti]

An error occurred while processing the data in the dataset.

Refresh failed: 
<<Dataset name>>  has failed to refresh.  
Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

<ccon>Expression.Error: We cannot convert the value <ccon>"[List]"</ccon> to type <ccon>Table</ccon>.. </ccon>. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: <<Tablename>>

Refresh of this dataset/table has worked for manual refresh for weeks.   

I've reviewed multiple forum posts, but not found any suggestions which help to identify the specific root cause.

Any suggestions?

[aj1973]

Hi @DTaraboletti 

Open the report in Power Query and check out the steps. It should identify the failure connection 

[DTaraboletti]

@aj1973 Thanks for your reply.   

Quick Update...

I've logged a support ticket and was informed that the algorithms for desktop vs cloud service refresh are different.   One key difference is that the cloud refresh service caches the table image and then uses that cache to perform subsequent steps in the refresh.  Thus, if you have an Sharepoint list (source) which references other list data... for instance a person lookup or lookup of another sharepoint list column, you must "Expand" ALL such fields before performing a join against another PBI Dataset table.

I've applied corrections to the queries to accommodate this "difference" but its still not working.  

[aj1973]

Not sure what why are you telling me about the cache but you kinda took into another direction.

Try to use 2.0 Beta when connecting to SharePoint list and see

[DTaraboletti · Accepted solution]

@aj1973 

1) I am using 2.0 for Sharepoint data connection

2) to explain the cache reference, consider the following image...

 

When the Sharepoint list is "pulled" into Power BI, fields shown as [list] are unexpanded.   The red circled items above are from Sharepoint Lookup (from other lists in sharepoint) and the green circled ones are from people lookup.

As you no doubt know, to make use of these fields, they must be expanded as follows:

= Table.ExpandListColumn(Source, "Project")

= Table.ExpandRecordColumn(#"Expanded Project", "Project", {"lookupValue"}, {"Project"})

According to the MS support rep... in the cloud refresh service, upon first reference of a table, the service caches the image of the table (as shown above)... so it thinks the values are [List] instead of the expanded values.

Thus, again, according to the rep, the service is unable to do a join against these values because it is trying to join the second table to the first based on a value of [List].   The Desktop refresh does not operate in this manner... thus, the solution is to expand every [List] field before performing a join.

In my dataset, I was doing a "merge as new" and then expanding fields.  (obviously the field that I was trying to do the initial join on was not one of the [List] values... but when I later used expanded the list values, the online service still thinks the value is [List] instead of the expanded value.

[aj1973]

Ok I see, well hiding those columns 'List' doesn't mean they're not included in the refresh, you are just hiding 'em from users. You need to delete them in Power Query and try again.

[DTaraboletti]

Per the Support rep... I have moved ALL expand field steps before I do any joins.

Still not working for cloud refresh...but making baby steps