Forum Discussion
Receiving Automated/Scheduled Refresh Error on dataset that works for manually invoked refresh
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.
Hi DTaraboletti
Open the report in Power Query and check out the steps. It should identify the failure connection
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.
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
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.
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.