Forum Discussion

tbrasch72's avatar
tbrasch72
Regular Visitor
3 months ago
Solved

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

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 W...
Need Help
  • tbrasch72's avatar
    tbrasch72
    3 months ago

    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/v2.0",

    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's avatar
v-achippa
Community Support
3 months ago

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

v-achippa's avatar
v-achippa
Community Support
3 months ago

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