Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to Solution.
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.
Open the report in Power Query and check out the steps. It should identify the failure connection
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
@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
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.