Adding navigation table to show tables - Custom Connector

[PBI_Monkey]

Hi All,

I have successfully created a Custom Connector which connects to Xero using OAuth2.

I am able to connect to 1 table (https://api.xero.com/api.xro/2.0/BankTransactions) ie. Endpoint/GET Request.

This is what happens when it connects and authentics - It takes me straight to Power Query with the 1 table only.

Instead of only being able to see 1 table/Endpoint, I'd like to be able to choose from multiple tables, shown in a Navigation Table eg:

I have tried a few things in the code, but I have no idea what or where to add the code to show a Navigation Table.

I'm not a developer, so seem to be going around in circles.

This is my working code:

// This file contains your Data Connector logic
section XeroConnectorTest;

// XeroConnectorTest OAuth2 values
client_id = Text.FromBinary(Extension.Contents("client_id.txt"));
client_secret = Text.FromBinary(Extension.Contents("client_secret.txt"));
redirect_uri = "https://xero.com/";
token_uri = "https://identity.xero.com/connect/token?=";
authorize_uri = "https://login.xero.com/identity/connect/authorize";
logout_uri = "https://login.microsoftonline.com/logout.srf";
connection_uri = "https://api.xero.com/connections";
tenantid = Text.From(GetTenantId(connection_uri));

// Login modal window dimensions
windowWidth = 720;
windowHeight = 1024;

//OAuth2 Scopes
scope_prefix = "";
scopes = {
"offline_access",
"openid",
"profile",
"email",
"accounting.transactions.read",
"accounting.transactions",
"accounting.reports.read",
"accounting.journals.read",
"accounting.contacts.read"
};

GetTenantId = (url as text) =>
let
Source = Web.Contents(url),
ImportedJSON = Json.Document(Source,1252),
ConvertedtoTable = Table.FromList(ImportedJSON, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedtoTable, "Column1", {"id", "authEventId", "tenantId", "tenantType", "tenantName", "createdDateUtc", "updatedDateUtc"}, {"id", "authEventId", "tenantId", "tenantType", "tenantName", "createdDateUtc", "updatedDateUtc"}),
ChangedType = Table.TransformColumnTypes(ExpandedColumn1,{{"id", type text}, {"authEventId", type text}, {"tenantId", type text}, {"tenantType", type text}, {"tenantName", type text}, {"createdDateUtc", type datetime}, {"updatedDateUtc", type datetime}}),
tenantId = Text.From(ChangedType{0}[tenantId])
in
tenantId;

[DataSource.Kind="XeroConnectorTest", Publish="XeroConnectorTest.Publish"]
shared XeroConnectorTest.Contents = (url as text) =>
let
source = Json.Document(Web.Contents(url,[Headers = [#"xero-tenant-id"=tenantid,#"Accept" = "application/json"]]))
in
source;

// Data Source Kind description
XeroConnectorTest= [
TestConnection = (DataSourcePath) =>
let
json = Json.Document(DataSourcePath),
server = json[server],
database = json[database]
in
try
{ "XeroConnectorTest.Contents", server, database }
otherwise
let
message = Text.Format("Couldn't find entity.")
in
Diagnostics.Trace(TraceLevel.Error, message, () => error message, true )
,

Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Refresh=Refresh,
Logout=Logout
]
],
Label = Extension.LoadString("DataSourceLabel")
];

// Data Source UI publishing description
XeroConnectorTest.Publish = [
Beta = true,
Category = "Other",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = XeroConnectorTest.Icons,
SourceTypeImage = XeroConnectorTest.Icons
];

// Helper functions for OAuth2: StartLogin, FinishLogin, Refresh, Logout
StartLogin = (resourceUrl, state, display) =>
let
authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
response_type = "code",
client_id = client_id,
redirect_uri = redirect_uri,
state = state,
scope = GetScopeString(scopes, scope_prefix)
])
in
[
LoginUri = authorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = 720,
WindowWidth = 1024,
Context = null
];

FinishLogin = (context, callbackUri, state) =>
let
// parse the full callbackUri, and extract the Query string
parts = Uri.Parts(callbackUri)[Query],
// if the query string contains an "error" field, raise an error
// otherwise call TokenMethod to exchange our code for an access_token
result = if (Record.HasFields(parts, {"error", "error_description"})) then
error Error.Record(parts[error], parts[error_description], parts)
else
TokenMethod("authorization_code", "code", parts[code])
in
result;

Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);

Logout = (token) => logout_uri;

// see "Exchange code for access token: POST /oauth/token" at https://cloud.ouraring.com/docs/authentication for details
TokenMethod = (grantType, tokenField, code) =>
let
query = [
grant_type = grantType,
redirect_uri = redirect_uri,
client_id = client_id,
client_secret = client_secret
],
queryWithCode = if(grantType = "refresh_token") then [refresh_token = code] else [code = code],

tokenResponse = Web.Contents(token_uri, [
Content = Text.ToBinary(Uri.BuildQueryString(query & queryWithCode)),

Headers= [#"Content-type" = "application/x-www-form-urlencoded",#"Accept" = "application/json"],
ManualStatusHandling = {400}
]),
body = Json.Document(tokenResponse),
result = if (Record.HasFields(body, {"error", "error_description"})) then
error Error.Record(body[error], body[error_description], body)
else
body
in
result;

Value.IfNull = (a, b) => if a <> null then a else b;

GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
let
prefix = Value.IfNull(scopePrefix, ""),
addPrefix = List.Transform(scopes, each prefix & _),
asText = Text.Combine(addPrefix, " ")
in
asText;

XeroConnectorTest.Icons = [
Icon16 = { Extension.Contents("XeroConnectorTest16.png"), Extension.Contents("XeroConnectorTest20.png"), Extension.Contents("XeroConnectorTest24.png"), Extension.Contents("XeroConnectorTest32.png") },
Icon32 = { Extension.Contents("XeroConnectorTest32.png"), Extension.Contents("XeroConnectorTest40.png"), Extension.Contents("XeroConnectorTest48.png"), Extension.Contents("XeroConnectorTest64.png") }
];

Any ideas would be amazing! I believe I'm almost there.

Thanks so much!

[Anonymous · Accepted solution]

Hi  @PBI_Monkey ,

According to your statement, I think you want to use Navigation tables in your custom connector.

You can use the Table.ToNavigationTable function to add the table type metadata needed to create a nav table.

Table.ToNavigationTable = (
    table as table,
    keyColumns as list,
    nameColumn as text,
    dataColumn as text,
    itemKindColumn as text,
    itemNameColumn as text,
    isLeafColumn as text
) as table =>
    let
        tableType = Value.Type(table),
        newTableType = Type.AddTableKey(tableType, keyColumns, true) meta 
        [
            NavigationTable.NameColumn = nameColumn, 
            NavigationTable.DataColumn = dataColumn,
            NavigationTable.ItemKindColumn = itemKindColumn, 
            Preview.DelayColumn = itemNameColumn, 
            NavigationTable.IsLeafColumn = isLeafColumn
        ],
        navigationTable = Value.ReplaceType(table, newTableType)
    in
        navigationTable;

You may refer to this blog to learn more details about how to use Navigation Tables.

Here is the offical blog, I hope it could help you solve your problem.

Best Regards,
Rico Zhou

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