Custom connecto hierarchy fail with on premise data gateway

[etiskapp]

Hi,

We have developed our own connector to our API. To help the use navigate through our API hierarchy, we have created nested table views (see screenshot).
The connector and report works fine in powerBI desktop but when we publish it and use our connector with the on premise data gateway we get an error.

The request made in the advanced editor :

let
Source = Bimsync.Contents(),
#"BOS Sandbox" = Source{[Name="BOS Sandbox"]}[Data],
Model1 = #"BOS Sandbox"{[Name="Model"]}[Data]
in
Model1

The error in the refresh console :

{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The key didn't match any rows in the table."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Key","detail":{"type":1,"value":"[Name = \"Model\"]"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.Table","detail":{"type":1,"value":"#table({\"Name\", \"Data\", \"ItemKind\", \"ItemName\", \"IsLeaf\"}, {})"

The connector code :

[Version = "1.0.0"]
section Bimsync;

project_base = "https://api.XXX.com/v2/";
opencde_base = "https://opencde.XXX.com/bcf/2.1";
client_id = "XXX";
client_secret = "XXX";
redirect_uri = "https://preview.powerbi.com/views/oauthredirect.html";
token_uri = "https://api.XXX.com/oauth2/token";
authorize_uri = "https://api.XXX.com/oauth2/authorize?";
JSON_TYPE = "application/json";
URL_ENCODED = "application/x-www-form-urlencoded";
TOKENURL = "/viewer3d/token";
MODELURL = "/models";
REVISIONURL = "/revisions";
MEMBERURL = "/members?userType=user";
TEAMSURL = "/members?userType=team";
TOPICURL = "/topics";
DOCUMENTURL = "/documents";
PRODUCTURL = "/ifc/products";
ISSUE_BOARD_STATUSES_URL_SUFFIX = "/extensions/statuses";
ISSUE_BOARD_TYPES_URL_SUFFIX = "/extensions/types";
ISSUE_BOARD_LABEL_URL_SUFFIX = "/extensions/labels";

BOARDURL = "projects?bimsync_project_id=";

REC = "{""query"":{""ifcType"": {""$in"": [""IfcWallStandardCase"",""IfcCovering"",""IfcOpeningElement"",""IfcBuildingElementProxy"",""IfcDoor"",""IfcMember"",""IfcSpace"",""IfcWindow"",""IfcFurnishingElement"",""IfcColumn"",""IfcGrid"",""IfcPlate"",""IfcSlab"",""IfcBuildingStorey"",""IfcCurtainWall"",""IfcStairFlight"",""IfcStair"",""IfcWall""]}}}";
PRODUCTS_FIELDS = {"objectId", "revisionId", "ifcType", "type", "attributes"};
READABLE_PRODUCTS_FIELDS = {"Object Id", "Revision Id", "Ifc type", "Type", "Attributes"};

TOPIC_FIELDS = {"bimsync_issue_board", "guid", "topic_type", "topic_status", "topic_priority", "due_date", "stage", "reference_links", "title", "creation_date", "creation_author", "modified_date", "modified_author", "assigned_to", "description", "bimsync_issue_number", "bimsync_assigned_to", "bimsync_custom_fields"};
READABLE_TOPIC_FIELDS = {"Bimsync issue board", "Issue Id", "Topic type", "Topic status", "Topic priority", "Due date", "Stage", "Reference links", "Title", "Creation date", "Creation author", "Modified date", "Modified author", "Assigned to", "Description", "Bimsync issue number", "Bimsync assigned to", "Custom fields"};

EMPTY_TABLE = #table({}, {});

windowWidth = 1200;
windowHeight = 1000;


DefaultRequestHeaders = [
    #"Content-type" = JSON_TYPE  // column name and values only
];


Bimsync.Feed = (url as text) =>
    let
        source = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),
        json = Json.Document(source),
        // The response is a JSON record - the data we want is a list of records in the "value" field
        //value = json[value],
        headers =
             let
                uniqueHeaders = List.Union(List.Transform(json, Record.FieldNames))
             in
                uniqueHeaders,
        asTable = Table.FromRecords(json, headers, MissingField.UseNull)

        //asTable = Table.FromRecords(json),
        //asFilteredTables = Table.SelectColumns(asTable, {"id","name"}) 
        // expand all columns from the record
        //fields = Record.FieldNames(Table.FirstValue(asTable, [Empty = null])),
        //expandAll = Table.ExpandRecordColumn(asTable, "Column1", fields)
    in
        asTable;

DoGET = (url) =>
    let
        serverData = Json.Document(Web.Contents(url,  [
            Headers = [
                #"Content-type" = JSON_TYPE
            ]
        ]))
    in
        serverData;

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;

Bimsync.Models = (url as text, projectId as text) =>
    let
        source = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),
        json = Json.Document(source),
        // The response is a JSON record - the data we want is a list of records in the "value" field
        asTable = Table.FromRecords(json),
        withProject = Table.AddColumn(asTable, "Project Id", each projectId),
        #"Rename columns" = Table.RenameColumns(withProject, {{"id", "Model Id"}, {"name", "Name"}})
        // expand all columns from the record
        //fields = Record.FieldNames(Table.FirstValue(asTable, [Empty = null])),
        //expandAll = Table.ExpandRecordColumn(asTable, "Column1", fields)
    in
        #"Rename columns";

BimsyncProjectNavTable = (url as text, id as text) as table =>
    let
        targetUrl = url & id,
        objects = #table(
            {"Name",                                  "Data",                                           "ItemKind", "ItemName", "IsLeaf"},{
            //{Extension.LoadString("TokenLabel"),      Bimsync.ViewerToken(targetUrl & TOKENURL, id),    "Table",    "Table",    true},
            {Extension.LoadString("ModelLabel"),      Bimsync.Models(targetUrl & MODELURL, id),         "Table",    "Table",    true}
            //{Extension.LoadString("Test"),      Test,         "Table",    "Table",    true}

        }),
        // Generate the nav table
        navTable = Table.ToNavigationTable(objects, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
        //result = Table.ExpandTableColumn
    in
        navTable; 

BimsyncNavTable = () as table =>
    let
        projectUri = project_base & "projects",
        projects = Bimsync.Feed(projectUri&"?pageSize=1000"),
        #"Expand owner column" = Table.ExpandRecordColumn(projects, "owner", {"name", "type"}, {"OwnerName", "Type"}),
        #"Name of organization or other" = Table.AddColumn(#"Expand owner column", "Owner", each if [Type] = "organization" then [OwnerName] else "Other"),
        rename = Table.RenameColumns(#"Name of organization or other", {{"name", "Name"}}),
        projectBaseUri = projectUri & "/",
        // Add Data as a calculated column
        withData = Table.AddColumn(rename, "Data", each BimsyncProjectNavTable(projectBaseUri, [id]), Uri.Type),
        // Add ItemKind and ItemName as fixed text values
        withItemKind = Table.AddColumn(withData, "ItemKind", each "Table", type text),
        withItemName = Table.AddColumn(withItemKind, "ItemName", each "Table", type text),
        // Indicate that the node should not be expandable
        withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each false, type logical),
        // Generate the nav table
        navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        navTable ;

Test = () => 
    let
        json = Bimsync.Feed("https://api.bimsync.com/v2/projects"),
        schema = GetSchemaForEntity(Extension.LoadString("Test")),
        appliedSchema = Table.ChangeType(json, schema),
        dateFormat = Table.TransformColumnTypes(appliedSchema, {{"createdAt", type text}}, "fr-FR")

    in
        dateFormat;


Bimsync.ViewerToken = (url as text, projectId as text) =>
    let
        source = Web.Contents(url, [ Headers = DefaultRequestHeaders, Content = Text.ToBinary("") ]),
        json = Json.Document(source),
        output = #table(
            {"Project Id", "Token"},{
            {projectId, json[url]}})
        // The response is a JSON record - the data we want is a list of records in the "value" field
    in
        output;

[DataSource.Kind="Bimsync", Publish="Bimsync.Publish"]
shared Bimsync.Contents = () => BimsyncNavTable() as table;

// Data Source Kind description
Bimsync = [
    TestConnection = (dataSourcePath) => {"Bimsync.Contents"},
    Authentication = [
        OAuth = [
            StartLogin = StartLogin,
            FinishLogin = FinishLogin,
            Refresh = Refresh,
            Label = Extension.LoadString("AuthenticationLabel")
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];



// Data Source UI publishing description
Bimsync.Publish = [
    Beta = false,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    SourceImage = Bimsync.Icons,
    SourceTypeImage = Bimsync.Icons
];

Bimsync.Icons = [
    Icon16 = { Extension.Contents("BimsyncConnector16.png"), Extension.Contents("BimsyncConnector20.png"), Extension.Contents("BimsyncConnector24.png"), Extension.Contents("BimsyncConnector32.png") },
    Icon32 = { Extension.Contents("BimsyncConnector32.png"), Extension.Contents("BimsyncConnector40.png"), Extension.Contents("BimsyncConnector48.png"), Extension.Contents("BimsyncConnector64.png") }
];



Bimsync.GetIssueBoards = (url as text) => 
    let 
        issueBoards = Bimsync.Feed(url),
        #"Rename columns" = Table.RenameColumns(issueBoards, {{"project_id", "Issue board Id"}, {"name", "Name"}, {"bimsync_project_name", "Project name"}, {"bimsync_project_id", "Project Id"}, {"bimsync_issue_board_name", "Issue board name"}}),
        schema = GetSchemaForEntity(Extension.LoadString("IssueBoards")),
        appliedSchema = Table.ChangeType(#"Rename columns", schema)
    in 
        appliedSchema;



// OAUTH

StartLogin = (resourceUrl, state, display) =>
    let
        AuthorizeUrl = authorize_uri & Uri.BuildQueryString([
            response_type = "code", 
            client_id = client_id,
            redirect_uri = redirect_uri])
    in
        [
            LoginUri = AuthorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = windowHeight,
            WindowWidth = windowWidth,
            Context = null
        ];

FinishLogin = (context, callbackUri, state) =>
    let
        
        parts = Uri.Parts(callbackUri)[Query],
         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;


TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            grant_type = grantType,
            redirect_uri = redirect_uri,
            client_id = client_id,
            client_secret = client_secret
        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),

        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Content-type" = URL_ENCODED,
                #"Accept" = JSON_TYPE
            ],
            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;
 
 Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);

//Schema table for each entity
DocumentType = type table [
                Project Id = text,
                Document Id = text,
                Library Id = text,
                Name = text,
                Type = text,
                Parent Id= text,
                Document type = text,
                Revision Id = text,
                Revision version = number, 
                Revision name = text,
                Owner = text,
                Created at = datetime,
                Published on = datetime,
                Published by user Id = text,
                Published by user full name = text,
                Published by username = text,
                Avatar url = text,
                Revision size = number,
                Additional format size = number,
                Media type = text
                ];

DocumentLabelsType = type table [
                Label Id = text,
                Document Id = text
                ];

IssuesType = type table [
                Issue board Id = text,
                Issue Id = text,
                Project Id = text,
                Topic type = text,
                Topic status = text,
                Topic priority = text,
                Due date = datetime,
                Stage = text,
                Reference links = text,
                Title = text,
                Creation date = datetime,
                Creation author = text,
                Modified date = datetime,
                Modified author = text,
                Assigned to = text,
                Description = text,
                Bimsync issue number = number,
                Bimsync assigned to user Id = text,
                Bimsync assigned to user email = text,
                Bimsync assigned to user name = text,
                Custom fields = list,
                Assigned to team Id = text,
                Assigned to team email = text,
                Assigned to team name = text,
                Status Id = text,
                Close date = datetime,
                Type Id = text
                ];

IssueBoardType = type table [
                Issue board Id = text,
                Name = text,
                Project name = text,
                Project Id = text,
                Issue board name = text
                ];

IssueLabelType = type table [
                Label Id = text,
                Issue Id = text
                ];

