Forum Discussion

ismael_spadetto's avatar
ismael_spadetto
Frequent Visitor
3 years ago

Incremental Refresh for Dataflow calling Source API Multiple Times

We have Dataflows that utilise API Sources that we would like to refresh incrementally and the incremental refresh is not working as expected. The issue we are seeing is that each time the increme...
Dataflow
cpwebb's avatar
cpwebb
Microsoft Employee
2 years ago

Sorry, my mistake - I should have said to use the Query option for RangeStart and RangeEnd, and not the RelativePath option.

OllieSvT's avatar
OllieSvT
Regular Visitor
2 years ago

No worries at all, all guidance is super appreciated 🙂 I am still trying to make sense of this and found the relevant guidance pages on the Microsoft pages but won't lie that I am struggling with the correct syntax. Would you be able to demonstrate the structure of the query for the two Range sections and how to wrap the other parts of the url to this? Thanks

  • cpwebb's avatar
    cpwebb
    Microsoft Employee
    2 years ago

    Here's a version of the Power Query query which I put together and which works for me:

     

    let
    RangeStartText = DateTime.ToText(RangeStart, [Format="yyyy-MM-dd"]),
    RangeEndText = DateTime.ToText(RangeEnd, [Format="yyyy-MM-dd"]),
    CallWebService = Json.Document(Web.Contents("http://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", [Query=[#"mineq-date"=RangeStartText, #"max-date"=RangeEndText, #"_limit"="2000000"]])),
    items = CallWebService[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"measure", "date", "dateTime", "value", "valid", "invalid", "missing", "completeness", "quality"}, {"measure", "date", "dateTime", "value", "valid", "invalid", "missing", "completeness", "quality"}),
    #"Expanded measure" = Table.ExpandRecordColumn(#"Expanded Column1", "measure", {"@id"}, {"@id"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded measure",{{"@id", type text}, {"date", type date}, {"dateTime", type datetime}, {"value", type number}, {"valid", Int64.Type}, {"invalid", Int64.Type}, {"missing", Int64.Type}, {"completeness", type text}, {"quality", type text}}),
    HandleError = try #"Changed Type" otherwise #table(type table [#"@id" = Text.Type, date = Date.Type, dateTime = DateTime.Type, value = Number.Type, valid = Int64.Type, invalid = Int64.Type, missing = Int64.Type, completeness = Text.Type, quality = Text.Type], {}),
    #"Added Custom" = Table.AddColumn(HandleError, "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)) 
    )
    *96)
    +1))
    ,2,"0")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Time_ID", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "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])
    })),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Timeslot_ID", Int64.Type}})
in
    #"Changed Type2"

     

    Apart from the code at the beginning for passing the RangeStart and RangeEnd parameters back to the API, the other challenge was handling the situation where the API doesn't have data for the requested date and returns no data at all (rather than an empty table) - I'm handling this by using a try...otherwise statement and returning my own empty table when that happens. Can you let me know if this works for you?