Forum Discussion
Advocate IUsing the Dynamics Business Central Connector, how can I add date filters?
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
______________________________________________________________________________________
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"
10 Replies
- v-angzheng-msft
Community Support
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 ZhengIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dmacdonaldI 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"
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:
letOrigen = 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.co.uk)) 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
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 ZhengIf 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.
BMG001Helper I
#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
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
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.
