Search code examples
restpowerbipowerquerym

Power Query - Populate column through REST API


I have a list of IDs in Power Query and would like to call and API to return some information about each. As there is no direct API that allows me to pull the information for my entire list, I have to call the API for each row in my table.

The API requires a dynamic access token, which I already have a function that takes care of (GetToken()).

I have followed this guide. The guide adds a custom column for which I have written the following code:

Json.Document(
Web.Contents(
    Text.Combine({"https://urlthatholdstheinfo.com/", [id]}), [Headers=[Authorization="Bearer "& GetToken()]]))

When I close the editor, I get this classic error:

"Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referer

I have previously mitigated this in the Data Source Settings by setting the Permission to "Anonymous". However, I can not find this query in those settings, so I don't know where to change this.

I have unsuccessfully been looking for a way to parse a parameter to Web.Content, that tells the query to do this with Anonymous settings, but it does not seem to exist.

I have tried this variation as well, but I get the same error

Any thoughts on what I can do?

UPDATE:

The answer for this post works, with a small addition. After implementing the answer, the error still occurred. It was resolved by creating a blank query with a fixed id number instead of parsing the column as an argument to the function. This allowed me to go to "Data source settings" and change the permission for the query to Anonymous, which also fixed the problem for the function and the custom column.

let
Source = 
Json.Document(
    Web.Contents(
            "https://https://urlthatholdstheinfo.com/id", 
                [Headers=[Authorization="Bearer "& GetToken()]])),
in
   Source

Also, be sure to not have the curly brackets that are in the original code of the question.


Solution

  • If you have a list of IDs you can make a custom function that uses appropriate constructors for RelativePath like David suggests. Here is a function you can paste into a blank query, which you can specify using Invoke Custom Function on your list of IDs:

    let
        Source = (ID as text) =>
        Json.Document(
            Web.Contents(
                "https://urlthatholdstheinfo.com/",
                [
                    Headers=[Authorization="Bearer "& GetToken()] ,
                    RelativePath=ID
                ]
            ]
            )
        )
    in 
        Source
    

    Make sure your ID column is in text format - or change the custom function above to:

    let
        Source = (ID as number) =>
        Json.Document(
            Web.Contents(
                "https://urlthatholdstheinfo.com/",
                [
                    Headers=[Authorization="Bearer "& GetToken()] ,
                    RelativePath=Text.From(ID)
                ]
            ]
            )
        )
    in 
        Source