Using the Dynamics Business Central Connector, how can I add date filters?

[dmacdonald]

The Business central connector is great, but all the data returned is current.

When doing financial reports, I need to look at months.

For example, I need the trial balance for January.

In the Business central API you add datefilters 

"trialBalance?$filter=dateFilter ge 2022-01-01 and dateFilter le 2022-01-31"

I could not find any documentation on the 

Dynamics365BusinessCentral.ApiContents function used

Is there a way to add datefilters?

FYI:

The PowerQuery generated looks like this

_______________________________________________________

let

Source = Dynamics365BusinessCentral.ApiContents(null, null, null),
PRODUCTION = Source{[Name="PRODUCTION"]}[Data],
#"XX LLC" = PRODUCTION{[Name="XX LLC"]}[Data],
v2.0 = #"XX LLC"{[Name="v2.0"]}[Data],
trialBalances_table = v2.0{[Name="trialBalances",Signature="table"]}[Data]
in
trialBalances_table

______________________________________________________________________________________

[BMG001]

#The marked solution:
This only filters after the table is loaded in to Power Query, so no query folding to the API. 

Think the only way to do this is to talk directly to the BC API and not use the Connector from MS, but need to test this..

[dmacdonald]

That's correct. 

Powerbi and multiple companies pulls so much data that it can take hours. 

There is no good way to use powerBI and dynamics together when multiple companies are involved. 

There

have been baby steps over the years to improve this. But it's a dead end to use these technologies together with multiple companies. 

it's too bad. The fix should be simple. 

[dmacdonald]

Getting data out of Business Central into PowerBI is difficult.

We have multiple small companies in our case.

Getting an update involved pulling huge amounts of data and an update can take 45 minutes.

And the first thing that is done is to filter it down.

It's remarkable that it has not been addressed to date.

I think the integration partner community is focussed on finance only and BI is at best an after thought.

[dmacdonald · Accepted solution]

I figured it out.

The Business Central connector using the Power BI Odata connector, and the authorizaton is built in.

So the doucumentation I was looking for is the in the Odata connector.

The Odata Connector is well designed and folds and filters back to the Odata source.

So the solution to my problem was dead simple, just add a filter to the query and you get the period you want.

Here's a sample of the M code.

It was all created using the GUI, no advaced editor required.

Chose the income statements table, then add a date filter with the date you want the data for.

let

    Source = Dynamics365BusinessCentral.ApiContents(null, null, null),

    PRODUCTION = Source{[Name="PRODUCTION"]}[Data],

    #"DC Hospitality Apex LLC" = PRODUCTION{[Name="Test Company 1"]}[Data],

    v2.0 = #" Test Company 1"{[Name="v2.0"]}[Data],

    incomeStatements_table = v2.0{[Name="incomeStatements",Signature="table"]}[Data],

    #"Filtered Rows" = Table.SelectRows(incomeStatements_table, each [dateFilter] = #date(2022, 1, 31))

in

    #"Filtered Rows"

[CanaryBI]

Does it really folds doing that? I have a query that only extracts data from Business Central into a dataflow. The query consists just in navigating to the desired service in BC and selecting some columns:

let

    Origen = Dynamics365BusinessCentral.ApiContentsWithOptions(Environment, Enterprise, "WebServices", [UseReadOnlyReplica = true]),

    Navigation = Origen{[Name = "TableName", Signature = "table"]}[Data],

    #"Columnas quitadas" = Table.RemoveColumns(Navigation, {"SomeColumn1", "SomeColumn2"})

in

    #"Columnas quitadas"

When I apply this method (Chris Webb's BI Blog: Another Way To Check Query Folding In Power BI/Power Query M Code (crossjoin.c...) to see if the query is folding, it says that folding is not happening. 

Any help would be appreciated 🙂

[dmacdonald]

Thanks.

I already have this working with the V1.0 API.

It seems that the new connector performs much better.

And I'd like to use it.

But I can't find any documentation on it whatsover. It supplies access to the 2.0 API, but filters are a critical part of using it becuase most quesries require a specific date range so you can look at a results at the end of an accounting period.

If someone could point to documentation on the syntax of

new Dynamics365BusinessCentral.Api()

that would really help.

[v-angzheng-msft]

Hi, @dmacdonald 

Here are some resources related to the API:

Welcome to the API(v1.0) for Business Central

Welcome to the API(v2.0) for Business Central

Standard API for Dynamics 365 Business Central SaaS (Get, Post, Patch, Delete and Basic query parame...

Getting Started Developing Connect Apps for Dynamics 365 Business Central

Welcome to the API(Beta) for Dynamics 365 Business Central

Enabling the APIs for Dynamics 365 Business Central

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[dmacdonald]

Thanks.

I'm using the API already. But I did some testing using the newly released PowerBI connector.

And that seems to peform much faster. I'm pulling data from over 40 companies, so it takes a while using the API.

But it's useless without the ability to filter by accounting periods.

And there is no documentation for it so it might already do that.

I'm just going to give up for now. Hopefully MS will provide some documentation in the future and it will be useful for financial reporting when that happens.

[AboodAlhamwi]

Hi @dmacdonald 

Were you able to achieve filtering the period not by specific dates? 

[v-angzheng-msft]

Hi, @dmacdonald 

Here are some references that may be helpful:

Power BI with Dynamics 365 Business Central

Dynamics 365 Business Central - How to Connect Dynamics 365 Business Central to Microsoft Power BI

How to create and embed a Power BI report into Dynamics 365 Business Central

Best Regards,
Community Support Team _ Zeon Zheng

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