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.




GilbertQ's avatar
GilbertQ
Super User
3 months ago

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's avatar
tbrasch72
Regular Visitor
3 months ago

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

  • GilbertQ's avatar
    GilbertQ
    Super User
    3 months ago

    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.