Auto navigation/transformation on results from function calls exposed by OpenApi.Document()

[MikeTheSnowman]

Preflight Checklist

• I have installed the latest version of Power Query SDK.
• I have checked existing resources, including the common issues and the release notes.
• I have searched for similar issues.

Problem

TL/DR:

I want an easy way to automatically apply some kind of navigation/transformation on data retrieved from a REST API function call, exposed by OpenApi.Document().

Detailed Explanation:

I have created a custom connector for an API that my company develops. My connector works by using OpenApi via swagger document, which lists the APIs as functions with parameters. Below is the code:

[DataSource.Kind="FortifySSC", Publish="FortifySSC.Publish"]
shared FortifySSC.Contents = (optional message as text) =>
    let
        credential = Extension.CurrentCredential(),
        RootUrl = credential[Username],
        BaseUrl = RootUrl,
        TokenServiceUrl = BaseUrl & "/api/v1/tokens",
        ServiceJsonUrl = BaseUrl & "/api/v1/spec.json",
        headers = [ Authorization = "FortifyToken " & credential[Password] ],
        navTable = OpenApi.Document(Web.Contents(ServiceJsonUrl), [ Headers = headers, ManualCredentials = true, IncludeMoreColumns = false, IncludeExtensions = true ])
    in
        navTable;

And things work as expected when seeing all the functions below:

The bit of trouble starts here, when you click on the function to try and start retrieving data. As you can see, I'm not getting the data directly, but need to apply a transformation to the retrieved data by navigating to the data attribute.

And then finally after navigating/transforming a user can access the actual data:

Desired Solution

I want an easy way to automatically apply some kind of navigation/transformation on data retrieved from a REST API function call, exposed by OpenApi.Document().

Maybe OpenApi.Document() can accept an additional parameter where I can pass in a function that performs the desired navigation/transformation? Example:

navTable = OpenApi.Document(Web.Contents(myUrl), myHeaders, myAutoNavigationFunction)

Alternatives and Workarounds

No response

Additional Context

No response

[MikeTheSnowman]

Also, as a follow up question, where's the documentation for OpenApi.Document()? I looked all over the website for the M language reference, but I can't find any information on this function other than in some code examples. It's really weird that there's no documentation for this when it's so heavily used. 🤨

[mattmasson]

The only documentation for OpenApi.Document() that I'm aware of is our samples: https://github.com/microsoft/DataConnectors/tree/master/samples/OpenApiSample

OpenApi.Document() will give you a "Function" focused connector. OpenApi (or at least, Swagger... I haven't looked up to see how the standard has evolved) doesn't have conventions for paging, querying data, and a number of other things you'd want a data wrangling experience like Power Query to take advantage of... these are things that you'd get from something like OData, which is a much better base for PQ connectors.

OpenApi.Document returns a navigation table, which is a Table you can manipulate with M like any other data table. You just have to remember that if you transform it in a way that changes its type (add/remove/rename columns, change column data types), it loses the type metadata that makes it a nav table, and you have to call a function like Table.ToNavigationTable to recreate those properties.

If you want to further manipulate the functions you get back from OpenApi.Document, the interesting columns are:

• [Name] : this will be the name of the action (ex. listProject)
• [Data] : this will be the function value for the action. Calling [Data]() would invoke that function.

To provide additional transformations on top of the functions, you have two main options.

You can add special handling for specific functions. We do this in one of the samples for the listAPIs function. Here, you're basically using OpenApi.Document to handle the GET requests and read the JSON response, but you're going build your own nav table for the end user (or just return the data if you're only interested in a single result).

originalNavTable = OpenApi.Document(Web.Contents(ServiceJsonUrl), [ Headers = headers, ManualCredentials = true, IncludeMoreColumns = false, IncludeExtensions = true ]),
listProjectFunc = originalNavTable{[Name="listProject"]}[Data],
listProjectInvoke = listProjectFunc(),
listProjectResult = listProjectInvoke[data]
// whatever else you have to do to listProjectResult to turn it into a nicely formatted table

Alternatively, if all of the actions follow similar patterns (i.e. they all return a data field), you can wrap another function around your [Data] column.

Something like...

   originalNavTable = OpenApi.Document(Web.Contents(ServiceJsonUrl), [ Headers = headers, ManualCredentials = true, IncludeMoreColumns = false, IncludeExtensions = true ]),
   modifiedData = Table.TransformColumns(originalNavTable, {{ "Data", (func) => func()[data] }} ), // call func and return [data] field
in
   modifiedData

For what it's worth, only 2 of our 100 or so certified connectors use OpenApi.Document() today. I think the function works fine for prototyping, or a "quick and dirty" connector targeted at developers, but if you're going to the trouble of manipulating/transforming the results, you might as well take the standard REST API approach and do it all with Web.Contents and Json.Document calls.

Based on the function parameters you shared (count, start, limit), I suspect you'd soon hit problems with paging.