cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter data from the Business Central source before retrieving it

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?

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: Filter data from the Business Central source before retrieving it

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Frequent Visitor

Re: Filter data from the Business Central source before retrieving it

@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.

Highlighted
Super User IV
Super User IV

Re: Filter data from the Business Central source before retrieving it

@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?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Frequent Visitor

Re: Filter data from the Business Central source before retrieving it

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. 

Highlighted
Super User IV
Super User IV

Re: Filter data from the Business Central source before retrieving it

@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!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors