List.Max returns error

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"