Hi,
Hope you can help as I'm new to dataflows!
I have setup a dataflow to fetch data from a paginated API. I have a query that will fetch a list of all customers from the API, for each of the customer id's a function GetData is called, which again calls GetPage to fetch each page. When limiting the range to 14 days of data, 800 pages are fetched (i.e. GetPage is called 800 times). See below for the scripts.
The issue is that there is a big difference in how long it takes to refresh the data:
Power BI Desktop (running on Azure Standard B4ms, 4 vcpus, 16 GiB memory) - 30 seconds
Power BI Service - 3 minutes
Power BI Dataflow - 15 minutes
The refresh time is managable with only 14 days of data, but this dataset will grow linearly, so in a few months the refresh time would be several hours. We don't have Premium, so I'm not able to use incremental load for dataflows.
Any idea of what is causing the low performance of dataflows? I've heard there's a minimum 30 sec query runtime in dataflows, could it be that somehow this applies to the function calls?
Here are the scripts (I have replaced the API url):
Data query:
let
Source = Json.Document(Web.Contents("https://api.example.com/customers", [Headers=[Authorization="Bearer " & Token]])),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"id"}),
#"Invoked Custom Function" = Table.AddColumn(#"Expanded Column1", "data", each GetData([id],"/sales"))
in
#"Invoked Custom Function"
GetData function:
let
Source = (CustomerId as number, Path as text) => let
Source = {1..Record.Field(GetPage(CustomerId,1, Path),"last_page")},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Invoked Custom Function" = Table.AddColumn(#"Converted to Table", "Data", each GetPageRange(CustomerId, [Column1], Path)),
#"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Column1"})
in
#"Removed Columns"
in
Source
GetPage function:
let
Source = (CustomerId as number, Page as number, Path as text) => let
Source = Json.Document(Web.Contents("https://api.example.com/customers/", [RelativePath= Number.ToText(CustomerId) & Path, Query = [page=Number.ToText(Page), from=rangeStart, to=rangeEnd], Headers=[Authorization="Bearer "& Token]]))
in
Source
in
Source
