Power BI Custom Connector - How to manipulate existing data from navigation table

[stijnwingens]

Hi,

I've built a custom connector with a OAuth2 authorization code flow. The custom connector retrieves data from a REST API. My goal is to manipulate data based on the result of the REST request;

The API returns a record and a transaction on that record:

{
  [
     {
        "key": {
           <the-record>
        },
        "tranaction": "added"
     },
     {
        "key": {
           <other-record>
        },
        "tranaction": "deleted"
     }
  ]
}

This way, only the added, updated and deleted records need to be transferred. Then, the custom connector should add, update or delete the specified record to the dataset. I have no idea where to start. Right now, a navigation table is used calling GetFromApi("some-entity-type");

shared GetTableFromApi = (query as text) => 
    let
        subId = clientId,
        json = Json.Document(Web.Contents(base_url & "/sync/" & api_version & "/" & query & "?subscriptionId=" & subId))
    in
        Table.FromRecords(json);

How do I get this function to read the transaction type and add, update or delete the record in the existing dataset?

[stijnwingens]

So if I understand correctly, you are saying this is not possible. I would then have to look at using the api to push the data, which is also a possibility if I understand correctly? Or is this also confined to flush and fill? Thanks.

[lbendlin]

Sort of. While you can push individual rows you cannot modify rows that you have already pushed.  For that you need to  - you guessed it - delete all rows and push them again,

[lbendlin]

Then, the custom connector should add, update or delete the specified record to the dataset. 

Any connector, be it custom or not, is confined to the "flush and fill"  paradigm.  Power Query presents a table to Vertipaq, and Vertipaq incorporates that table in its data model.  That's it.  No adds, deletes, or updates.  Just flush and fill.  (ignoring self referencing datasets for a second).  Even incremental refresh is fundamentally flush and fill, just on partition level rather than table level.

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com/?forum=2d80fd4a-16cb-4189-896b-e0dac5e08b41