Some dataflows stopped working after Business Central updated to v24

[bignadad]

Microsoft recently updated its API's to default to version 2.0.

I have custom API's built in Buisness central SaaS.

Here is the AL code for this example I am having the issue with

Here is my testing in postman.

I can force schemaversion 1.0 in the url and the Type Enum is correct "Put-Away"

When i switch to schemaversion 2.0 you can see I get the unix encoded value for the Type Enum "Put_x002D_away"

The problem is my Power BI dataflow will not allow me to force the schema to 1.0

Here is the error I am getting in Power Query when trying to view my dataflow

---------- Message ----------
DataSource.Error: Dynamics365BusinessCentral: Request failed: The remote server returned an error: (400) Bad Request. ('Put_x002D_away' is not an option. The existing options are:  ,Put-away,Pick,Movement,Invt. Put-away,Invt. Pick,Invt. Movement  CorrelationId:  ce628306-bbf1-4e10-97e6-3f2d42e96f8b.)

In the first step I have tried to replace that "_x002D_" with "-" but it still fails

Table.ReplaceValue(#"Sorted rows", "_x002D_", "-", Replacer.ReplaceText, {"type"})

Here is the advanced editor data

let
  Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, [UseReadOnlyReplica = null, AcceptLanguage = null, ODataMaxPageSize = null, Timeout = #duration(10, 0, 0, 0)]),
  #"Navigation 1" = Source{[Name = "PRODUCTION"]}[Data],
  #"Navigation 2" = #"Navigation 1"{[Name = "*** Machine LLC"]}[Data],
  #"Navigation 3" = #"Navigation 2"{[Name = "Advanced"]}[Data],
  #"Navigation 4" = #"Navigation 3"{[Name = "***/BI/v1.0"]}[Data],
  #"Navigation 5" = #"Navigation 4"{[Name = "registeredWhseActivityHdr", Signature = "table"]}[Data],
  #"Sorted rows" = Table.Sort(#"Navigation 5", {{"systemCreatedAt", Order.Descending}}),
  #"Replaced Unicode dash" = Table.ReplaceValue(#"Sorted rows", "_x002D_", "-", Replacer.ReplaceText, {"type"}),

It seems as though the Power Bi Business Central connector hasn't been updated to accomodate this new schema version.

What's even more odd is I have other dataflows that are still refreshing without errors. They also have these unicode values.

[LennertVDD]

Hey,

Did you find a solution for this issue? We are experiencing exactly the same thing...

[bignadad]

I contacted Microsoft support and was told this

I've found an already opened development ticket which relates to this issue, that's being worked on. Just so you know this development tickets take time to be completed and implemented. 

So the Microsoft development team is aware of this issue, but no fix as of now. It blows my mind how they can update a product and not preemptively fix their own connectors before going live.

For me there were two issues.

1) API page fails every time it tries to sync

2) Unicode characters for all enum fields.

I had one case of the API page failing and had to change from the BC connector to an OData Feed connector. You can use this on any API pages you might have that are failing.

Source = OData.Feed("https://api.businesscentral.dynamics.com/v2.0/*YOUR_DOMAIN*/production/api/*APIPublisher*/*APIGroup*...", null, [Implementation = "2.0"]),

The key to this is being able to force the schemaversion to 1.0. this will fix all the unicode characters.

As far as second issue this will cause problems if you have filters set to those enum values, using them in DAX queries, or relationships. Your reports will break. 

So what I did on a few of my tables was go into PowerQuery and added find/replace for any of those columns.

_x002E_ = .

_x0020_ = 

_x002D_ = -

_x002F_ = /

Luckily I only had to do this for a few of my tables. I hestitated doing too much because this should be fixed in the near future. I only did it for reports that had issues.

I would image when the Microsoft developers fix this they won't give an option to force schemaversion to 1.0 because it will be deprecated. I would hope they allow an option to tell power BI to auto change all the unicode characters. 

[Anonymous]

Hi @bignadad ,

Try creating a custom column in Power Query to handle the conversion instead of replacing the values ​​directly. Here is an example:

#"Added Custom" = Table.AddColumn(#"Sorted rows", "Corrected Type", each Text.Replace([type], "_x002D_", "-"))

Also, you can try writing schemaversion 1.0 here to try to force the conversion.

Building Reports in Power BI Desktop to Display Business Central Data - Business Central | Microsoft...

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bignadad]

How would I write the schema version 1.0 into the Source call? I have tried to insert it in many places, but it doesn't fit anywhere.