Custom connector development FAQ

Outside of documented warning patterns, we currently don't provide a way to return out of band warnings, such as a large table or large metadata warning.

It's possible if end users frequently want to retrieve a single partition of data. However, this functionality can't be added to an already existing connector. In essence, this change would break an already existing connector.

There could be several reasons why you're seeing this behavior. Some common errors that might occur while running the connector on Power BI service are:

• Failed to update data source credentials
• An exception occurred while trying to get OAuthProvider from DataSourceReference
• The given data source kind isn't supported

Before you begin troubleshooting this behavior, first collect a copy of the custom connector (.pq or .mez file). If you have a .mez file, rename the file to .zip and extract the .pq file.

To troubleshoot the custom connector:

1. Open the custom connector file (.pq) in a text editor of your choice.

2. Find the TestConnection function. The TestConnection function is required for scheduled refresh in the Power BI service, but isn't used in Power BI Desktop. Check the .pq file for a TestConnection implementation, and confirm that the parameters match the connector's data source function. More information: Handling gateway support

3. If the connector uses OAuth, check for the state parameter. A common cause of service-only failures is a missing state parameter in the connector's StartLogin implementation. This parameter isn't used in Power BI Desktop, but is required in the Power BI service. The state parameter must be passed into the call to Uri.BuildQueryString. The following example demonstrates the correct implementation of state.

StartLogin = (resourceUrl, state, display) =>
  let
      authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
          response_type = "code",
          client_id = client_id,
          state = state,                 //correct implementation
          redirect_uri = redirect_uri,
          resource = resource
      ])
  in
      [
          LoginUri = authorizeUrl,
          CallbackUri = redirect_uri,
          WindowHeight = 720,
          WindowWidth = 1024,
          Context = null
      ];

This behavior might be a side effect of how you're building your navigation table. If you're creating new records with Table.TransformRows, this usage usually results in eager evaluation of the data tables. However, values produced by Table.AddColumn are produced lazily. so, in the following example code, "each GetSchemas(url, [name])" won't be evaluated unless the user query actually references this data.

GetShares = (server_host as text) as table =>
    let
        url =  server_host & "/shares",
        shares = GetItems(url),
        withData = Table.AddColumn(shares, "Data", each GetSchemas(url, [name])),
        withItemKind = Table.AddColumn(withData, "ItemKind", each "Folder"),
        withItemName = Table.AddColumn(withItemKind, "ItemName", each "Folder"),
        withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each false),
        renamed = Table.RenameColumns(withIsLeaf, {{"name", "Name"}, {"key", "Key"}}),
        navTable = Table.ToNavigationTable(renamed, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        navTable;

This behavior is a side effect of using Table.Combine. An alternative method is to build a "table of tables" and use the Table.ExpandTableColumn function. This method lazily expands partitions as needed. For example:

GetFiles = (tables_url as text, table_name as text) as table =>
    let
        // parse raw ndjson and get the list of parquet files
        // resp format: line 1: protocol, line 2: schema, line 3..:file info
        resp = Lines.FromBinary(SendRequest(tables_url & "/" & table_name & "/query", [
            Headers= [#"Content-Type"="application/json"],
            Content= Text.ToBinary("{}")
        ]), null, null, 1252),
        protocol = resp{0}, // TODO: Add protocol version check
        schema = Json.Document(Json.Document(resp{1})[metaData][schemaString])[fields],
        columnNames = List.Transform(schema, each [name]),

        fileInfos = List.Range(resp, 2),
        fileUrls = List.Transform(fileInfos, each Json.Document(_)[file][url]),
        numFiles = List.Count(fileUrls),

        toTable = Table.FromList(fileUrls, Splitter.SplitByNothing(), {"FileUrl"}),
        processPartition = Table.AddColumn(toTable, "Data", each Parquet.Document(Binary.Buffer(ProtectSensitiveQueryParameters([FileUrl], [ManualCredentials = true])))),
        removeFileUrl = Table.RemoveColumns(processPartition, {"FileUrl"}),
        expanded = Table.ExpandTableColumn(removeFileUrl, "Data", columnNames)
    in
        if numFiles = 0 then #table(columnNames, {}) else expanded;

ProtectSensitiveQueryParameters = (url as text, options as record) =>
    let
        uriParts = Uri.Parts(uri),
        uriWithoutQuery = Uri.FromParts(uriParts & [Query = []]),
        modifiedOptions = options & [
            CredentialQuery = uriParts[Query],
        ]
    in
        Web.Contents(uriWithoutQuery, modifiedOptions);

Additional information