Functions in Navigation Tables for Power BI Custom Connector

[petrbroz]

Hi everyone,

I'm building a custom data connector for Power BI, trying to expose a navigation table with a couple of functions, following this specific code sample: https://github.com/microsoft/DataConnectors/blob/master/samples/NavigationTable/NavigationTable.pq#L.... However, even though my function does not accept any parameters, I get a "No parameter values specified" message when trying to import its data in Power BI:

Any idea why? Am I missing something in my code?

Also, I've been wondering whether I could add custom UI to the individual functions by adding the function documentation, however that doesn't seem to be working, either. Is this feature only available for the top-level function of the connector?

For reference, here's the complete implementation of `my TestConnector:

// This file contains your Data Connector logic
[Version = "1.0.0"]
section TestConnector;

TestConnector = [
    Authentication = [
        Anonymous = []
    ]
];

TestConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = TestConnector.Icons,
    SourceTypeImage = TestConnector.Icons
];

TestConnector.Icons = [
    Icon16 = { Extension.Contents("TestConnector16.png"), Extension.Contents("TestConnector20.png"), Extension.Contents("TestConnector24.png"), Extension.Contents("TestConnector32.png") },
    Icon32 = { Extension.Contents("TestConnector32.png"), Extension.Contents("TestConnector40.png"), Extension.Contents("TestConnector48.png"), Extension.Contents("TestConnector64.png") }
];

[DataSource.Kind="TestConnector", Publish="TestConnector.Publish"]
shared TestConnector.Contents = () => GetNavigationTable();

GetNavigationTable = () as table =>
    let
        _table = #table(
            { "Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf" },
            {
                { "Test Function", "func1", Value.ReplaceType(TestFunction, TestFunctionType), "Function", "Function", true },
                { "Another Function", "func2", AnotherFunction.Contents, "Function", "Function", true }
            }
        )
    in
        NavigationTable.FromTable(_table, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf");

TestFunction = (message as text) as table =>
    let
        result = #table(
            { "Column1", "Column2" },
            {{ "Test Function A", message }}
        )
    in
        result;

TestFunctionType = type function (
    message as (type text meta [
        Documentation.FieldCaption = "Message",
        Documentation.FieldDescription = "Parameter description..."
    ])
) as table meta [
    Documentation.Name = "Test Function",
    Documentation.LongDescription = "Function description..."
];

AnotherFunction.Contents = () => "Returns a static string when invoked.";

NavigationTable.FromTable = (_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;

 Thank you in advance for any suggestions!

[Anonymous]

Hi @petrbroz ,

The message "No parameter values specified" typically appears when a function expects parameters but none are provided. Please check if the function define any unexpected parameter, update the codes below for the function TestFunction and TestFunctionType:

  TestFunction = () as table => 
    let 
        result = #table({"Column1", "Column2"}, {{"Test Function A", "Default Message"}}) 
    in 
        result;

TestFunctionType = type function () as table meta
    [
        Documentation.Name = "Test Function",
        Documentation.LongDescription = "Function description..."
    ];

Best Regards

[petrbroz]

Thank you for the suggestion @Anonymous. Unfortunately that doesn't solve the issue. Please note that the function I'm trying to access in Power BI is AnotherFunction.Contents which is already without any parameters.

Btw. I've also tested the official sample: https://github.com/microsoft/DataConnectors/blob/master/samples/NavigationTable without any modifications, and I get the same message, so I'm wondering if this is the expected behavior?

[Anonymous · Accepted solution]

Hi @petrbroz ,

Yes, this is the expected behavior when a function in a custom connector does not require any parameters. When you click the function in the navigation pane, Power BI will display a message indicating that no parameter values are specified because the function does not need any input from the user. This is a standard message to show that there are no parameters to pass for the function when you invoke it. 

Best Regards

[petrbroz]

Thank you @Anonymous. After some more research I've found that Power BI shows a slightly different message depending on whether the function accepts any parameters:

• If the function accepts no parameters, Power BI shows "No parameter values specified"
• If the function accepts parameters (doesn't matter whether it's documented or not), Power BI shows "Parameter value not specified"

I think the naming is a bit misleading. Also, it would be nice if the behavior was:

• If the function accepts no parameters, Power BI shows the results of calling the function
• If the function accepts parameters, Power BI shows UI to input these parameters (just like it does for the top level function), and then shows the results based on the provided inputs

 .

Source code:

[Version = "1.0.0"]
section TestConnector;

TestConnector = [
    Authentication = [
        Anonymous = []
    ]
];

TestConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = {"Test Connector", "Test Connector"},
    LearnMoreUrl = "https://powerbi.microsoft.com/"
];

[DataSource.Kind="TestConnector", Publish="TestConnector.Publish"]
shared TestConnector.Contents = () =>
    let
        records = {
            [
                Name = "Function with no params",
                Key = "f1",
                Data = FunctionWithNoParams,
                ItemKind = "Function",
                ItemName = "Function",
                IsLeaf = true
            ],
            [
                Name = "Function with params (undocumented)",
                Key = "f2",
                Data = FunctionWithParams,
                ItemKind = "Function",
                ItemName = "Function",
                IsLeaf = true
            ],
            [
                Name = "Function with params (documented)",
                Key = "f3",
                Data = Value.ReplaceType(FunctionWithParams, FunctionWithParamsType),
                ItemKind = "Function",
                ItemName = "Function",
                IsLeaf = true
            ]
        }
    in
        NavigationTable(records);

FunctionWithNoParams = () as table =>
    #table(
        {"Column"},
        {{"Foo"}}
    );

FunctionWithParams = (name as text) as table =>
    #table(
        {"Name"},
        {{name}}
    );

FunctionWithParamsType = type function (
    name as (type text meta [
        Documentation.FieldCaption = "My Param Name",
        Documentation.FieldDescription = "Parameter description..."
    ])
) as table meta [
    Documentation.Name = "My Function Name",
    Documentation.LongDescription = "Function description..."
];

NavigationTable = (records as list) as table =>
    let
        _table = Table.FromRecords(records),
        tableType = Value.Type(_table),
        newTableType = Type.AddTableKey(tableType, {"Key"}, true) meta 
        [
            NavigationTable.NameColumn = "Name",
            NavigationTable.DataColumn = "Data",
            NavigationTable.ItemKindColumn = "ItemKind",
            Preview.DelayColumn = "ItemName",
            NavigationTable.IsLeafColumn = "IsLeaf"
        ]
    in
        Value.ReplaceType(_table, newTableType);

Since you say this is the expected behavior, I'll accept your solution, and I'll submit my suggestions in https://ideas.fabric.microsoft.com. Thank you!

[Xaratas]

---

@petrbroz wrote:

I think the naming is a bit misleading. Also, it would be nice if the behavior was:

• If the function accepts no parameters, Power BI shows the results of calling the function
• If the function accepts parameters, Power BI shows UI to input these parameters (just like it does for the top level function), and then shows the results based on the provided inputs

 That is entirely possible, as many screenshots of different connectors show you. What the documentation did not tell you is that the line

Preview.DelayColumn = itemNameColumn

in the ToNavigationTable helper just does not work with functions! You need to remove it to have the function parameters shown.

When you have functions and other types, like tables, then you would need 2 different ToNavigationTable functions. One with, and one without that line.

Crossreference

https://stackoverflow.com/questions/79147989/functions-in-navigation-tables-for-power-bi-custom-conn...