Incremental & Refresh Help (JSON Web API)

[pe2950]

Edited Question: Asking about why automatic refresh is failing also...

1. I'm wondering why the powerbi service will not permit an automatic refresh?
2. I'm wondering if it possible to use incremental refresh against this specific web API, and if the performance gains will be noticeable?

Data Details:

• Table contains around 5k rows, growth is around 1k rows/year
• I must pull them from a web API as JSON data
• Each page of results returns 25 rows (requiring 200 calls for the full set at this time)
• The API requires me to call a pre-built view (akin to a filter) to return a set of data (it does not allow the API to specify a filter)
• I can sort the returned results by "updated_at" asc/desc

Specifically my question:

1. Automatic refresh fails "Dynamic URL" issue, however i have followed code samples and are unable to find my error.
2. Given that I am unable to pass in a RangeStart and RangeEnd parameter. Is it possible to configure an incremental refresh that returns the data sorted by updated_at, and to stop when it reaches a record that is older then the last refresh date/time?
3. Alternativly, if i were to build a "view" that says "return only rows updated today (or similar)" is there a way to fetch only that data as a work-around to improving the load performance?

My current query:

let 
    BaseUrl         = "https://lasereye.freshsales.io/api/deals/view/300112/",
    Token           = "XXXXX",
    EntitiesPerPage = 25,
    Include = "owner",
 
    GetJson = (QParam) =>
        let Options = [Headers=[ #"Authorization" = "Token token=" & Token ], Query = QParam],
            RawData = Web.Contents(BaseUrl, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let QParam   = [include=Include],
            Json  = GetJson(QParam),
            Count = Json[meta][total_pages]
        in  Count,
 
    GetPage = (Index) =>
        let Page  = "page=" & Text.From(Index),
            QParam   = [page=Page,include=Include],
            Json  = GetJson(QParam),
            Value = Json[deals]
        in  Value,
 
    EntityCount = List.Max({ EntitiesPerPage, (GetEntityCount() * 25 ) }),
    PageCount   = GetEntityCount(),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column2" = Table.ExpandRecordColumn(Table, "Column1", {"id", "name", "amount", "base_currency_amount", "expected_close", "closed_date", "updated_at", "created_at"}, {"Column1.id",  ....}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column2",{{"Column1.created_at", type datetimezone}, {"Column1.closed_date", type date}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Column1.created_at]), type date),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Date",{{"Date", "created_at1"}, {"Column1.id", "ID"}, {"Column1.name", "Title"}, {"Column1.amount", "Amount"}...}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column1.forecast_category", "Column1.avatar", "Column1.deal_freddy_metrics", "Column1.tags"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Column1.is_deleted] = false)
in
    #"Filtered Rows"

 (Removed some of the expand/rename to keep it read-able.

Details on the web API: 
https://developer.freshsales.io/api/#list_all_deals

[lbendlin]

This API is too rigid for Power BI.  Pull the data with a different tool. As you suggested use the view Updated_at/descending for the cutoff.

[pe2950]

Suggestion on tool / where to store data?

I was thinking simple python script to pull into an excel sheet given the small amount of rows?

[lbendlin]

A CSV file on a OneDrive, or a Sharepoint List.  Excel is not a good data storage vehicle.