Forum Discussion

dmacdonald's avatar
dmacdonald
Icon for Advocate I rankAdvocate I
4 years ago
Solved

Using 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 Bus...
Need Help
  • dmacdonald's avatar
    dmacdonald
    4 years ago

    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"

dmacdonald's avatar
dmacdonald
Icon for Advocate I rankAdvocate I
4 years ago

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's avatar
CanaryBI
Regular Visitor
2 years ago

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.co.uk)) to see if the query is folding, it says that folding is not happening. 

Any help would be appreciated 🙂