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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dawidvh
Helper I
Helper I

We cannot convert the value "[List]" to type Table.

Hi all,

 

I am getting the below error in the service when I try and do a refresh. I have stripped the query and can track it to a merge of data from a Web API to SQL. This happens before the data is expanded, and just the merge seems to cause this error, but only when refreshed on the service. This works without a problem in the desktop.

 

We cannot convert the value "[List]" to type Table.. The exception was raised by the IDataReader interface. Please review the error

message and provider documentation for further information and corrective action.

3 REPLIES 3
ThePowerBiCpl
New Member

I had this issue. When i clicked 'refresh all' in power bi desktop, powerbi pretended everything was great and gave me no errors, despite the power bi service throwing this error.  Only when i opened up power query editor and started clicking through each applied step on each datasource did i find a yellow hazard warning with the same error.  In my case, here is what happened and how i fixedit:

  • I was doing a lookup from a sharepoint list to another datasource to bring a value into the table. 
  • After the lookup, i was expanding rows in sharepoint to get to a metadata field. These are parsed like -> List, Record
  • When i built this, it all worked as intended. But later, i moved my excel datasource that i was looking up the value in from my desktop to sharepoint (eg i was bringing a value from excel file into my sharepoint list datasource). Apparently power bi treats a file in sharepoint differently than a file on your laptop. The lookup turned the List i was expanding into the string "List" (I didnt change the data type, but somehow my lookup did). 
  • To correct, I changed the order of the applied steps. I did my row expansion to get the metadata early on in the applied steps (when it was still an actual list), then I did my lookup at the end, which still transforms all Lists into the string "List"

Moral of the story for my case, If you try to perform a lookup to an excel file thats in sharepoint, it will transform all  your [List]'s in your sharepoint list datasource into the string list! And then Power Bi desktop wont be kind enough to throw you an error when you refresh the data, but Power Bi service WILL throw an error and cause your refreshes to stop working. 

 

I hope this helps someone!

 

 

BInovice
Frequent Visitor

Had exact same problem. Pinpoint the issue to append query from 2 sources. No issues if I separate them, but it will error out whenever I tried to merge them in power query. Was able to get around by using UNION to generate final table

v-kkf-msft
Community Support
Community Support

Hi @dawidvh ,

Please try the following.

  • Tried setting perms to Organizational for all of them.
  • Removing from the Power BI Service and republishing did nothing.
  • If you have a list that has some information in nested Lists, expanding these Lists in the source query.

For more details, please refer to this post.
https://community.powerbi.com/t5/Service/We-cannot-convert-the-value-quot-Table-quot-to-type-Table/m...

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors