Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 :
Solved! Go to 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.
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
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.
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.
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
40 | |
4 | |
3 | |
3 | |
3 |