Forum Discussion
We cannot convert the value "[List]" to type Table.
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!