Can I prefilter Business Central OData?

[Anonymous]

Hello everyone,

I have a GL Entry table exposed as a web service in Business Central.

In my Power BI Report I'm getting all the data in the report, my issue is that I'm getting all the data ( 600k+ reports), I want to filter it to an interval of 10 days.

From what I know, if I set a normal filter in Power Query the Power BI will still get all the data and after that will filter the rows.

I want to prefilter it to get only the rows between those 2 dates.

This is what I want to add but I'm not sure how/where.

?$filter=Posting_Date ge 2021-02-10 and Posting_Date lt 2021-02-21

An alternative to this is to create a query that gets those rows and then publish it in the web services page in Business Central but that's not a solution unfortunately 😞 .

Thank you for the time spent here and have a great day!

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please try to use the OData Feed connector to prefilter data. 

And for how to create a OData URL, please refer to: 

Prefilter data for Power BI with OData URLs 

Using Filter Expressions in OData URIs 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[v-kkf-msft]

Hi @Anonymous ，

Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[Anonymous · Accepted solution]

Hello,

Sorry for not replying, I've managed to solve the issue, I only needed to add the filter right after the data source in the advanced editor, in that way Power Query folded the query back to the data source and only what I needed was retrieved.

Thank you for your time and wish you have an awesome day!

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please try to use the OData Feed connector to prefilter data. 

And for how to create a OData URL, please refer to: 

Prefilter data for Power BI with OData URLs 

Using Filter Expressions in OData URIs 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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