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 created a column in another table in Power Query that calls this function for the project ID for each row. However, Power Query wants me to 'Configure connections" for every project.

 

I can get around this in Power BI itself by changing the Privacy Level to 'Ignore the Privacy levels and potentially improve performance". However, I don't see this option in Power Query within Datamarts.

 

Here is the function:

 

let

    GetTimeEntriesByProject = (ProjectID as text) =>

    let
        BaseUrl = "https://url/api/v2/time/ByProject/",
        StartDate = Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), -365), "yyyy-MM-dd"),  // Current date - 365 days
        EndDate = Text.From(DateTime.Date(DateTime.LocalNow())),  // Current date
        Url = BaseUrl & ProjectID & "?StartDt=" & StartDate & "&EndDt=" & EndDate & "&View=Detailed",
        Headers = [
            #"X-Auth-Token" = "xxx", 
            #"X-Auth-Realm" = "xxx",
            #"Authorization" = "" 
        ],
        Source = Json.Document(Web.Contents(Url, [Headers=Headers]))
    in
        Source
in

    GetTimeEntriesByProject

 

  

Here is the Query:

 

let
    Source = Xml.Tables(Web.Contents("https://url/api/v2/picklist/projects", [Headers=[#"X-Auth-Token"="xxx", #"X-Auth-Realm"="xxx", #"Content-Type"="application/xml", Accept="application/xml"]])),
    Table0 = Source{0}[Table],
    #"Invoked Custom Function" = Table.AddColumn(Table0, "TimebyProject", each TimebyProject([Id])),
    #"Other Transformations" = ...
in
    #"Other Transformations"

 

 

Here is what the query is trying to get me to do for every single project:

 

 

Is there a way to write this configuration into the Power Query itself? Or to set permissions somewhere to avoid this?

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

3 Replies

  • 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?

  • chizzle's avatar
    chizzle
    Regular Visitor
    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