Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBI_Monkey
Helper I
Helper I

Adding navigation table to show tables - Custom Connector

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.

PBI_Monkey_1-1652795607919.png

PBI_Monkey_2-1652795676827.png

 

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:

PBI_Monkey_0-1652795202341.png

 

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!

 

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

 

View solution in original post

1 REPLY 1
v-rzhou-msft
Community Support
Community Support

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.