Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DTaraboletti
Helper I
Helper I

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

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?

1 ACCEPTED SOLUTION

@aj1973 

1) I am using 2.0 for Sharepoint data connection

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

DTaraboletti_1-1695039010204.png 

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.

 

 

View solution in original post

6 REPLIES 6
aj1973
Community Champion
Community Champion

Hi @DTaraboletti 

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

@aj1973 

1) I am using 2.0 for Sharepoint data connection

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

DTaraboletti_1-1695039010204.png 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors