Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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" }
];
Solved! Go to Solution.
I didn't have a test connection in my data source kind description. The scheduled refresh works now. Here are the links that helped me:
https://learn.microsoft.com/en-us/power-query/samples/trippin/9-testconnection/readme
I didn't have a test connection in my data source kind description. The scheduled refresh works now. Here are the links that helped me:
https://learn.microsoft.com/en-us/power-query/samples/trippin/9-testconnection/readme
Would it be possible for you to share your code again like you did above, but with the test connection portion added? I'm running into the same issue with a different Custom Connector and am stumped on how to get the test connection part added.
Hi @ljaos67
You would need to create a custom connector and have it in the right folder in the On-Premise Data Gateway
Use custom data connectors with the on-premises data gateway - Power BI | Microsoft Learn
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.