In my model I am importing the sales from a REST API. When calling the API you need to parameters:
- URL: An URL is parsed with information of the content and the period of time required.
- Credentials: Each business location has its own credentials (token).
There are restrictions, so it’s common to get an error message such as ‘too many request errors’ when requesting 2 or more locations at the same time.
As the incremental refresh does not work for these type of web queries, I am trying to implement the following workaround:
- Create different staging queries with historical transactional information, one for each combination of location and month. Location 1 May 20, Location 2 May 20, etc.
- Disable ‘Enable Load’ for those queries
- Create a ‘current month query’ for each location, building dinamically the dates parameters within the URL.
- Theese ‘Current month query’ will have ‘Enable load’ active.
- Append all the tables in a Combined Sales Fact table.
To get the URL dinamically created I have created 3 functions that get the following info:
- Today in the text format: “2020-06-17”
- LastLoad: Last day that the data was loaded.
The problem is when I construct the URL by using LastLoad and Today (as start and end dates) Power BI indicates “Expression.Error: A cyclic reference was encountered during evaluation”.
How could I solve it?
Is there a better way to efficiently update the data just with new transactional info?
See attached the file, as you would see the credentials are visible (I tried to hide but I couldn’t), anyway it’s for a non active location and historical data is not sensible.
ThanksSales DNA.pbix (428.1 KB)