Hi,
I am building a Power BI custom connector using Power Query SDK. My connector is simple — it makes 2 API calls: one to the /schema endpoint to get the schema for tables (what tables I have and what fields), and another to the /data endpoint to get data for tables. Then, using this data, it constructs tables and populates them. It is working, but it is making too many API calls and I don't know why. Perhaps this is just how Power BI Desktop behaves.
When I am using my connector in "Get Data," it first opens a pop-up with all my tables, and I see 2 requests in my logs to /schema and then to /data, which is correct. But when I select all tables, for example (I have 14), I see 22 calls to /schema and /data. If I imagine that for each table it would call the function to get data again, it should be 14 calls only, but why do I have 22? Another question: my /data endpoint initially returns all the data I need to construct tables. Can I somehow prevent this behavior of having multiple API calls?
Here is code of my connector:
[Version = "1.0.0"]
section TestConnector;
config = Json.Document(Extension.Contents("config.json"));
api_url = config[api_url];
client_id = config[client_id];
redirect_uri = config[redirect_uri];
authorize_uri = config[authorize_uri];
[DataSource.Kind="TestConnector", Publish="TestConnector.Publish"]
shared TestConnector.Contents = Value.ReplaceType(NavTable, Type);
TestConnector = [
TestConnection = (dataSourcePath) => { "TestConnector.Contents", dataSourcePath },
Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Label=Extension.LoadString("AuthenticationLabel")
]
]
];
TestConnector.Publish = [
Beta = true,
Category = "",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
LearnMoreUrl = "",
SourceImage = TestConnector.Icons,
SourceTypeImage = TestConnector.Icons
];
TestConnector.Icons = [
Icon16 = { Extension.Contents("16.png"), Extension.Contents("20.png"), Extension.Contents("24.png"), Extension.Contents("32.png") },
Icon32 = { Extension.Contents("32.png"), Extension.Contents("40.png"), Extension.Contents("48.png"), Extension.Contents("64.png") }
];
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,
NavigationTable.ItemNameColumn = itemNameColumn,
NavigationTable.IsLeafColumn = isLeafColumn
],
navigationTable = Value.ReplaceType(table, newTableType)
in
navigationTable;
Type = type function (
link as (Uri.Type meta [
Documentation.FieldCaption = Extension.LoadString("UrlParameterCaption"),
Documentation.FieldDescription = Extension.LoadString("UrlParameterDescription"),
Documentation.SampleValues = { Extension.LoadString("UrlParameterSampleValue") }
]))
as table meta [
Documentation.Name = Extension.LoadString("TableName"),
Documentation.LongDescription = Extension.LoadString("TableDescription")
];
NavTable = (link as text) =>
let
linkValue = if (link = null or link = "") then error "link is required." else link,
schemaUrl = api_url & "/schema",
schemaSource = Web.Contents(schemaUrl),
schemaJson = Json.Document(schemaSource),
access_token = RefreshToken(),
dataSource = Web.Contents(api_url, [
RelativePath = "/data",
Query = [url = linkValue],
Headers=[Authorization = "Bearer " & access_token],
Timeout=#duration(0, 2, 0, 0),
ManualStatusHandling = {404, 400, 500, 403},
ManualCredentials = true
]),
dataJson = Json.Document(dataSource),
tableNames = Record.FieldNames(schemaJson),
tablesWithData = List.Select(tableNames, each
Record.HasFields(dataJson, _) and
not List.IsEmpty(Record.Field(dataJson, _))
),
navTableRows = List.Transform(tablesWithData, each {_, GetDynamicTable(dataJson, schemaJson, _), "Table", "Table", true}),
navTable = #table({"Name", "Data", "ItemKind", "ItemName", "IsLeaf"}, navTableRows),
navTableWithNavigation = Table.ToNavigationTable(navTable, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
navTableWithNavigation;
GetDynamicTable = (dataJson as record, schemaJson as record, tableName as text) as table =>
let
fieldsDefinition = Record.Field(schemaJson, tableName),
fieldNames = if (Type.Is(Value.Type(fieldsDefinition), type list)) then
fieldsDefinition
else
Record.FieldNames(fieldsDefinition),
tableData = Record.Field(dataJson, tableName),
tableFromList = Table.FromList(tableData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expandedTable = Table.ExpandRecordColumn(tableFromList, "Column1", fieldNames, fieldNames),
typedTable = if (Type.Is(Value.Type(fieldsDefinition), type list)) then
expandedTable
else
ApplyColumnTypes(expandedTable, fieldsDefinition)
in
typedTable;
ApplyColumnTypes = (table as table, fieldsDefinition as record) as table =>
let
fieldNames = Record.FieldNames(fieldsDefinition),
GetPQType = (fieldDef as record) =>
let
typeValue = Record.Field(fieldDef, "type")
in
if typeValue = "text" then type text
else if typeValue = "number" then type number
else if typeValue = "date" then type date
else if typeValue = "datetime" then type datetime
else if typeValue = "logical" or typeValue = "boolean" then type logical
else if typeValue = "time" then type time
else if typeValue = "duration" then type duration
else type any,
transformations = List.Transform(
fieldNames,
each {_, GetPQType(Record.Field(fieldsDefinition, _))}
),
result = Table.TransformColumnTypes(table, transformations)
in
result;
PS
I've already added a caching layer to cache data on my /data API for 10 minutes because of this behavior, but for me, it is just a hack solution.
