Forum Discussion
Power BI incremental refresh from Shopcada store using API
Hello Perfecta,
We regret the inconvenience caused. Please consider raising a Microsoft support ticket for further investigation. You can explain all the troubleshooting steps you have taken to help them better understand the issue.
You can create a Microsoft support ticket with the help of the link below:
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticketIf this helps, please "Accept as solution" and drop a "Kudos" so other members can find it more easily.
Thank you.
Hi Perfecta
Based on available info, it sounds like the source doesnt fully support incremental refresh because its using an API.
The suggestions from Microsoft are to use a staging layer. Basically its means if you can move you API call to a SQL DB or something similar then that could work.
Another thing I would suggest is, can you move you Power Query M code to a Power BI Dataflow?
Dataflows sit in the cloud and they have slightly different behaviour to local queries. You can try moving the query to a Data flow and then connect to the data flow through desktop. The Data flow also supports incremental refresh. It should be easy to setup and you simply need to copy paste your query to a dataflow. Worth testing.
Thanks for the response.
However, the problem is still unresolved, so I’m providing more context below.
I'm currently using Power BI Pro (not Premium). In Power BI Desktop, I’m getting a query folding warning. But when I publish the report to the Power BI Service, the refresh fails completely.
I'm suspecting the issue is related to query folding, but I’m not sure how to resolve it under the limitations of the Pro license. I'm happy to share the M code if needed.
Can help guide me on how to fix this issue or work around it? Any support would be appreciated!
Thank you.
#Mcode:
let
StartDate = DateTime.ToText(RangeStart, "yyyy-MM-dd"),
EndDate = DateTime.ToText(RangeEnd, "yyyy-MM-dd"),
APIKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxx",
PageSize = 100,
MaxPages = 2000,
DelaySeconds = 1,
GetPage = (PageNum as number) =>
let
Response = Json.Document(
Web.Contents("xxxxxxxxxxxxxx", [
RelativePath = "xxxxxxxxxxxx",
Query = [
page = Text.From(PageNum),
limit = Text.From(PageSize),
created_at_min = StartDate,
created_at_max = EndDate
],
Headers = [#"xxxxxxxxxxx" = APIKey],
ManualStatusHandling = {429}
])
),
Status = Value.Metadata(Response)[Response.Status]?,
Result = if Status = 429 then error "Too Many Requests" else Response,
Orders = Result[orders],
Delayed = Function.InvokeAfter(() => Orders, #duration(0, 0, 0, DelaySeconds))
in
Delayed,
PageNumbers = List.Numbers(0, MaxPages),
AllPages = List.Transform(PageNumbers, each try GetPage(_) otherwise null),
FilteredPages = List.RemoveNulls(AllPages),
Combined = List.Combine(FilteredPages),
ToTable = Table.FromList(Combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Expanded = Table.ExpandRecordColumn(ToTable, "Column1", Record.FieldNames(Combined{0})),
ExpandedWithDate = Table.TransformColumnTypes(Expanded, {{"created_at", type datetimezone}}),
AdjustedCreatedAt = Table.AddColumn(ExpandedWithDate, "created_at_adjusted", each DateTimeZone.RemoveZone([created_at]), type datetime),
FilteredRows = Table.SelectRows(AdjustedCreatedAt, each [created_at_adjusted] >= RangeStart and [created_at_adjusted] < RangeEnd)
in
FilteredRows
Hello Perfecta,
We regret the inconvenience caused. Please consider raising a Microsoft support ticket for further investigation. You can explain all the troubleshooting steps you have taken to help them better understand the issue.
You can create a Microsoft support ticket with the help of the link below:
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket
If this helps, please "Accept as solution" and drop a "Kudos" so other members can find it more easily.
Thank you.