A cyclic reference error when use #shared

[andreyminakov]

Hi All!

I don't understand why PQ (in both Excel and Power BI Desktop) raises "Expression.Error: A cyclic reference was encountered during evaluation." in the code below. Error is raised regardless of Table.Buffer in the first step (i.e. with the Buffer and without it). 

let
    Source = Table.Buffer(Record.ToTable(#shared)),
    #"Filtered Rows" = Table.SelectRows(Source, each Value.Is([Value], type number)),
    Custom1 = List.Buffer(#"Filtered Rows"[Name])
in
    Custom1

I can remove List.Buffer, but if I use List.Accumulate instead, the error is raised for still unknown reason, even if it doesn't actually accumulate anything, like List.Accumulate(#"Filtered Rows"[Name], "", (s,c)=>s).

The error starts to be raised only if I filter by Value field, e.g. if I use #"Filtered Rows" step. But the error is raised if I filter the field in anyway, including variants like [Value] = 0.

If I filter by Name, all works without problems. What is the magic with Value field of #shared?

Please help!

[andreyminakov]

Hi Qiuyun! Yes, of course :-).

[v-qiuyu-msft]

Hi @andreyminakov,

My pleasure. As the issue is resolved now, may I close this thread? 

Best Regards,
Qiuyun Yu 

[andreyminakov]

Hi Qiuyun! Thanks a lot for your answer, it's very appreciated!

[v-qiuyu-msft · Accepted solution]

Hi @andreyminakov,

The reason it worked when Value.Is as [Name] because when it is type text the value of it is not necessary to pass it to the query.

But when type as number it is necessary to pass the value column as well which makes it to go into an infinite loop.

When you use Value.Is([Value], type number), please add a step before this step to filter out all tables which exist in current report. For example: 

Update: In my sample, as other two queries don't have error. You can just filter out current query table, eg: 

#"Filtered Rows1" = Table.SelectRows(Source, each  [Name]<>"Query1")

But if some other tables contain error, we need to filter out them to avoid issue. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @andreyminakov,

I have consulted this issue internally, will update here once I get any information. 

Best Regards,
Qiuyun Yu