Scheduled Refresh Fails While Desktop Refresh Succeeds

[regerror6]

I have a new report that queries a SharePoint Online document library (no gateway, single source).  My model has a few tables, but nothing overly complex.  It refreshes without issue in Power BI Desktop, but when I publish it to the service, I get the following error:

"We cannot convert the value null to type Text."

I have a couple data type conversion steps, but I have not been able to isolate the issue.  A ticket with Microsoft has not made any progress, and I am not sure how to troubleshoot this when I cannot recreate the error in Desktop.

Does anyone have any thoughts on how to approach this?  I cannot even determine which query is triggering the error.

On a side note, Microsoft seemed somewhat annoyed that I could not screen screen share confidential data on a recorded Teams conference.  C'mon!

[regerror6]

Alright.  I have some additional insight.  I was able to isolate the query and step causing the problem.  It is a pivot function:

= Table.Pivot(#"Removed Position Columns", List.Distinct(#"Removed Position Columns"[Field]), "Field", "Value")

Again, I confirmed there were no errors in the results from this step or the prior step.  I mocked up some data below with two scenarios that could (maybe?) be causing the issue.  Is there something different in the way the service engine processes this compared to the desktop engine?

[regerror6 · Accepted solution]

I discovered a workaround by adding replace null with "" across all columns (in the step before the pivot).  The only null values were in the Value column, and there were only a handful.  Later in the query, I reversed it by replacing "" with null.

Someone will need to explain to me one day how the service and desktop engines differ.  What a pain.

[collinq]

Hi @regerror6 ,

I have had this problem before as well.  First thing to note is that the Service engine and the Desktop engine are not the same and therefore the Service will sometimes kick out some things that the Desktop does not.  So, the way I have found this in the past when I have lots of columns and/or I don't know where this is from is to create a table visual and put in all of the fields and then do a sort by each one (yes, one at a time, but in the table it is just click and wait).  And then, the "nulls" will be at the top OR, if this error is correct, you will get a field that says "Error" on it.  Painful but sometimes that is the only way.

[regerror6]

Thanks!  I had the same though earlier.  I rechecked the tables using your method and the column quality/profile tools in Power Query, and there were no errors.

I did not menion earlier, but my largest table only has ~200 rows (currently)—fairly low volume.