Hello,
I am trying to use a Custom Connector to generate tables with API GET request data. The connector works and I can see the relevant data, but I can't implement scheduled refresh because of this error when adding my custom connector as a data source:
Unable to create data source. The provider is not support or due to invalid data source. Please check the data source connection string
Details: CustomCmicConnector: The given data source kind is not supported. Data source kind: CustomCmicConnector. Please have this information handy if you choose to create a support ticket. ActivityId: 7eb00e4f-ecf9-471a-84fe-4898d6ce0d01 RequestId: 373a5771-3841-44c8-8c37-fd7640d89d3f Cluster URI:
https://api.powerbi.com Status code: 400
And here is my script:
section CustomCmicConnector;
[DataSource.Kind="CustomCmicConnector"]
shared CustomCmicConnector.Feed = Value.ReplaceType(CustomCmicConnectorImpl, type function (url as Uri.Type) as any);
[DataSource.Kind="CustomCmicConnector", Publish="CustomCmicConnector.Publish"]
shared CustomCmicConnector.Contents = Value.ReplaceType(TestNavTable, type function (url as Uri.Type) as any);
CustomCmicConnectorImpl = (url as text) =>
let
source = Json.Document(Web.Contents(url))
in
source;
// Data Source Kind description
CustomCmicConnector = [
Authentication = [
Key = [],
UsernamePassword = [],
Windows = [],
Implicit = []
],
Label = "CustomCmicConnector"
];
RetrieveAPInvoiceRegistryGroupRet = (Page as number)=>
let
Source = Json.Document(Web.Contents("https://mobile.cmicr12.com/cmicprod", [RelativePath="/ap-rest-api/rest/1/apreggrp/?offset="&Number.ToText(Page)])),
RetrieveAPInvoiceRegistryGroup1 = Record.ToTable(Source),
Value = RetrieveAPInvoiceRegistryGroup1{0}[Value],
#"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"AprgCode", "AprgCompCode", "AprgDesc", "AprgRowid", "AprgVUuid", "DiscriminatorCode", "links"}, {"AprgCode", "AprgCompCode", "AprgDesc", "AprgRowid", "AprgVUuid", "DiscriminatorCode", "links"})
in
#"Expanded Column1";
RetrieveAPInvoiceRegistryGroup = (url as text) as table =>
let
num = Record.ToTable(Json.Document(Web.Contents(url,[RelativePath="/ap-rest-api/rest/1/apreggrp/"]))){1}[Value],
Query2 = List.Generate(()=>
[Result = try RetrieveAPInvoiceRegistryGroupRet(0) otherwise null, Page = 0],
each [Result]<>null and num <> 0,
each [Result = try RetrieveAPInvoiceRegistryGroupRet([Page] + num) otherwise null, Page = [Page]+num],
each[Result]),
#"Converted to Table" = Table.FromList(Query2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = if Record.ToTable(Json.Document(Web.Contents(url,[RelativePath="/ap-rest-api/rest/1/apreggrp/"]))){1}[Value] <> 0
then Table.ExpandTableColumn(#"Converted to Table", "Column1", {"AprgCode", "AprgCompCode", "AprgDesc", "AprgRowid", "AprgVUuid", "DiscriminatorCode", "links"}, {"AprgCode", "AprgCompCode", "AprgDesc", "AprgRowid", "AprgVUuid", "DiscriminatorCode", "links"})
else #table({"AprgCode", "AprgCompCode", "AprgDesc", "AprgRowid", "AprgVUuid", "DiscriminatorCode", "links"},{})
in
#"Expanded Column1";
TestNavTable = (url as text) as table =>
let
source = #table({"Name", "Data", "ItemKind", "ItemName", "IsLeaf"}, {
{ "RetrieveAPInvoiceRegistryGroup", RetrieveAPInvoiceRegistryGroup(url), "Table", "Table", true }
}),
navTable = Table.ToNavigationTable(source, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
navTable;
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;
//
// Data Source UI publishing description
CustomCmicConnector.Publish = [
Beta = true,
Category = "Other",
ButtonText = { "CustomCmicConnector", "CustomCmicConnector" }
];
