Excel BI Power Query Challenge 31 - Brian Julius Solution

gistfile1.txt

let
    Source = Table.TransformColumnTypes( Table.DuplicateColumn( FatherSonRaw, "Name", "Surname"), {"Year", Int64.Type}),
    LastName = Table.RemoveColumns( Table.SplitColumn(Source, "Surname", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Surname.1", "LastName"}), "Surname.1"),
    Group = Table.Group(LastName, {"LastName", "Country"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Name=nullable text, Country=nullable text, Year=nullable number, LastName=nullable text]}, {"MinYear", each List.Min([Year]), type nullable number}}),
    Filter = Table.RemoveColumns( Table.SelectRows(Group, each ([Count] <> 1)), {"LastName", "Country", "Count"}),
    Expand = Table.ExpandTableColumn(Filter, "All", {"Country", "LastName", "Name", "Year"}, {"Country", "LastName", "Name", "Year"}),
    Relation = Table.AddColumn(Expand, "Relation", each if [Year] = [MinYear] then "Father" else "Son"),
    AgeDiff = Table.SelectRows( Table.AddColumn(Relation, "AgeDiff", each [Year] - [MinYear]), each List.AnyTrue( {[AgeDiff] = 0, [AgeDiff] >= 16 }) ),
    ReGroup = Table.Group(AgeDiff, {"Country", "LastName"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Country=nullable text, LastName=nullable text, Name=nullable text, Year=nullable number, MinYear=nullable number, Relation=text, AgeDiff=number]}}),
    ReFilter = Table.RemoveColumns( Table.SelectRows(ReGroup, each ([Count] <> 1)), {"Country", "LastName"}),
    Index = Table.ReorderColumns( Table.AddIndexColumn(ReFilter, "Index", 1, 1, Int64.Type), {"Index", "All"}),
    ReExpand = Table.ExpandTableColumn(Index, "All", {"Country", "LastName", "Name", "Relation"}, {"Country", "LastName", "Name", "Relation"}),
    SonTable = Table.SelectRows( Table.SelectColumns( ReExpand, {"LastName", "Name", "Country", "Relation"}), each [Relation] = "Son"),
    FatherTable = Table.PrefixColumns( Table.SelectRows( Table.SelectColumns( ReExpand, {"LastName", "Name", "Country", "Relation"}), each [Relation] = "Father"), "Father"),
    JoinTable = Table.Join( SonTable, {"Country", "LastName"}, FatherTable, {"Father.Country", "Father.LastName"}),
    Clean = Table.ReorderColumns( Table.RenameColumns( Table.SelectColumns ( JoinTable, {"Name", "Father.Name", "Country"}), {{"Name", "Son"}, {"Father.Name", "Father"}}), {"Father", "Son", "Country"}),
    Sort = Table.Sort(Clean,{{"Country", Order.Ascending}, {"Son", Order.Ascending}})
in
    Sort