Cyclic reference in Power Query

Hi all,

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)

1 Like

Hi @juanjo, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi all,

Finally I managed to solve it by using Data Flows, which has been a great discovery, it brings great things in terms of order and fix the incremental refresh that I need.

Available to share the knowledge in case anybody needs it.

Juanjo

Hi @juanjo, It’s great to know that you were able to find a solution with your query. Would you mind sharing the solution here to help other members who have the same issue? Thanks!

Well I would, but the magic occurs in Power BI Service, there’s no a pretty pbx to show, but I would resume that Data Flows is great if:

  • You use the same Data source in different reports (for example Calendar table or Sales fact table).
  • That data source is a Rest API. In this case you create the same query as a data flow and it allows to plan an incremental refresh, which is not possible in the power bi desktop.

I recommend all to investigate further and check if Data flow suits their needs.