Web v2 connection with Service Principal authenticated successfully but won't appear in semantic mod

[tbrasch72]

I have a Power BI semantic model that uses Web.Contents in M to query the Microsoft Business Central cloud API. I need unattended scheduled refresh using a Service Principal.

I created a Cloud Web v2 connection with Service Principal authentication against https://api.businesscentral.dynamics.com/. The connection authenticates successfully.

However, when I go to the semantic model settings to map data sources, the Web v2 connection does not appear in the dropdown for any of the detected data sources.

My understanding is that Web.Contents in M publishes as a Web v1 data source type in the service, which would explain why the Web v2 connection is not offered as a match — but I'm not sure if that's correct, or if there's a configuration step I'm missing.

Note on the Business Central connector:
I'm aware of the dedicated BC connector but it is not practical for this scenario. The report queries approximately 50 companies dynamically across multiple BC environments. Using the BC connector would require a separate, manually maintained query per company, which is not maintainable at that scale. Web.Contents with a dynamic company list is the only viable approach here.

Question:
Is it possible to use a Web v2 connection with a Web.Contents-based semantic model? If so, what is required on the M query side or connection configuration side to make it map correctly?

Query sample:

let
Source = Json.Document(Web.Contents(
"https://api.businesscentral.dynamics.com",
[
RelativePath = "/v2.0/TENANT_ID/ENV_NAME/api/PATH/powerBI/v1.0/companies(COMPANY_ID)/businessUnits",
Headers = [Accept = "application/json"]
]
)),
Data = Source[value],
businessUnits_table = Table.FromRecords(Data),
FinalColumns = Table.SelectColumns(businessUnits_table, {"systemId", "code", "name", "currencyCode", "environmentName", "bcApiUrl"})
in
FinalColumns

[v-achippa]

Hi @tbrasch72,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @GilbertQ for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the response provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[tbrasch72]

Unfortunately, no, it's still not working. I've tried a Million things. I was hoping someone had a Web.Content query with web v2 connection working and could point out how to configure it.

[v-achippa]

Hi @tbrasch72,

Here looks like this is not working because of a current limitation, like Web.Contents is treated as Web (v1) data sources in the service and it cannot be used with Web v2 connections (Service Principal).

So currently there is no configuration that will work. If possible use Web(v1) authentication, or a custom connector or a intermediate API if service principal is required.

Thanks and regards,

Anjan Kumar Chippa

[tbrasch72 · Accepted solution]

I had a remote session with 2 Microsoft Power BI support engineers. They couldn't get it to work either and also concluded it's currently a "product limitation".

Afterwards, I took a step back, reconsidered the root cause (base URL it not a valid endpoint, so auth fails) and then found the solution:

The trick was to find a base URL that reponds to auth requests and could have the RelativePath added to it for the final endpoint:

BaseUrl = "https://api.businesscentral.dynamics.com/v2.0/9afae3d0-395a-430d-bf09-71afcd116b58/ProductionCA/api/...",

AddInvoices = Table.AddColumn(FilteredRows, "Invoices", each
let
CompanyId = Text.BetweenDelimiters([bcApiUrl], "companies(", ")"),
Source = Json.Document(Web.Contents(
BaseUrl,
[
RelativePath = "companies(" & CompanyId & ")/salesInvoices?$filter=status eq 'Open'",
Headers = [Accept = "application/json"]
]
)),
Data = Source[value]


This now allows for the Web v1 connection to be mapped to the model AND Service Principal authentication to succeed.

The report is now auto-refreshing correctly.

[v-achippa]

Hi @tbrasch72,

We wanted to kindly follow up to check if the response I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[GilbertQ]

Hi @tbrasch72 

But I would highly recommend doing is to make sure to see if we can get a working with the web dot contents feature with the code you described below, because the natural work successfully in the power of BI service.

[tbrasch72]

Thanks for your response. I'm not sure exactly what you are recommending. Would you please provide some details?

[GilbertQ]

Hi @tbrasch72 

Apologies for not making clear what I was trying to describe. What I was trying to say was. If you could use the Web Connector to see if that will successfully work both in Power BI Desktop and within the service. Because quite often it takes a bit of setting up, especially when working with web connections, APIs and service principles.