March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi,
I am trying to create a custom connector for Power BI. I have followed the documentation on how to set up
a Navigation Table. The table will navigate to three different functions.
I have set up the code accordingly. However, the UI for the function inputs is not generated in the Navigator window. Rather I am seeing this:
I see the UI just fine in the Power Query Editor. Here is my implementation of some test code:
[DataSource.Kind="PQExtension1", Publish="PQExtension1.Publish"]
shared PQExtension1.Contents = () as table =>
let
objects = #table(
{"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{
{"Item1", "item1", PQExtension1.GetSeries, "Function", "Function", true}
}),
NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
NavTable;
[DataSource.Kind = "PQExtension1"]
shared PQExtension1.GetSeries = () => Value.ReplaceType(HelloWorldImpl, HelloWorldType)
HelloWorldType = type function (
message as (type text meta [
Documentation.FieldCaption = "Message",
Documentation.FieldDescription = "Text to display",
Documentation.SampleValues = {"Hello world", "Hola mundo"}
]),
optional count as (type number meta [
Documentation.FieldCaption = "Count",
Documentation.FieldDescription = "Number of times to repeat the message",
Documentation.AllowedValues = { 1, 2, 3 }
]))
as table meta [
Documentation.Name = "Hello - Name",
Documentation.LongDescription = "Hello - Long Description",
Documentation.Examples = {[
Description = "Returns a table with 'Hello world' repeated 2 times",
Code = "HelloWorldWithDocs.Contents(""Hello world"", 2)",
Result = "#table({""Column1""}, {{""Hello world""}, {""Hello world""}})"
],[
Description = "Another example, new message, new count!",
Code = "HelloWorldWithDocs.Contents(""Goodbye"", 1)",
Result = "#table({""Column1""}, {{""Goodbye""}})"
]}
];
HelloWorldImpl = (message as text, optional count as number) as table =>
let
_count = if (count <> null) then count else 5,
listOfMessages = List.Repeat({message}, _count),
table = Table.FromList(listOfMessages, Splitter.SplitByNothing())
in
table;
I appreciate any help on this. I have seen similar posts here in the Power BI forum but clear solutions have not been given.
Solved! Go to Solution.
So I found a solution from the PowerBI REST API connector project. Basically you need a special implementation of the Table.ToNavigationTable function. It seems that you need to delete the "Preview.DelayColumn = itemNameColumn" from Table.ToNavigationTable .
This implementation of the navigation table works:
Table.ForceToNavigationTable = (
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.IsLeafColumn = isLeafColumn
],
navigationTable = Value.ReplaceType(table, newTableType)
in
navigationTable;
The only problem I have now is that the documentation is not shown in the Navigator window:
In the Power Query Editor it looks like this:
So I found a solution from the PowerBI REST API connector project. Basically you need a special implementation of the Table.ToNavigationTable function. It seems that you need to delete the "Preview.DelayColumn = itemNameColumn" from Table.ToNavigationTable .
This implementation of the navigation table works:
Table.ForceToNavigationTable = (
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.IsLeafColumn = isLeafColumn
],
navigationTable = Value.ReplaceType(table, newTableType)
in
navigationTable;
The only problem I have now is that the documentation is not shown in the Navigator window:
In the Power Query Editor it looks like this:
Hi, I have a similar problem.
My function has a record type parameter like this
but in the navigation table it looks like this
Hi @Anonymous ,
It's glad to hear that your problem has been resolved. And thanks for sharing your solution here. Thank you.
Best Regards
Hi,
The examples you have given are with functions that does not implement parameters. I have implemented the NavigationTable function from Handling navigation for Power Query connectors | Microsoft Docs. For instance, if I add a parameter to FunctionCallThatReturnATable then I get the same "No Paramters Specified"-Window in the Navigator. Why does the Preview window not allow me to generate the function UI. In the Power Query editor it works just fine.
shared NavigationTable.Simple = () =>
let
objects = #table(
{"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{
{"Item1", "item1", #table({"Column1"}, {{"Item1"}}), "Table", "Table", true},
{"Item2", "item2", #table({"Column1"}, {{"Item2"}}), "Table", "Table", true},
{"Item3", "item3", (t as text) => FunctionCallThatReturnsATable(t), "Table", "Table", true},
{"MyFunction", "myfunction", AnotherFunction.Contents, "Function", "Function", true}
}),
NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
NavTable;
shared FunctionCallThatReturnsATable = (t as text) =>
#table({"DynamicColumn"}, {{t}});
Hi @Anonymous ,
Please review the following link, hope it can help you.
Best Regards
Thank you, really appreciate the help! I will check it out.
Hi @Anonymous ,
Please refer the following links to get it:
Handling navigation for Power Query connectors | Microsoft Docs
shared NavigationTable.Simple = () =>
let
objects = #table(
{"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{
{"Item1", "item1", #table({"Column1"}, {{"Item1"}}), "Table", "Table", true},
{"Item2", "item2", #table({"Column1"}, {{"Item2"}}), "Table", "Table", true},
{"Item3", "item3", FunctionCallThatReturnsATable(), "Table", "Table", true},
{"MyFunction", "myfunction", AnotherFunction.Contents, "Function", "Function", true}
}),
NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
NavTable;
shared FunctionCallThatReturnsATable = () =>
#table({"DynamicColumn"}, {{"Dynamic Value"}});
Custom Connector and Navigation Tables
GetRows = () => if (state <> null) then state else
let
list = listFunction(),
withName = Table.FromRecords(list, {"Name", "ItemKind", "Parameters"}, MissingField.UseNull),
withData = Table.AddColumn(withName, "Data", each dataFunction([Name], [Parameters])),
withItemName = Table.AddColumn(withData, "ItemName", each if [Parameters] = null or Record.FieldCount([Parameters]) = 0 then "Table" else null),
withoutParameters = Table.RemoveColumns(withItemName, {"Parameters"}),
withIsLeaf = Table.AddColumn(withoutParameters, "IsLeaf", each isLeaf),
navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
navTable
Best Regards
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
6 | |
3 | |
1 | |
1 | |
1 |
User | Count |
---|---|
8 | |
6 | |
4 | |
3 | |
2 |