Forum Discussion

CMAltium's avatar
CMAltium
Frequent Visitor
2 years ago

Multiple data source kinds in one custom power query connector

I have created a connector to access reports from an API with OAuth 2.0, which works fine. However, the API call returns a URL used to retrieve the report, which is truncated by Power BI. I want to...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi CMAltium ,
Based on your description, Power Query does not directly support setting up multiple different credentials for the same connector. You can use parameters to manage different credentials. You can create a parameter table with credentials for different APIs and then use those parameters in your query.
You can create a custom function to do this

let
    Source = (username as text, password as text) as table =>
    let
        // use username and password to send API request
    in
        // return data list
in
    Source

 

Best regards,
Albert He

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


 

CMAltium's avatar
CMAltium
Frequent Visitor
2 years ago

Hi Albert,

 

Below is an example of my code.

The flow is as follows:

- Authenticate for Nexar using OAuth 2.0. I haven't included all the code for this in the example below, but it works fine.

- Call API to retrieve a long URL, which when clicked, downloads a CSV. This URL is truncated by Power BI desktop.

- Call tinyUrl API to shorten the long URL. This is where I believe the power connector is unhappy, and in Power BI desktop I get this error:

 

- Open the report as a table.

 

My code is as follows:

//
// Data Source definition
//
[DataSource.Kind="Nexar_PKCESample", Publish="Nexar_PKCESample.Publish"]
shared Nexar_PKCESample.Contents = (url) =>
    let
        accessToken = Extension.CurrentCredential()[access_token],
        url = "https://api.nexardev.com/graphql",
        
        contents = GetReport(accessToken)
    in
        contents;

//
// Data Source Kind definition
//This part works fine. I haven't included the full authentication code in this example.
//
Nexar_PKCESample = [
    Authentication = [
        OAuth = [
            //
            //Authentication for reporting API
            //
            StartLogin = StartLogin,
            FinishLogin = FinishLogin,
            Refresh = Refresh
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];


//TinyURL URL shortening
GetReport = (accessToken) =>
    let
        //...
        //Original script to get URL from a different API using the accessToken
        //...

        //Long URL from script above
        longUrl = "https://www.examplelongurl.com",
        shortUrl = shortenUrl(longUrl)
    in
        shortUrl;

//Function for URL shortening
//This function seems to cause issues as the Power Query Connector requires credentials to be set to call this API too, even though I have my token built in to the function

shortenUrl = (longUrl) =>
    let
       
        tinyUrl = "https://api.tinyurl.com/create?api_token=<MYTOKEN>",
        json_data = Json.FromValue([url = longUrl]),
        response = Web.Contents(
            tinyUrl, 
            [    
                Content = json_data,
                Headers = [#"Content-Type" = "application/json"]
            ]
        ),
        #"JSON" = Json.Document(response),
        shortUrl = #"JSON"[data][tiny_url]
    in
        shortUrl;

 

 I tried to make a separate connector for tinyUrl, and when I run that this popup appears:

It appears to me that because there are two APIs in use, the credentials for the first do not match the credentials for the second even when my authentication token is provided in the function.