Update semantic model extension busines central

[Syndicate_Admin]

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.

[nilendraFabric]

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]

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

[Anonymous]

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.

[Syndicate_Admin]

It doesn't let me download the pbix, maybe it's not uploaded correctly? thank you