Forum Discussion

chizzle's avatar
chizzle
Regular Visitor
2 years ago
Solved

Avoiding credentials in Datamart Power Query

I am trying to pull information via API into a Datamart. I have a function in Power Query that pulls APIs using a project ID as the source requires the pull by project ID rather than all data. I have...
Need Help
  • chizzle's avatar
    chizzle
    2 years ago

    FYI, I figured out that if you use the base URL and create a connectoin for that then use a relative path for the rest, it works itself out. Here is how the code was structured:

    let

        BaseUrl = "https://url/base/",

        StartDate = Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), -365), "yyyy-MM-dd"),  // Current date - 365 days

        EndDate = Date.ToText(Date.From(DateTime.LocalNow()), "yyyy-MM-dd"),  // Current date

        Headers = [

            #"X-Auth-Token" = "xxx", 

            #"X-Auth-Realm" = "xxx" 

        ],

        Source = Json.Document(
                    Web.Contents(
                        BaseUrl, 
                        [
                            RelativePath="api/v2/time/ByProject/" & ProjectID, 
                            Query=[StartDt=StartDate, EndDt=EndDate, View="Detailed"], 
                            Headers=Headers
                        ]
                    )
                )

    in

        Source
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi chizzle,

 

As far as I know, Ignore the Privacy Levels and potentially improve performance doesn't work in the Power BI service.

 

We suggest that you add all your Project ID as a list and combine data in one query.

 

More information for your reference:

 

Understand Power BI Desktop privacy levels - Power BI | Microsoft Learn

 

Best regards,

 

Joyce

 

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

chizzle's avatar
chizzle
Regular Visitor
2 years ago

I believe I am already doing what you suggest. The Query I have listed above starts with a list of all of the Project IDs. The custom column calls the function for each of those Project IDs to pull information from the API. The issue is how to mass create credentials for all of those API pulls since the API requires a separate pull for each Project ID: https://url/{projectID} .  Is there a way to set the Authentication kind in the header or the Web.Contents function or something else to avoid having to click through the UI for each project API?