none
A cyclic reference error when use #shared RRS feed

  • Question

  • 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!

    Friday, January 25, 2019 11:43 AM

Answers

  • Hi Andrey. The reason you're getting cyclic reference errors is that #shared includes the values of the queries in the current file (including the current query). So if the [Value] of the current query is accessed, this will result in a cyclic reference error (since it's trying to use the value of the current query to compute the value of the current query). As long as you can avoid pulling on the [Value] of the current query, things will work.

    Here's one way to make your original query work. Simply filter out the current query (currently assumed to be named "Query1" in the below code) before filtering by the data type of [Value].

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

    Hope that helps.
    Ehren

    Tuesday, January 29, 2019 12:10 AM
    Owner

All replies

  • Hi Andrey,

    I have no idea what's wrong with Value field, but this code should work:

    let
        Source = Table.Buffer(Record.ToTable(#shared)),
        types = Table.TransformColumnTypes(Source,{"Value", type number}),
        remove = Table.RemoveRowsWithErrors(types, {"Value"}),
        list = List.Buffer(remove[Name])
    in
        list

    Friday, January 25, 2019 3:09 PM
  • Hi Aleksei!

    Thanks for your reply, your idea regarding Table.RemoveRowsWithErrors is very interesting!

    I think that the source of the magic with the field Value is that it contains all the functions of M, including all which are used in the Query in which call for #shared takes place. The logic of the error is not clear for me, but I'm sure that that's all because #shared, from some point of view, is the (copy) of M itself.

    But not less interesting is that Table.RemoveRowsWithErrors({"Value"}) by itself removes the error completely. We even not have to do any of transformations. So operator "Table.RemoveRowsWithErrors(Source, {"Value"})" in the code:

    let
        Source = Record.ToTable(#shared),
        #"Removed Errors" = Table.RemoveRowsWithErrors(Source, {"Value"})
    in
        #"Removed Errors"

    destroy any magic and makes the table absolutely usual, and we can filter by Value and transform it in any way without the error. And all of this despite the fact that not a single row is filtered out by the filter :-).

    While this seem to be the solution for any kind of problems with #shared usage, the reasons for 1. the error and 2. for the workaround for tackle the problem are absolutely unclear. And, from my experience, the stranger the situation is for me, the more fundamental misunderstanding I have. So it would be great if somebody from PQ team would comment regarding the roots of that problem.

    Sunday, January 27, 2019 6:20 PM
  • Hi Andrey. The reason you're getting cyclic reference errors is that #shared includes the values of the queries in the current file (including the current query). So if the [Value] of the current query is accessed, this will result in a cyclic reference error (since it's trying to use the value of the current query to compute the value of the current query). As long as you can avoid pulling on the [Value] of the current query, things will work.

    Here's one way to make your original query work. Simply filter out the current query (currently assumed to be named "Query1" in the below code) before filtering by the data type of [Value].

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

    Hope that helps.
    Ehren

    Tuesday, January 29, 2019 12:10 AM
    Owner
  • Hi Ehren!

    Thanks a lot for your answer. It's very useful, as always.

    And the solution is very simple, I definitely had to think more before placing the question here.

    Wednesday, January 30, 2019 8:38 PM