PowerBI Refresh works on Desktop but query error in service - cannot convert Table to List

[rchamplin]

Hi,

I have a very odd error that seems like it should be straightforward to fix.  My query works properly in PBI desktop, but when I run a scheduled refresh I get the error:

"We cannot convert a value of type Table to type List.. The exception was raised by the IDataReader interface."

My query post-processing is somewhat complex (dealing with SharePoint content makes it necessary), but I have reviewed my m-code a dozen times, and I cannot find anywhere that I try to implicitly (or explicitly) convert a table to a list.  It also runs on PBI desktop without error, so I can't track the issue to the query line item that way.

One last comment: this dataset ran on auto-refresh for several months without any problems before this error appeared.  No updates made to the query, the error just appeared a week or 2 ago.

Any help is greatly appreciated.  Thank you!

[Daryl-Lynch-Bzy]

Hi @rchamplin , I am happy to see that you are using the Implementation="2.0".  The original has caused performance issues for me in the past.  There are a couple of your comments that I want to highlight:

1. many different SharePoint lists into many different queries
2. Person fields and multi-select fields in particular are a nightmare to deal with, between handling NULL cases, splitting out nested lists, lists of tables of records

I like SharePoint list, but I tend to process them one at a time rather that process many at once.  I like the spliting of multiple options, and multiple people and multiple html references into separate tables (but this creates bi-directional relationships).

So, the advice I would like to suggest is to create a Dataflow for each SharePoint List.  The idea is to process the each individual List into a separate Dataflow, and then combine them together in the Power BI Dataset or other Dataflow.  This approach is advantageous because the Dataflows can be scheduled to running in parallel or sequentially (especially if you have Power Automate and Premium Capacity).

This approach can take advantage of paramaters.  So when a new list is created you simple copy all the Queries to a new dataflow and then update the SharePoint Site and List Name parameters.  The new dataflow should be automatically picked up when the Dataset is refreshed.

Consider using the approach below in your Dataflow to create separate tables for the People and Multiple Option columns - this need to happen because the Dataflow cannot save nested objects:

The Import List query is using linked to the SharePoint List.  The other queries then reference.  So when I have People or multiple fields, I process them into separate tables that are related useing SharePoint ID.  Query Form is 1 for 1 without any of the nested tables, list or records, but the other tables could be 1 to Many as the nest objects are split.

Unfortunately, this approach will struggle with Many lists because of duplicate ID.  You need to add a large number to create uniquie ID for each list.  I would suggest adding a number ID field. 

Eg. List A 1 * 1000000 + ID, List B 2 * 1000000  + ID, List C 3 * 1000000 + ID ... List N n * 1000000  + ID

[rchamplin]

This is a cleaner way to do it than mine.  Thanks for the insight!  The original issue started showing up in PBI Desktop so I was able to easily debug it and find where my mistake was.  This feels like a Microsoft issue: I understand that the processing is necessarily a bit different between server and desktop versions and things won't always be perfectly in sync, but it should be straightforward to provide real debugging information in a failed dataset refresh.

[Daryl-Lynch-Bzy]

Hi @rchamplin - can you please provide the details for the SharePoint connector - just replace the URL with #"URL".

[rchamplin]

Hi @Daryl-Lynch-Bzy   Thanks for the response!  Sorry for my ignorance, but are you referring to the m code?  I couldn't find any other information on the connector itself (version, etc.).  The SharePoint connector code is straightforward:  

= SharePoint.Tables("URL", [Implementation="2.0", ViewMode="All"])

If you are talking about the full query, I am pulling things together from many different SharePoint lists into many different queries - a couple hundred lines of m code (Person fields and multi-select fields in particular are a nightmare to deal with, between handling NULL cases, splitting out nested lists, lists of tables of records, etc.).  My next step is to go through the elimination process to see which query is actually causing the problem, but it's time consuming.  If I can't figure it out from there I will post the offending query in its entirety for help 🙂

I suppose I was hoping someone could point to some flaw in the way that PowerBI translates the desktop query into an online query that causes this type of problem.