List.Max returns error

[Goodkat]

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

[AlB · Accepted solution]

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.

[Goodkat]

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