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 https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. 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.