How to format Custom Connector with multiple parameters and Authorization Level selection?

[clongaw]

I'm trying to create a Custom Connector that connects to an API that needs a Company variable in the header. I have it working where a user can input both a url and a company parameter, but the OAuth flow isn't being run properly as the url is treated as text instead of converted to Uri.Type. How can I conver the url to Uri.Type and allow the user to select the level at which to authorize, while also intaking other parameters?

My code without the other parameters and hardcoding the Company allows this and is:

#"DefaultHeaders"=[#"accept"="*/*", 
        #"Company"=#"MyCompany"];

[DataSource.Kind="OAuthTest", Publish="OAuthTest.Publish"]
shared OAuthTest.Contents = Value.ReplaceType(GetDataImpl,GetDataType);

GetDataImpl = (url as text) =>
let 
      Data = Xml.Tables(Web.Contents(url, 
        [Headers=#"DefaultHeaders"]))
  in
      Data;

GetDataType = type function (url as Uri.Type) as any;

My code that works but doesn't properly convert the url to Uri.Type in the correct location is:

[DataSource.Kind="OAuthTest", Publish="OAuthTest.Publish"]
shared OAuthTest.Contents = (url as text, company as text) => 
    let 
        url = Value.ReplaceType(url, Uri.Type) as any,
        #"DefaultHeaders"=[#"accept"="*/*", 
            #"Company"=company],
        ResultInfo = GetDataImpl(url, #"DefaultHeaders")
    in 
        ResultInfo;

GetDataImpl = (url as text, #"DefaultHeaders" as record) =>
    let
        Data =  Xml.Tables(Web.Contents(url, 
            [Headers=#"DefaultHeaders"])),
        TableInfo = Data{0}[Table]
    in
        TableInfo;

[clongaw · Accepted solution]

I have found the solution to this issue. The code should look like 

[DataSource.Kind="OAuthTest", Publish="OAuthTest.Publish"]
shared OAuthTest.Contents = Value.ReplaceType(GetDataImpl, GetDataType);

GetDataImpl = (url as text, company as text, optional answer as text) as table=>
let 
      Data = Xml.Tables(Web.Contents(url, 
        [Headers=#"DefaultHeaders"]))
  in
      Data;

GetDataType = type function (url as Uri.Type, company as text, optional answer as text) as table;

[v-yiruan-msft]

Hi @clongaw ,

Please update the codes as below and check if you can get what you want. The codes uses the Value.ReplaceType function to replace the type of the OAuthTestImpl function with a new type that has a single parameter of type Uri.Type. This will allow you to pass a Uri value to the OAuthTest.Contents function and have it be correctly treated as a Uri.Type.

shared OAuthTest.Contents = Value.ReplaceType(OAuthTestImpl, type function (url as Uri.Type, company as text) as any);

OAuthTestImpl = (url as text, company as text) => 
    let 
        #"DefaultHeaders"=[#"accept"="*/*", 
            #"Company"=company],
        ResultInfo = GetDataImpl(url, #"DefaultHeaders")
    in 
        ResultInfo;

GetDataImpl = (url as text, #"DefaultHeaders" as record) =>
    let
        Data =  Xml.Tables(Web.Contents(url, 
            [Headers=#"DefaultHeaders"])),
        TableInfo = Data{0}[Table]
    in
        TableInfo;

Best Regards

[clongaw]

Hey @v-yiruan-msft, when running that I get the error "OAuthTest.Contents is not recognized". I believe this means it's throwing an error in the definition of the function somehow. 

As a workaround, I've set up an input record which appears in the Power Query editor after the OAuth process is run. This still does not allow authentication levels, but mimics the functionality by having the user input the base URL and building the rest as needed.

The code is as follows:

[DataSource.Kind="OAuthTest", Publish="OAuthTest.Publish"]
shared OAuthTest.Contents = GetDataImpl;

// This causes the following steps to occur:
//  1. The user is prompted for a url. Currently the Base URL is required
//  2. The user is prompted to go through the OAuth process if needed
//  3. The Connector opens the Power Query editor and allows the user to input all record fields
//  4. The user can duplicate or replicate calls without going through OAuth again since the Base URL is already set
GetDataImpl = (url as text, input as record)=>
    let 
        Func = (input as record) =>
        let
            // Build URL as needed. URL can have Package, Folder, and XML formatted answers
            urlWithPackage = if (Record.Field(input,"package") <> null) then
                url & "/" & Record.Field(input,"package")
            else url,
            urlWithFolder = if (Record.Field(input,"folder") <> null) then
                urlWithPackage & "/" & Record.Field(input,"folder")
            else urlWithPackage,
            // GetAllPages provides pagination for calls
            // XML answers are included in url, and company is included in header
            TableInfo = GetAllPages(urlWithFolder, Record.Field(input, "answers"), Record.Field(input, "company"), 50)
        in
            TableInfo,

        NewType = type function (
            input as 
                [
                    company = text,
                    optional package = text,
                    optional folder = text,
                    optional answers = text
                ]
            ) as any,
        Ascribed = Value.ReplaceType(Func, NewType)
    in 
        Ascribed;

[v-yiruan-msft]

Hi @clongaw ,

It seems that you are using OAuth authentication. You can implement custom logic for your service by providing functions for StartLogin and FinishLogin. The StartLogin function returns the authorization URI to initiate the OAuth flow, while the FinishLogin function exchanges the authorization code for an access token. Please review the following link, hope it can help you.

Additional connector functionality - Power Query | Microsoft Learn

Handling authentication for Power Query connectors - Power Query | Microsoft Learn

Authentication = [
    OAuth = [
        StartLogin = StartLogin,
        FinishLogin = FinishLogin,
        Refresh = Refresh,
        Logout = Logout
    ],
    Key = [],
    UsernamePassword = [],
    Windows = [],
    Anonymous = []
]

Best Regards