Filter data from the Business Central source before retrieving it

[jashjacob]

My company has many reports with MS Business Central as a source. After a recent update to Business Central, many of the reports are failing. The cause of this is some specific dataset in the Business Central database. This error prevents the upload from counting more data. One way I think I can solve this is by filtering before or while the data is captured from the server. In SQL, you could have used a select statement as the source.

Is there a way to do this for a Business Central source?

[parry2k]

@jashjacob what is actual error? Can you filter it in power query? Without knowing full details, hard to say what is the appropriate solution.

[jashjacob]

@parry2k

Thanks a lot for the response.

OLE DB or ODBC error: [DataSource.NotFound] Dynamics365BusinessCentral: Request failed (404): The remote server returned an error: (404) Not Found. (The [Table Name] does not exist. Identification fields and values: [ID Column]='0'  CorrelationId:  b76ed4a9-6627-477b-985a-5f5a3d491c8c.).

I replaced the original table name and column name. As per analysis by the BC team, the issue is caused because the field that shows 0 should actually not have 0. Now, there are teams trying to identify and fix the issues, but since we just closed a month, some of the reports are critical and is being stuck because of this issue. I am trying to find a workaround.

I tried adding a filter in advanced editor, however, I am not sure how to add this at source or navigation stage of a Business Central connection. I do not have much experience working with Business Central or Dynamics 365. This needs to be done in the source or navigation because it fails in the navigation stage. 

EDIT: I need to add, the table name that is shown in the error message is not the one I am trying to add. I think there is a relation between the table I am trying to add and the one in the error message at the backend, not created by me.

[parry2k]

@jashjacob I don't know much about BC, but seems like the table is completely removed or changed and Power Query now cannot see that table, correct?

[jashjacob]

That's the thing. The table I am trying to retrieve is visible. My guess is, in business central we have something like a view on top of actual tables. This probably is one of the underlying tables that is causing the issue.

Even I don't know enough about Business Central to answer further. So, looks like I have to wait till they are able to solve the issue. 

[parry2k]

@jashjacob bottom line is if the underline data table is not available regardless what is the source, you cannot do much on Power bI side, let's you are connected to CSV file and that CSV file is renamed or not available, you either have to bring that CSV file back or change to the new file. So in this case, if the underline table is not available, not sure you can do much until it is fixed.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!