Forum Discussion
List.Max returns error
Dear PowerQuery enthusiasts,
I was trying to evaluate the current year by a List.Max on a custom column. I thought this was goanna be a small thing and I thought I have done this already multiple times without any problem. But today I fail all along… I searched the web and youtube, but nothing helped.
In the query 'vrYear' er calculate the maximum year of records by = List.Max(src [Year])
And it works fine. If I want to do the same in the query 'src' in a column ('Test_2'), it returns error. If I want to reference the 'vrYear' in a column I also get an error. If I hard code 2023 in 'vrYear' it works.
Datatypes are ok.
Test data: https://c.gmx.net/@324888734501700174/Br_lnWwMRwixsCNZM6TgRQ
Why does the List.Max not work in column 'Test_2' and why can I not reference 'vrYear' in column 'test' ? I am a bit desperate to fail on such easy task
I hope someone in the forum can guide me into the right direction to learn and never struggle here again.
Thank you so much in advance!
Best regards, Andreas
Hi there
You cannot reference vrYear from src because vrYear depends or src and so you get a circular reference that PQ cannot work out.
A circular reference is a situation in which two or more formulas are dependent on each other, creating a loop that cannot be resolved.
For example, suppose you have a budget spreadsheet where cells A1 and A2 both use a formula to calculate the total amount of money in a bank account. If cell A1 uses the formula = A2 + 500, and cell A2 uses the formula = A1 + 500, this creates a circular reference. Neither formula can be resolved because each one depends on the other.
Place the following M code in a blank query to see the steps of a possible fix for src:
let Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], #"Tiefer gestufte Header" = Table.DemoteHeaders(Quelle), #"Beibehaltene erste Zeilen" = Table.FirstN(#"Tiefer gestufte Header",1), #"Transponierte Tabelle" = Table.Transpose(#"Beibehaltene erste Zeilen"), #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Transponierte Tabelle", "Year", each if Text.Contains([Column1], "act", Comparer.OrdinalIgnoreCase) = true then Number.FromText(Text.Start([Column1],4)) else null), #"Geänderter Typ" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte",{{"Year", Int64.Type}}), maxYear_= List.Max(#"Geänderter Typ"[Year]), #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Geänderter Typ", "Test_2", each maxYear_) in #"Hinzugefügte benutzerdefinierte Spalte2"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
2 Replies
Hi there
You cannot reference vrYear from src because vrYear depends or src and so you get a circular reference that PQ cannot work out.
A circular reference is a situation in which two or more formulas are dependent on each other, creating a loop that cannot be resolved.
For example, suppose you have a budget spreadsheet where cells A1 and A2 both use a formula to calculate the total amount of money in a bank account. If cell A1 uses the formula = A2 + 500, and cell A2 uses the formula = A1 + 500, this creates a circular reference. Neither formula can be resolved because each one depends on the other.
Place the following M code in a blank query to see the steps of a possible fix for src:
let Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], #"Tiefer gestufte Header" = Table.DemoteHeaders(Quelle), #"Beibehaltene erste Zeilen" = Table.FirstN(#"Tiefer gestufte Header",1), #"Transponierte Tabelle" = Table.Transpose(#"Beibehaltene erste Zeilen"), #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Transponierte Tabelle", "Year", each if Text.Contains([Column1], "act", Comparer.OrdinalIgnoreCase) = true then Number.FromText(Text.Start([Column1],4)) else null), #"Geänderter Typ" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte",{{"Year", Int64.Type}}), maxYear_= List.Max(#"Geänderter Typ"[Year]), #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Geänderter Typ", "Test_2", each maxYear_) in #"Hinzugefügte benutzerdefinierte Spalte2"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Dear AIB,
oh dear... I stumbled over an circular reference... I could not find this root cause by the error messages. In the other data model the data sources to evaluate upon and insert into were different, that's wyh it worked.
I will put your answer into my knowledge base to prevent in future.
Thank you so much!
Best regards, Andreas
