How to connect a Rest API with variable path parameters as endpoints in URL with Power BI?

[ANNOAH]

Hi,

I would like to connect a GET request with variable endpoints in the URL to Power BI, the url has a structure as the following:

https://test.com/name/server/api/1

The endpoint is a variable path parameter, could be 1, 2 or 3 or any number or text. 

Afterwards, I would like to pass this parameter from Power BI Desktop as well as Service to the API to get only the data/information for this parameter.

1. How can I connect such an URL to Power BI? I found some solutions to query parameters, but not for variable path parameters.

2. Is there any way to dynamically pass the variable parameters to the REST API from both Power BI Desktop and Power BI Service?

Thanks a lot in advance!

[Anonymous · Accepted solution]

HI @ANNOAH,

You can create a query aptamer with text type and invoke it in your query table steps, then you can change the query parameter by rest API to dynamic affect the data source target:

let
    Path = "/name/server/api/" & Parameter,
    Result =
        Web.Contents(
            "https://test.com",
            [
                Headers = [
                    Authorization = "Bearer " + token,
                    RelativePath = Path
                ]
            ]
        )
in
    Result

Datasets - Update Parameters In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Regards,

Xiaoxin Sheng

[ANNOAH]

Hi Xiaoxin,

thank you for your answer!

My issue is that I have to use path parameters (not only text, but also integer) and no query parameters. Is there any solution for path parameters? 

Best regards

[lbendlin]

Sort of. But you need to cheat and hide the fact that you are using dynamic parameters. Otherwise the service will refuse to refresh.

So you need to declare an API call in your connection that works (so the service can see it works) AND that works withot parameters being added.  In your case the call would have to go to https://test.com/name/server/api/

Without that you will be limited to desktop refreshes.

[ANNOAH]

Hi Ibendlin,

thank you for your answer!

If I do so, I get a list of hundreds of numbers. But now I would like to get the information for a specific number. How can I invoke the information of this specific number? This is what my extended API call with the variable path parameter would do. The reason I am using such an API is that I do not want to load all the data to Power BI, I just want to invoke the data that I need by the API and use it in Power BI to minimize data load.

Kind regards,

ANNOAH

[Anonymous]

HI @ANNOAH,

Did the API support query string? If that is the case, you can consider adding a query option in the web connector:

let
    Path = "/name/server/api/" & Parameter,
    Result =
        Web.Contents(
            "https://test.com",
            [
                Headers = [
                    Authorization = "Bearer " + token,
                    RelativePath = Path
                ],
                Query = [
                    id = Parameter2
                ]
            ]
        )
in
    Result

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

Regards,

Xiaoxin Sheng