Forum Discussion
Incremental Refresh for Dataflow calling Source API Multiple Times
let
Source = Json.Document(Web.Contents(Url)),
Navigation = Source[items],
#"Converted to table" = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Columns" = Table.ExpandRecordColumn(#"Converted to table", "Column1", {"dateTime", "date", "value", "valid", "invalid", "missing", "completeness", "quality"}, {"DateTime", "Date", "Value", "valid", "invalid", "missing", "completeness", "quality"}),
#"Changed column type" = Table.TransformColumnTypes(#"Expanded Columns", {{"DateTime", type datetime}, {"Date", type date}, {"Value", type number}, {"valid", Int64.Type}, {"invalid", Int64.Type}, {"missing", Int64.Type}, {"completeness", type text}, {"quality", type text}}),
#"Inserted time" = Table.AddColumn(#"Changed column type", "Time", each DateTime.Time([DateTime]), type nullable time),
#"Added TID" =
Table.AddColumn(
#"Inserted time",
"Time_ID",
each
Text.PadStart(
Text.From((
Number.Round(
Number.From(
Time.From(Number.Round(Number.From(Time.From([DateTime]))*(24*60/15))/(24*60/15)) //Create rounded 15 min time interval (96 divisions for TimeslotID)
)
*96)
+1))
,2,"0")),
#"Added TSID" = Table.AddColumn(#"Added TID", "Timeslot_ID", each Text.Combine({
Text.From(Date.Year([DateTime])),
Text.PadStart(Text.From(Date.Month([DateTime])),2,"0"),
Text.PadStart(Text.From(Date.Day([DateTime])),2,"0"),
Text.From([Time_ID])
})),
#"Change TimeID type" = Table.TransformColumnTypes(#"Added TSID", {{"Timeslot_ID", Int64.Type}, {"Time_ID", Int64.Type}}),
#"Removed columns" = Table.RemoveColumns(#"Change TimeID type", {"Time_ID"}),
#"Reordered columns" = Table.ReorderColumns(#"Removed columns", {"DateTime", "Date", "Time", "Timeslot_ID", "Value", "valid", "invalid", "missing", "completeness", "quality"}),
#"RF_Parkend-incremental_refresh" = Table.SelectRows(#"Reordered columns", each DateTime.From([DateTime]) >= RangeStart and DateTime.From([DateTime]) < RangeEnd),
#"RF_Parkend-4461746554696D65-autogenerated_for_incremental_refresh" = Table.SelectRows(#"RF_Parkend-incremental_refresh", each DateTime.From([DateTime]) >= RangeStart and DateTime.From([DateTime]) < RangeEnd)
in
#"RF_Parkend-4461746554696D65-autogenerated_for_incremental_refresh"
I'm fairly sure I see the problem here: incremental refresh will only make your refresh faster if the date/time filter is passed back to the data source, otherwise you're going to be loading all the data from the source and filtering it in the Power BI Service. I can see that the filter is not being passed back to the source here: the URL in your code is asking for all the data between 1920 and 2050:
https://environment.data.gov.uk/hydrology/id/measures/5adcd239-4420-40b5-abe2-69082f9e24ff-rainfall-t-900-mm-qualified/readings.json?mineq-date=1920-01-01&max-date=2050-01-01&_limit=2000000
You need to rewrite your code so the date filters specified in RangeStart and RangeEnd are passed back to the mineq-date and max-date parameters in the URL.