Web service refresh failing with: We cannot convert the value null to type List..

[mike_honey]

I have two datasets with very similar query designs. Both have been refreshing reliably for weeks (several times/day) in a Pro workspace, then both started failing from 11-Dec-2021, 03:04:16 (GMT+11), with the same error, eg:

Data source error:	We cannot convert the value null to type List.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action.
Cluster URI:	WABI-WEST-US-redirect.analysis.windows.net
Activity ID:	783bf20b-db3e-4c6e-a30f-d9e83a9cc39d
Request ID:	55f77681-d0e6-4a83-af9b-9ef51174bf62
Time:	2021-12-10 16:47:31Z

I can refresh and publish both reports using Power BI Desktop.  Data sources are SharePoint Folders, SharePoint Lists and Dataverse tables.

Any help or tips are appreciated.

[v-robertq-msft]

Hi,

According to my research, a similar issue has been reported internally. ICM: 263171514

In the latest update of this ICM, this issue has been fixed, and the fixed workaround is:

After narrowing the M Queries to the one that is used for the table conversion, it started to work.

it may be related with some internal fix or some data quality that we did not spot.

If you still have a problem, I suggest you to open a support ticket to get the direct help from the technical support team of Microsoft.

https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/6830...

https://powerbi.microsoft.com/en-us/support/

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

[mike_honey]

Turns out this was a harbinger - once I updated Power BI Desktop to the December 2021 update, it started throwing the same errors and I was able to use the normal methods (Query Preview) to isolate the problem. 

FWIW something has changed in the evaluation of columns of type: List.  Here's the code I was running that worked fine multiple times a day for over a year:

if [Market] = "" then "" else Text.Combine(List.Sort(List.Transform([Market], Text.From)), ", ")

Here's what I had to patch it to, to avoid that error:

if [Market] = null then "" else if [Market] = "" then "" else Text.Combine(List.Sort(List.Transform([Market], Text.From)), ", ")

@v-robertq-msft - thanks for trying, but I didn't understand what to do with your reply.