Use a table value as a filter parameter when Refreshing the same table

[Anonymous]

Hi All

I have built a Custom Connector in VS for use in Power BI to call a Web Service that will pull in data from one of our company's systems.

The Web Service will pull in data from whichever table is specified in the Web Service request (the web service can pull data from anywhere in the system).

On average, there will be around 20 individual tables imported from the source system, all called from the same Web Service with a variable Table code.

My issue is with the Refresh of the data and we cannot use the Incremental Refresh function.

Some tables will hardly ever change and same tables the data could change on a minute by minute basis. I have included a column in the table of the datetime the individual records were last updated.

The Web Service does allow for a 'Filter' to be specified in the Web Service call, so as part of the message, we can include something along the lines of  "import where updated >= datetime value', so we are only getting the records that have changed.

I'm struggling to find a way, if its even possible, that when the Refresh is initiated:

1. Determine the MAX last_refresh date from the current table contents in Power BI (this part is easy enough in Power BI Query Editor)
2. Write that value to variable for use as a parameter
3. Call the Web Service, passing the variable from point 2 in as a parameter to the Web Service, where I can reference that value in the Web Service filter.
   1. The Web Service will already receive the correct Table Code on a Refresh, I'm trying to work out how to obtain other table data at the point of Refresh

Considerations:

1. While a full project refresh will likely happen once a day, it is possible for users to refresh Individual/specific tables and not refresh others, so the last updated date Must be derived from the table being refreshed

I have found a partial solution of adding a Table that holds the Last Refresh Date, but that would then require the full project and all the tables to be updated together, which isn't particuarly efficient in this project considering some tables have 12 rows and others have 1m.

Does anyone have a solution of being able to refresh individual tables taking a value from the same table at runtime before calling the Web Service?

Any suggestions welome.

[watkinnc]

First, make a new step in your table query like:

MaxDate = List.Max(#"Table Name"[DateTime])

Now you have your filter value, which is the latest datetime value in the column.

Now let's say that prior to us adding this step, your final step was named "Table":

NewParam = Table.SelectRows(Table, each Date.From(DateTime.LocalNow()) >= MaxDate)

--Nate

[Anonymous]

Thanks for the suggestion Nate, although I think my connector is slightly more complicated as the Web Service is also wrapped in Pagination logic:

This is the snipped from the Connector that calls the XML Web Service, with your suggested code added:

XMLWebService = (hostUrl as text, gridCode as text, optional filterField as text,  optional RangeStart as datetime, optional RangeEnd as datetime)  =>

let
    hostUrl = hostUrl,
    gridCodeString = """" & gridCode & """",
    filterFieldString = """" & filterField & """",
    filterFrom = if RangeStart = null then """1900-01-01""" else """" & RangeStart & """",
    filterTo = if RangeEnd = null then """""" else """" & RangeEnd & """",
    XMLUserName = Extension.CurrentCredential()[Username],
    XMLUserPassword = Extension.CurrentCredential()[Password],
  //  GetXML =  GetXMLData(hostUrl,XMLUserName, XMLUserPassword, gridCodeString,1, filterFieldString, RangeStart, RangeEnd),
    Pagination = List.Skip(List.Generate( () => [IsMore = null, Last_Key = 0, Counter = 0], // Start Value
                    each [IsMore] <> false,// Whilst this is true, keep going
                    each [GetXML =  GetXMLData(hostUrl,XMLUserName, XMLUserPassword, gridCodeString,Last_Key, filterFieldString,MaxDate, RangeEnd),

    Last_Key = try [GetXML][#"cursorPosition"] otherwise 0,
    IsMore = if [Counter] < 1 then null else [GetXML][#"hasMore"],
    Counter = [Counter]+1,

    splitList = Table.FromRows(GetXML[rows], GetXML[fieldNameList]),
    #"Transform Types" = Table.TransformColumnTypes(splitList, List.Zip( {GetXML[fieldNameList], GetXML[fieldDataTypes] } )),
    MaxDate = List.Max(#"Transform Types"[last_refreshed])
    ]
    ,each [#"Transform Types"] // selector
) ,1)
    
    , Custom = Table.Combine(Pagination)
   // output = GetXML

    in Custom ;

This on its own works fine. The RangeStart and RangeEnd parameters are there from my trial and error and will likely be removed when this is all working.

Where i'm struggling with your suggestion is the the last step which creates the table is in the Pagination Logic, and perhaps i'm looking at this too much through my SQL glasses where you have to specify which table to get the value from and SQL evaluates in the order its written.

I added the MaxDate which is after the #"Transform Types" (this step takes the table of data and forces the data types to match the incoming data types as PowerBI, surprisingly, gets them all wrong.)

I then referenced the MaxDate further up in the Pagination item > GetXML function parameters (the GetXML function is the full XML Web Service code which is where the filter is used.

When trying to refresh in PowerBI, i'm getting a cyclic reference error (in this image, 'Property' is the name of the table being refreshed).

In my mind I sort of know what it means (i'm guessing its like a circular reference error in Excel or SQL), but as i'm new to Power Query/Power BI, i'm not sure where to go from here.

 Ultimately, in my test environment, I have already loaded the 'Property' table and the 'last_refreshed' is available.

what it needs to to is:

1. Click Refresh (or scheduled refresh)
2. Get the Max date from the data that is already loaded (in this case, 24/07/2021 4:28:52 AM)
3. Pass that date to the Web Service so it will only get new records from the Source system where the data was updated on or after 24/07/2021 4:28:52 AM