A cyclic reference was encountered during evaluation?

[pthapa]

Hello all,

Source of data is sql server  and via stored procedure. There are two RangeStart and RangeEnd parameters that I created for incremental refresh. On the advanced editor I modified the query to enableFolding. Once that is done, applied the filter on date/time field with those two parameters. Once close and apply the steps, pbi throws the error :

Here is my code from advanced editor:

Does anyone know what did I do wrong on these steps that PBI didn't like it.

Thanks for your time.

pthapa

[pthapa]

Hello @v-jtian-msft ,
Thanks for your reponse on this topic. Appreciate taking time to gather valuable informations and sharing with the community.
This issue is still not resolved and under investigation as of now. I am still researching why or how.

1. As per your advise, I tried another sample stored procedure without having [EnableFolding=true]; still the query will not fold.

2. And, I tried the query without sub-query on the sql, it still will not fold, just for your reference.

It will not fold after I filtered the date/time field. See below

Then get this error once close/apply:

Thanks,

pthapa

[pthapa]

Hello all,

I wanted to provide some update to this topic though the issue is not exactly related to cyclic reference error.

I tired a stored procedure with Startdate and Enddate parameters that is already in the stored procedure.

Pass the RangeStart and RangeEnd parameters to the Startdate and Enddate and enable folding = true, the query folds without issues and able to apply incremental refresh to this dataset.

I followed the instructions from GuyInCUbe. Thank you Patric for sharing such wonderful tips in the video.

https://www.youtube.com/watch?v=-KXDpi_wyD4&t=196s

Hope this helps.

But I am still hunting to why a cyclic referene error comes up from my original post!!!

Thanks,

pthapa

[v-jtian-msft]

HI,@pthapa
I am glad to help you.

 I tried to reproduce the situation you are experiencing, but unfortunately I failed.

Here is my test data.

let
    Source = Sql.Database("VM1", "AdventureWorks2019"),
    dbo_data_refreshTest = Source
    {[Schema="dbo",Item="data_refreshTest"]}
    [Data],
    RunSQL = Value.NativeQuery(
        Source,
        "select * from data_refreshTest",
        null,
        [EnableFolding=true]

    ),
    #"Filtered Rows" = Table.SelectRows(RunSQL, each [refreshPoint] >= RangeStart and [refreshPoint] < RangeEnd)
in
    #"Filtered Rows"

For the problem you are experiencing after the modification: A cyclic reference was encountered during evaluation
You can investigate the problem from the following directions:
1. Your data source comes from the stored procedure of sql server, the sql server data source itself supports query folding, you can try to see if it is feasible without adding [EnableFolding=true].
2. check the stored procedure you are using, this may happen if the query in the data source used or any subquery of it tries to reference the result of the query itself.
Below is the information I found, you can use it as a reference, it contains the description of the problem “A cyclic reference was encountered during evaluation”.
URL: Value.NativeQuery - Function | Power Query How
URL: Query folding on native queries - Power Query | Microsoft Learn
URL: Understanding The "A cyclic reference was encountered during evaluation" Error In Power Query In Pow...

You can also take a look at this video:About Data Source Settings in Incremental Refresh, hope you can find inspiration from it.
https://www.bing.com/ck/a?!&&p=5ec42018fd48657eJmltdHM9MTcxNTkwNDAwMCZpZ3VpZD0wYjE0NzExMy1hZWUyLTZlY...

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.