cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tarz
Helper III
Helper III

Cyclic reference during evaluation

Good Morning, 

I have this query but power query  said me that I have an error expression: Cyclic reference during evaluation

 

Table.AddColumn(#"Aggiunta colonna personalizzata3", "MeseGennaio", each if [DWH.GENNAIO] is null then (if Date.Month([Inizio Lavori])=1 then Merge1[Ricavo pesato]/(Date.Month([Fine Lavori]) + 1 - Date.Month([Inizio Lavori])) else null) else [DWH.GENNAIO])

 

Can someone help me?

Thank you everyone

1 ACCEPTED SOLUTION
watkinnc
Super User
Super User

Actually, I'd write this as:

 

Table.AddColumn(#"Aggiunta colonna personalizzata3", "MeseGennaio", each if [DWH.GENNAIO] <> null then [DWH.GENNAIO] else if Date.Month(Merged1[Inzio Levori]) <> 1 then null else each Merge1[Ricavo pesato]/((Date.Month([Fine Lavori]) + 1)- Date.Month([Inizio Lavori])))

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

5 REPLIES 5
watkinnc
Super User
Super User

Cyclical errors occur when a value in a calculation refers to itself, like:

NewTable = Table.Split(NewTable, 2)

 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

Actually, I'd write this as:

 

Table.AddColumn(#"Aggiunta colonna personalizzata3", "MeseGennaio", each if [DWH.GENNAIO] <> null then [DWH.GENNAIO] else if Date.Month(Merged1[Inzio Levori]) <> 1 then null else each Merge1[Ricavo pesato]/((Date.Month([Fine Lavori]) + 1)- Date.Month([Inizio Lavori])))

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

thank you very much i believe you saved my job

Now I run tests with the other months as well

watkinnc
Super User
Super User

When you break this down logically, your query says:

Add column named MeseGennaio and then populate it like this:

if [DWH.GENNAIO] = null then do the following: if the month of [Inzio Lavori] is 1 then Merge1[Ricavo pesado]/Month number of [Fine Levori] + 1 -1. //-1 here is your - Date.Month([Inzio Lavori]) value and since you already said (if that value = 1 then do the rest of the calculation) then that value will always be -1.

I would suggest adding parentheses to make sure that your calculations are what you expect. It's difficult to understand if you are trying to divide Ricavo pesado by the month number +1, or by month number +1-1, or by month number, and then adding the integers. Remember, clearly separated in, then, else or elseifs, and clearly delineated parentheses for your calculations are important in if then else. 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Hi, thanks for the answer.
The calculations are correct, in fact if I run the formula in January it works but not in February. It tells me it's cyclical. I should be running the formula for several months.
I don't understand what cyclical means

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors