Take maximum value from another table with duplicate values

Anonymous 

Simple answer:

1. You didn't specify that

2. I didn't look in detail

Simplified version:

New column V2 = 
VAR auxT_ =
    CALCULATETABLE (
        'Reel defect',
        TREATAS ( CALCULATETABLE ( DISTINCT ( 'Reel status'[Key] ) ), 'Reel status'[Key] )
    )
VAR maxLenT_ = TOPN ( 1, auxT_, [Length], DESC )
RETURN
    MAXX ( maxLenT_, [Fault code] )

Please mark the question solved 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.

Cheers 

Anonymous 

If you need it in PQ (which you didn't specify at the beginning  either), place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdFBCoUwDIThu3StYDOJ6FnE+1/jKbwWZ5KFFORb/J1eV9v67m1p/f/ZuW/P8f5d+9rbvRCxREwJEoEST8SVRCLxJTZz4xzEONdm7peYEiQCJZ6IK4lEKBfFuuBcFOuCc1GsC85FsS44F8W64NzxCMcxhXPtuDcJUwEVUOEqXEWoeErvHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, #"Runup Sequence" = _t, Lane = _t, #"Reel Lenght" = _t, Key = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"Runup Sequence", Int64.Type}, {"Lane", Int64.Type}, {"Reel Lenght", Int64.Type}, {"Key", type text}}),

    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Key"}, #"Reel defect", {"Key"}, "Reel defect", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Fault code", (input)=> try let max_ = List.Max(input[Reel defect][Length]), pos_ = List.PositionOf(input[Reel defect][Length], max_, Occurrence.First), res_ = input[Reel defect][Fault code]{pos_} in res_ otherwise null, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Reel defect"})
in
    #"Removed Columns"

Please mark the question solved 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.

Cheers 

Anonymous 

See it all at work in the attached file.

Please mark the question solved 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.

Cheers