Forum Discussion
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
- Anonymous5 years ago
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
5 Replies
- AnonymousNot applicable
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
- AnonymousNot applicable
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
- AnonymousNot applicable
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
- AnonymousNot applicable
thank you very much i believe you saved my job
Now I run tests with the other months as well
- AnonymousNot applicable
Cyclical errors occur when a value in a calculation refers to itself, like:
NewTable = Table.Split(NewTable, 2)