Circular Query

[Tom12347821]

I get the below message when looking at the data model on power bi service. It doesn't show up on desktop version. is there an way to identifiy exactly what it's refering to so I could fix?

Circular query chain present. Member name= Parameter4. Index = 383

Thanks,

[gareauk · Accepted solution]

I encountered the same error and resolved it by adjusting my parameters. Initially, my parameter was designed to either pull in the top 10,000 rows or display the table name.  I did this to reduce the table sizes. I corrected this by changing the parameter to point directly to the source instead of the table name.  After republishing and refreshing, the error disappeared. I hope this helps someone.

[gareauk · Accepted solution]

I encountered the same error and resolved it by adjusting my parameters. Initially, my parameter was designed to either pull in the top 10,000 rows or display the table name.  I did this to reduce the table sizes. I corrected this by changing the parameter to point directly to the source instead of the table name.  After republishing and refreshing, the error disappeared. I hope this helps someone.

[cdebackere]

For me I have the same problem, but I can't get it solved.

In my case the query / function is indeed recursive on purpose. A single DB API call is limited in number of records to lets say 1000. So when I call the function, it always asks for max 1000 to the DB API, and then the rest is called from a recursive call with record 1000/2000/3000.... as starting point.

This works perfectly in Desktop.

But now when published to the service I get this error message.

Any idea? 

[lbendlin]

what error message?

[cdebackere]

Actual error message in Service:

getTEobjects is one of those function query's using this recursive mechanism

[lbendlin]

If this behaves differently on the desktop and the service you can raise it as a bug. Do not be surprised when they tell you that it should never have worked on the desktop either.

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

[cdebackere]

OK, thanks.

"never intended": I can indeed imagine and even undestand such answer to a degree. Since power m is not a sequential interpreted language, I can only guess the complexity behind the scenes to optimise the queries like power bi does.

In any case, looks like waiting for a solution for that recursive seems a long term thing at the best. 

So I'm going to look to rewrite the code with some form of iterator.  I used the recursive call because this was advised in some other forum topic. But a search reveils there is also a "for list" iterator. So I'll dig into and  analyse that alternative for a recursive call.

thx for your clear answers

[lbendlin]

Read Ben Gribaudo's Primer.  He specifically mentions the complexities and design limitations.  In the vast majority of cases an iterator is preferable to a recursion, especially when large amounts of data are involved.

[cdebackere]

found it, including his loop & recursive replacement constructs. Thx

For me, this is the 'true' solution to this tread .. sorry I can't mark ik anymore

[lbendlin]

you can post the link 🙂

[cdebackere · Accepted solution]

My pleasure.

In fact, I wasn't sure why you didn't do it .. matter of checking my willingness to search or forum policy or ..

Anyway: this is the part of his primer dealing with 'control structure' in power query

https://bengribaudo.com/blog/2020/01/06/4844/power-query-m-primer-part14-control-structure

Cheers,

C.

[cdebackere]

for those interested, this the construdt I now made as a for-like approach to replace the recursive call

it(s kinda 'pseudo code' as in 'not with the actual webcalls, but the construct is working code

let
  //function generates the xml to get part 'loop' of the answers
  genXML = (loop ) => "<node>" & Text.From( loop) & "</node>",
  //funtion get the answers for the given xml
  getWebresult = (path,xml) => Table.FromRecords({[path=path,xml=xml],[path=1,xml=2]}),

  //get the first partial answer
  result1=getWebresult("APIendpoint",genXML(1)),
  //extract the number of total loops from the first answer
  maxCnt = 11,

  //extract the additional parts of the answer and combine
  results = if maxCnt=1
  then
    result1
  else
    let
      Bron = List.Numbers(2,maxCnt-1),
      stap1 = List.Transform(Bron,each getWebresult("APIendpoint", genXML(_))),
      //convertert stap1
      stap2=Table.Combine(List.InsertRange(stap1,0,{result1}))
    in
      stap2
in
  results

[Tom12347821]

Thanks I did managed to resolve in the end. It was the parameters that were auto generated from connecting to an excel online in the past. I removed all the sample files/paratmeter bits and reconnected the affected tables which fixed.

[lf2606]

Hey were you able to solve it?

[Tom12347821]

not yet still have a ticket open with support

[lbendlin]

That's a new one.

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

[Tom12347821]

Thanks i have raised a ticket so will see what they say