Forum Discussion
A cyclic reference was encountered during evaluation?
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 Power BI Or Excel (crossjoin.co.uk)
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=5ec42018fd48657eJmltdHM9MTcxNTkwNDAwMCZpZ3VpZD0wYjE0NzExMy1hZWUyLTZlYzEtMTQwZC02MmUxYWZkYTZmMTgmaW5zaWQ9NTI2NQ&ptn=3&ver=2&hsh=3&fclid=0b147113-aee2-6ec1-140d-62e1afda6f18&psq=does+power+bi+support+stored+procedure+when+using+incremental+refresh&u=a1aHR0cHM6Ly93d3cueW91dHViZS5jb20vd2F0Y2g_dj0tS1hEcGlfd3lENA&ntb=1
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.