undefinedPower BI error NULL

[keinshah]

Hello community,

I have my data source as MS business central. When I refresh on desktop, it works fine without any errors. However, when scheduled refresh or manual refresh online, it returns the below error.

Data source error: Expression.Error: The OData connector failed with the following error: We cannot convert the value null to type Record... . The exception was raised by the IDbCommand interface. Table: LotNoInformationList.
Cluster URI: WABI-UK-SOUTH-B-PRIMARY-redirect.analysis.windows.net
Activity ID: 50d92772-e7ba-4953-8dd1-dd60c4ec3044
Request ID: d9f0b6f9-b3c6-1882-5d96-93f34fb03110
Time: 2024-09-03 15:35:47Z

Similar error on all my reports with various datasets. This triggered today morning onwards.

I did try to remove the 'null' from the dataset, but still the error persists.

Any tips on how to resolve it?

Many thanks.

[Dejan-Pajk]

The latest version of the connector no longer permits the 4th parameter to be null.

Please try the following solution:

Before:
Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null)

After:
Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, [UseReadOnlyReplica = null])

[keinshah]

Hi Dejan,

I am getting the below error on the same data set starting today. 

OLE DB or ODBC error: [DataSource.Error] OData: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host..

Any idea how to solve this?

[pdolan_tapco]

This means the timeout on the server that you are pulling from is two small or the dataset you are pulling is too large, either limit your query or have your DBA extend the timeout on the server you are pulling from. Or Limit the Number of records and paginate if this is BusinessCentral

[Sailor1000]

Hi Dejan-Pajk
It solved my problem. 
A lot of work know updating all the queries. 
Do you think Microsoft will fix this? Becouse I created a new dataset for tests, and got the same problem. 
Thank you!

[Hittheroof24]

I've had the same happen to me today - absolute nightmare! 

Opened a ticket with Microsoft and they suggested the following (neither of which has resolved the issue) 

- Are you utilizing the latest version of Power BI Desktop to ensure compatibility and feature support?

- The dataset might contain null or empty values that the OData connector cannot process as a Record type. So, are there any null or empty values in the source data? If yes, try to remove these values.

So currently manually updating 30+ reports in preparation for tomorrow morning...

[Dejan-Pajk · Accepted solution]

The latest version of the connector no longer permits the 4th parameter to be null.

Please try the following solution:

Before:
Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null)

After:
Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, [UseReadOnlyReplica = null])

[Monolithe85]

And what was the reason exactly for this? I mean, it haven't been a issue for years and now they suddenly decided this.

Also, thank you so much Dejan-Pajk!

[brianofinch]

Worked beautifully!  Thanks so much for the fix. 

[Rameritz2]

Dejan-Pajk-

What if there are no parameters set on any of the tables and there error still pops up?

[pdolan_tapco]

Where is the lastest version documentation that tells me this?

[ecrins]

Hi,

Thanks for the suggested solutions.

null, null, null , [userReadOnlyReplica = null

seems to work,

null, null 

Doesn't work with my reports.

Any idea why this connector update/ change wasn't communicated? It has a large effect.

Now I have to update alot of tables in alot of reports.

Any idea if there will be another update to fix this?

Or does anyone have a hint on how to change this in a way other thatn opening all tables from all affected reports in the advanced editor and change the first few lines?
(I'll make a base table with the route to the database and link to this table in all the other tables, like a parameter.
So that when another update is required I only have to change one table)

[pa1s]

Hello Dejan,

Thank you for the solution. The original problem for me is the below two error codes:

1) Data source error: Expression.Error: The OData connector failed with the following error: We cannot convert the value null to type Record... . The exception was raised by the IDbCommand interface. Table: TableName.

2) Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

<ccon>Expression.Error: The OData connector failed with the following error: We cannot convert the value null to type Record... </ccon>. The exception was raised by the IDbCommand interface. Table: TableName.

Since, I have the following:

OData.Feed("https://XXXX.dynamics.com/data/", null, [Implementation="2.0"])

XXXX = Companyname

I was trying to replace null with [UseReadOnlyReplica = null] and it is giving below error.

Expression.Error: OData: The header with name 'UseReadOnlyReplica' has a value type 'Null' that is invalid. Only DateTime, Logical, Number, and Text are supported.

How should we handle this? Could you please help?

Thank You and Regards,

Pavan.

[Dejan-Pajk]

Hi Pavan,

The solution mentioned above applies only to the Power BI connector (Dynamics365BusinessCentral.ApiContentsWithOptions), not to OData.Feed.

Therefore, replacing null will not resolve the issue with the OData.Feed connector.

You can find more documentation about OData here:
https://learn.microsoft.com/en-us/power-query/connectors/odata-feed 

Below is an example that connects to Business Central web services:

OData.Feed("https://api.businesscentral.dynamics.com/v2.0/11111111-1111-1111-1111-111111111111/Sandbox/ODataV4/C... Name')", null, [Implementation = "2.0"])

(replace 11111111-1111-1111-1111-111111111111 & Company Name)

Best regards,
Dejan

[pa1s]

Thank you very Dejan, will try that

[Shaishavm]

Thanks Dejan-Pajk, I use webservices from Business Central with following code.

Appreciate if you could suggest a resolution on this?

Source = Dynamics365BusinessCentral.EnvironmentContents(null, null)

Thanks and Regards,

SM

[Shaishavm]

Hi @Dejan-Pajk thanks a ton for the solution. Work-around is working fine. I also noticed that Microsoft has released an incident request acknowledging the bug and that they are working on a fix.  

[Xiaoying]

It works! Thank you Dejan-Pajk!

[Prasooth]

Thanks Dejan-Pajk,

We got this error from 08/09/2024 1 am onwards, after researching hours, I finally found your solution, and it fixed our problem.

Many Thanks,

Prasoothan

[gvol10110]

are things like this published anywhere? why do we need to find out about this thru research? 

www.navisiontech.com  

[Hittheroof24]

I couldn't find anything. In fact, I had opened a ticket with Microsoft Support and I had to tell them the solution!

[keinshah]

This worked. Thank you so much.