IssueStatusType = type table [
                Name = text,
                Color = text,
                Type = text,
                Issue board Id = text,
                Status Id = text
                ];

IssueTypeType = type table [
                Name = text,
                Color = text,
                Issue board Id = text,
                Type Id = text
                ];

LabelType = type table [
                Label Id = text,
                Name = text,
                Color = text,
                Group = text
                ];

MemberType = type table [
                User full name = text,
                Username = text,
                User Id = text,
                Role = text,
                Project Id = text,
                Visibility = text
                ];

ModelType = type table [
                Model Id = text,
                Name = text,
                Project Id = text
                ];

ModelRevisionType = type table [
                Revision Id = text,
                Comment = text,
                Version = number,
                Created at = datetime,
                Model Id = text,
                Model name = text,
                Created by user Id = text,
                Created by user full name = text,
                Created by username = text
                ];

ProductsType = type table [
                Object Id = text,
                Revision Id = text,
                Ifc type = text,
                Type = Record.Type,
                Guid = text,
                Project Id = text
                ];

TeamType = type table [
                Project Id = text,
                Name = text,
                Team Id = text,
                Role = text,
                Visibility = text
                ];

TeamMemberType = type table [
                User full name = text,
                Username = text,
                User Id = text,
                Role = text,
                Visibility = text,
                Team Id = text
                ];

TestType = type table [
                id = text,
                name = text,
                description = text,
                createdAt = datetime,
                owner = Record.Type,
                siteLocation = Record.Type,
                imageUrl = text
                ];

SchemaTable = #table({"Entity", "Type"}, {
    {Extension.LoadString("DocumentLabel"), DocumentType},
    {Extension.LoadString("LabelsAssociatedToDocuments"), DocumentLabelsType},
    {Extension.LoadString("IssueLabel"), IssuesType},
    {Extension.LoadString("IssueBoards"), IssueBoardType},
    {Extension.LoadString("LabelsAssociatedToIssues"), IssueLabelType},
    {Extension.LoadString("Statuses"), IssueStatusType},
    {Extension.LoadString("Types"), IssueTypeType},
    {Extension.LoadString("Labels"), LabelType},
    {Extension.LoadString("MemberLabel"), MemberType},
    {Extension.LoadString("ModelLabel"), ModelType},
    {Extension.LoadString("RevisionLabel"), ModelRevisionType},
    {Extension.LoadString("ProductLabel"), ProductsType},
    {Extension.LoadString("Teams"), TeamType},
    {Extension.LoadString("TeamMembers"), TeamMemberType},
    {Extension.LoadString("Test"), TestType}
});



GetSchemaForEntity = (entity as text) as type => try SchemaTable{[Entity=entity]}[Type] otherwise error "Couldn't find entity: '" & entity &"'";

 // Load common library functions
// 
// TEMPORARY WORKAROUND until we're able to reference other M modules
Extension.LoadFunction = (name as text) =>
    let
        binary = Extension.Contents(name),
        asText = Text.FromBinary(binary)
    in
        Expression.Evaluate(asText, #shared);

Table.ChangeType = Extension.LoadFunction("Table.ChangeType.pqm");

 A screenshot of the hierachy in PBI desktop :

[v-yiruan-msft]

Hi @etiskapp ,

You can refer the following links to try to solve your problem.

Use custom data connectors with the on-premises data gateway - Power BI | Microsoft Learn

Solved: Custom connector not showing when configuring gate... - Microsoft Fabric Community

Best Regards

[etiskapp]

Me answer wasn't a solution. It just shows that if you totally remove the data retreival, it works. But I need to dig into the data.

[etiskapp · Accepted solution]

Actually it's already configured like that and the Gateway works. If I change the connector code and remove the hierarchy or if I change my request in the report to this :

let
Source = Bimsync.Contents(),
in
Source

The refresh works and I don't get any error.

[etiskapp]

NO it's not solved. I'm saying that if I remove the hierarchy (and can't access to my data) this doesn't work. So this is not the solution.

The solution would then be : don't use your connector. Please reopen.