Forum Discussion
Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd
- 2 years ago
Finally we found the solution:
It is all up to query folding. The article helped a lot:
https://bengribaudo.com/blog/2022/01/20/6500/power-query-m-primer-part-23-query-folding-i
One does not need to define RangeStart nor RangeEnd parameters anywhere in the connector.
Instead one should implement Table.View as shown here:
https://learn.microsoft.com/en-us/power-query/samples/trippin/10-tableview1/readmeThe only missing information is that one need to implement OnSelectRows.
Further information:
https://community.fabric.microsoft.com/t5/Power-Query/Query-folding-Table-View-handler-function-to-filter-rows-add/td-p/3154149br, Michael
Here is the full code:
[Version = "1.0.0"]
section Foo;
[DataSource.Kind = "Foo", Publish = "Foo.Publish"]
shared Foo.Contents = Value.ReplaceType(FooNavTable, type function (Url as Uri.Type, optional RangeStart as nullable datetime, optional RangeEnd as nullable datetime) as any);
DefaultRequestHeaders = [
#"Content-Type" = "application/json",
#"api-key" = Extension.CurrentCredential()[Key]
];
FooNavTable = (url as text, optional rangeStart as nullable datetime, optional rangeEnd as nullable datetime) as table =>
let
source = Json.Document(Web.Contents(url, [Headers = DefaultRequestHeaders, RelativePath = "navTable"])),
tablesList = List.Transform(source, each
[
Name = _[Name],
KeyColumn = _[KeyColumn], // Der Schlüssel sollte eindeutig sein
Data = GetTable(url, Name, KeyColumn, _[Schema], rangeStart, rangeEnd),
ItemKind = "Table",
ItemName = "Table",
IsLeaf = true
]),
tablesTable = Table.FromRecords(tablesList),
navigationTable = Table.ToNavigationTable(tablesTable, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
navigationTable;
GetTable = (url as text, tableName as text, keyColumn as text, schema as record, optional rangeStart as nullable datetime, optional rangeEnd as nullable datetime) as table =>
let
startDate = if rangeStart = null then "" else DateTime.ToText(rangeStart, "yyyy-MM-dd"),
endDate = if rangeEnd = null then "" else DateTime.ToText(rangeEnd, "yyyy-MM-dd"),
source = Json.Document(Web.Contents(url, [Headers = DefaultRequestHeaders, Query=[rangeStart=startDate, rangeEnd=endDate], RelativePath = tableName])),
toTable = Table.FromRecords(source),
typedTable = SetDataTypes(toTable, schema),
typedTableWithKey = Table.AddKey(typedTable, { keyColumn }, true)
in
typedTableWithKey;
SetDataTypes = (table as table, schema as record) as table =>
let
schemaTable = Record.ToTable(schema),
transformationList = List.Transform(Table.ToRows(schemaTable),
each {_{0}, GetTypeFromString(_{1})
}),
transformedTable = Table.TransformColumnTypes(table, transformationList)
in
transformedTable;
GetTypeFromString = (typeString as text) as type =>
let
result = if typeString = "number" then Int64.Type
else if typeString = "string" then Text.Type
else if typeString = "boolean" then Logical.Type
else if typeString = "Date" then Date.Type
else if typeString = "DateTime" then DateTime.Type
else if typeString = "Time" then Time.Type
else if typeString = "Decimal" then Decimal.Type
else Any.Type
in result;
Everything in Power Query is case sensitive. "rangeStart" and "rangeEnd" will not work.
You must hand over the URL already precompiled. The Power BI service must be able to inject the RangeStart and RangeEnd values directly.
- emikelsoft2 years agoHelper I
RangeStart and RangeEnd is upercase at the shared function. Do you mean it should be upercase on the other functions as well?
Or can you give an example to see where exactly those params get injected?
By the way, the url can not be precompiled because we need our customer to connect to his individual endpoint.
Many thanks for your help so far.
- lbendlin2 years agoSuper User
Incremental refresh only works on partition level. I cannot see how you would be able to add additional filters like your customer name. Are you expecting this to address different partitions?
Let's take a step back. What is your primary reason for trying to use Incremental Refresh?
- emikelsoft2 years agoHelper I
We want to reduce the amount of data to be transfered. We configured to load factData for the last 5 years into the archive partition, so only once. The last 3 month should load on every refresh to get the latest data. So we applied incremental refresh on a timestamp-columd.