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

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.