Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
1 year ago

Update semantic model extension busines central

Hello

I'm having a problem updating my semantic model with the gateway over a connection to Microsoft Business Central. After having an issue with Microsoft, they came to the following conclusion:

"Looking at your query, I think the problem is that Power BI is getting confused by the way they perform cross-company queries and therefore generates a lot of OData queries to BC, which in turn causes Power BI (or data flow) to run out of computational resources.

To avoid this, they should try to merge data between companies AFTER performing all Table.ExpandTableColumn operations."

And then they give me the following instructions:

"Create a new function that gets all BC companies in the desired environment, for example, GetCompanies(), which you just need to call:

Table.SelectColumns(Dynamics365BusinessCentral.ApiContentsWithOptions("PRODUCTION", null, null, null), {"Name"})Remove your GetBCData function and instead change make your entire query a function that takes a CompanyName parameter and change the first two likes to something like:

(CompanyName as text) => let Source = Dynamics365BusinessCentral.ApiContentsWithOptions("PRODUCTION",CompanyName,"TEKenable/Ses/v2.0", [UseReadOnlyReplica = true, AcceptLanguage = null, Timeout = Duration.From("00:15:00"), ODataMaxPageSize = null]){[Name = "purchaseOrders"]}[Data],Make a new query that calls GetCompanies, and then calls the newly created query for each company and merges the results"

The thing is that I don't understand very well what they indicate, could someone guide me a little more finely to do what they say?

Thank you.

4 Replies

  • nilendraFabric's avatar
    nilendraFabric
    Icon for Super User rankSuper User
    1 year ago

    Hello Syndicate_Admin 

     

    Microsoft is recommending you to restructuring your Power BI data integration with Business Central using this strategy: process company-specific data first, then combine results.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      1 year ago

      Yes, I already understand that, what would not be to implement it through each query in Power BI Query

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Thanks for the reply from nilendraFabric , please allow me to provide another insight:

        Hi,  Syndicate_Admin 
        Thanks for reaching out to the Microsoft fabric community forum.

        Regarding your statement, my understanding is that you're unsure how to create a function in Power Query:

        1.First, you need a new blank query.

        2.Then, go into the Advanced Editor and modify it as follows:

         

        let
    GetCompanies = () => 
        Table.SelectColumns(
            Dynamics365BusinessCentral.ApiContentsWithOptions("PRODUCTION", null, null, null), 
            {"Name"}
        )
in
    GetCompanies
        let
    GetPurchaseOrders = (CompanyName as text) => 
    let 
        Source = Dynamics365BusinessCentral.ApiContentsWithOptions(
            "PRODUCTION",
            CompanyName,
            "TEKenable/Ses/v2.0", 
            [
                UseReadOnlyReplica = true, 
                AcceptLanguage = null, 
                Timeout = Duration.From("00:15:00"), 
                ODataMaxPageSize = null
            ]
        ){[Name = "purchaseOrders"]}[Data]
    in
        Source
in
    GetPurchaseOrders

         

        For the rest, you can continue to try based on his explanation.

         

        3.For more details, you can refer to my PBIX file.

         

        Of course, this is only based on my understanding of how to match your actual data, or you need to continue to communicate with Microsoft staff, after all, we do not know your data structure and data source information.

         

        Best Regards,

        Leroy Lu

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