Identify FIFA father-son pairs by

— This is the second last problem in the series of FIFA world cup challenges — Identify the father son duo from the data set of FIFA World cups (some data changed). Following are the criteria 1. Both should belong to same country 2. Surname i.e. last name should be same 3. Minimum difference in years will be 16

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 31
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Identify FIFA father-son pairs by with Power Query

Power Query solution 1 for Identify FIFA father-son pairs by, proposed by Luan Rodrigues:

let
  Fonte = Data, 
  a = Table.AddColumn(Fonte, "Personalizar", each List.Last(Text.Split([Name], " "))), 
  b = Table.AddColumn(a, "Personalizar.1", each a[Name]), 
  c = Table.ExpandListColumn(
    Table.AddColumn(
      b, 
      "Personalizar.2", 
      each List.Distinct(List.FindText([Personalizar.1], [Personalizar]))
    ), 
    "Personalizar.2"
  ), 
  d = Table.NestedJoin(
    a, 
    {"Name"}, 
    c, 
    {"Personalizar.2"}, 
    "Personalizar.2 Expandido", 
    JoinKind.LeftOuter
  ), 
  e = Table.ExpandTableColumn(
    d, 
    "Personalizar.2 Expandido", 
    {"Country", "Name", "Year"}, 
    {"Country.1", "Father", "Year.1"}
  ), 
  Result = Table.Sort(
    Table.RenameColumns(
      Table.SelectRows(
        Table.AddColumn(
          e, 
          "Personalizar.3", 
          each 
            if [Name] <> [Father] and [Country] = [Country.1] and ([Year] - [Year.1]) > 15 then
              true
            else
              false
        ), 
        each ([Personalizar.3] = true)
      ), 
      {{"Name", "Son"}}
    ), 
    {{"Country", Order.Ascending}}
  )[[Father], [Son], [Country]]
in
  Result

Power Query solution 2 for Identify FIFA father-son pairs by, proposed by Bhavya Gupta:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  InsertedSurname = Table.AddColumn(
    Source, 
    "Surname", 
    each Text.AfterDelimiter([Name], " ", {0, RelativePosition.FromEnd}), 
    type text
  ), 
  FnTbl = (tbl as table) =>
    let
      C_1 = List.Min(tbl[Year]), 
      C_2 = List.Distinct(tbl[Country]), 
      C_3 = List.Select(
        List.Transform(
          List.Sort(Table.ToRows(tbl), each _{1}), 
          each 
            let
              a = (_{1} - C_1)
            in
              if a > 15 then {_{0}} else if a = 0 then {_{0}} else {}
        ), 
        each _ <> {}
      ), 
      C_4 = List.Transform(List.Skip(C_3), each List.First(C_3) & _ & C_2), 
      C_5 = Table.FromRows(C_4, {"Father", "Son", "Country"})
    in
      C_5, 
  Grouped = Table.Group(
    InsertedSurname, 
    {"Country", "Surname"}, 
    {{"FnInvoked", each FnTbl([[Name], [Year], [Country]])}}
  ), 
  RemoveEmptyTables = Table.Combine(List.Select(Grouped[FnInvoked], each not Table.IsEmpty(_))), 
  ExpectedOutput = Table.Sort(
    RemoveEmptyTables, 
    {{"Country", Order.Ascending}, {"Son", Order.Ascending}}
  )
in
  ExpectedOutput

Power Query solution 3 for Identify FIFA father-son pairs by, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

Part3:
 #"Filtered Rows2" = Table.SelectRows(#"Grouped Rows1", each [Count] >= 2),
 #"Removed Other Columns2" = Table.SelectColumns(#"Filtered Rows2",{"All"}),
 #"Expanded All" = Table.ExpandTableColumn(#"Removed Other Columns2", "All", {"Country", "Index1", "Name"}, {"Country", "Index1", "Name"}),
 #"Added Conditional Column" = Table.AddColumn(#"Expanded All", "Custom", each if [Index1] = 1 then "Father" else "Son"),
 #"Pivoted Column" = Table.Pivot(#"Added Conditional Column", List.Distinct(#"Added Conditional Column"[Custom]), "Custom", "Name"),
 #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Father"}),
 #"Filled Up" = Table.FillUp(#"Filled Down",{"Son"}),
 #"Removed Other Columns3" = Table.SelectColumns(#"Filled Up",{"Country", "Father", "Son"}),
 #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns3"),
 #"Reordered Columns" = Table.ReorderColumns(#"Removed Duplicates",{"Father", "Son", "Country"})
in
 #"Reordered Columns"

Power Query solution 4 for Identify FIFA father-son pairs by, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

Part2:
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] >= 2),
 #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"All", "MinYear"}),
 #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.AddIndexColumn([All],"Index1",1,1)),
 #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Custom", "MinYear"}),
 #"Inserted Subtraction" = Table.AddColumn(#"Expanded Custom", "Subtraction", each [Year] - [MinYear], type number),
 #"Filtered Rows1" = Table.SelectRows(#"Inserted Subtraction", each [Subtraction] = 0 or [Subtraction] >= 16),

Power Query solution 5 for Identify FIFA father-son pairs by, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Country", type text}, {"Year", Int64.Type}}),
 #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Name.1", "Name.2"}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
 #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Name.1", "First Name"}, {"Name.2", "Last Name"}}),
 #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Last Name", Order.Ascending}, {"Year", Order.Ascending}}),
 #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
 #"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Index", "Index."}}),
 #"Grouped Rows" = Table.Group(#"Renamed Columns1", {"Last Name", "Country"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [First Name=nullable text, Last Name=nullable text, Country=nullable text, Year=nullable number, #"Index."=number]}, {"MinYear", each List.Min([Year]), type nullable number}}),

Power Query solution 6 for Identify FIFA father-son pairs by, proposed by Matthias Friedmann:

let
  Source = Excel.CurrentWorkbook(){[Name = "WCfatherSon"]}[Content], 
  Surname = Table.AddColumn(Source, "Surname", each Text.AfterDelimiter([Name], " ")), 
  One = Table.AddColumn(Surname, "Custom", each 1), 
  Merged = Table.NestedJoin(One, {"Custom"}, One, {"Custom"}, "Added Custom", JoinKind.LeftOuter), 
  Expanded = Table.ExpandTableColumn(
    Merged, 
    "Added Custom", 
    {"Name", "Country", "Year", "Surname"}, 
    {"Son?", "Country.1", "Year.1", "Surname.1"}
  ), 
  Filtered = Table.SelectRows(
    Expanded, 
    each [Country] = [Country.1] and [Surname] = [Surname.1] and [Year.1] - [Year] > 15
  )[[Country], [Name], [#"Son?"]]
in
  Filtered

Power Query solution 7 for Identify FIFA father-son pairs by, proposed by Matthias Friedmann:

let
  Source = Excel.CurrentWorkbook(){[Name = "WCfatherSon"]}[Content], 
  Surname = Table.AddColumn(Source, "Surname", each Text.AfterDelimiter([Name], " ")), 
  #"Grouped Rows" = Table.Group(
    Surname, 
    {"Country", "Surname"}, 
    {
      {
        "All", 
        each 
          let
            table = Table.AddColumn([[Country], [Name], [Year]], "Custom", each 1), 
            merged = Table.NestedJoin(
              table, 
              {"Custom"}, 
              table, 
              {"Custom"}, 
              "joint", 
              JoinKind.LeftOuter
            ), 
            expanded = Table.ExpandTableColumn(
              merged, 
              "joint", 
              {"Name", "Year"}, 
              {"Son?", "Year.1"}
            ), 
            filtered = Table.SelectRows(
              expanded, 
              each ([Name] <> [#"Son?"] and [Year.1] - [Year] > 15)
            )[[Country], [Name], [#"Son?"]]
          in
            filtered, 
        type table
      }
    }
  ), 
  Combined = Table.Combine(#"Grouped Rows"[All])
in
  Combined

Power Query solution 8 for Identify FIFA father-son pairs by, proposed by Victor Wang:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ToRecs = Table.ToRecords(Source), 
  GetPairs = List.Select(
    List.Transform(
      ToRecs, 
      each Table.FromRecords(
        {_}
          & List.Select(
            ToRecs, 
            (a) =>
              [Country]
                = a[Country] and Number.Abs([Year] - a[Year])
                >= 16
                  and Text.AfterDelimiter([Name], " ")
                  = Text.AfterDelimiter(a[Name], " ") and a
                  <> _
          )
      )
    ), 
    each Table.RowCount(_) > 1
  ), 
  Finish = Table.FromRecords(
    List.Distinct(
      List.Sort(
        List.Transform(
          GetPairs, 
          each [
            Father  = Table.Min(_, "Year")[Name], 
            Son     = Table.Max(_, "Year")[Name], 
            Country = _{0}[Country]
          ]
        ), 
        each [Country]
      )
    )
  )
in
  Finish

Power Query solution 9 for Identify FIFA father-son pairs by, proposed by Venkata Rajesh:

let
  Source = Data, 
  Surname = Table.AddColumn(Source, "Surname", each List.Last(Text.Split([Name], " ")), Text.Type), 
  Sorted = Table.Sort(Surname, {{"Country", Order.Ascending}, {"Year", Order.Ascending}}), 
  Grouped = Table.Group(
    Sorted, 
    {"Country", "Surname"}, 
    {
      {"All", each _, type table [Name = nullable text, Year = nullable Int64.Type]}, 
      {"FatherYear", each List.Min([Year]), type nullable Int64.Type}, 
      {"Father", each List.First([Name]), type nullable text}
    }
  ), 
  Expanded = Table.ExpandTableColumn(Grouped, "All", {"Name", "Year"}, {"Son", "Year"}), 
  Filtered = Table.SelectRows(Expanded, each [Year] - [FatherYear] >= 16)[
    [Father], 
    [Son], 
    [Country]
  ]
in
  Filtered

Power Query solution 10 for Identify FIFA father-son pairs by, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Surname = Table.AddColumn(
    Source, 
    "Surname", 
    each Text.AfterDelimiter([Name], " ", {0, RelativePosition.FromEnd}), 
    type text
  ), 
  #"Grouped Rows" = Table.Group(
    Surname, 
    {"Country", "Surname"}, 
    {
      {
        "Detail", 
        (t) =>
          [
            #"Added Custom" = Table.AddColumn(t, "Custom", each t), 
            #"Expanded Custom" = Table.ExpandTableColumn(
              #"Added Custom", 
              "Custom", 
              {"Name", "Year"}, 
              {"Name.1", "Year.1"}
            ), 
            #"Inserted Subtraction" = Table.AddColumn(
              #"Expanded Custom", 
              "Subtraction", 
              each [Year] - [Year.1], 
              type number
            ), 
            #"Filtered Rows" = Table.SelectRows(#"Inserted Subtraction", each [Subtraction] <= - 16), 
            #"Removed Other Columns" = Table.SelectColumns(
              #"Filtered Rows", 
              {"Name", "Name.1", "Country"}
            )
          ][#"Removed Other Columns"]
      }
    }
  ), 
  Custom1 = Table.Combine(#"Grouped Rows"[Detail])
in
  Custom1

Solving the challenge of Identify FIFA father-son pairs by with Excel

Excel solution 1 for Identify FIFA father-son pairs by, proposed by محمد حلمي:

=LET(r,
    REDUCE({0,
    0},
    C2:C37,
    LAMBDA(a,
    c,
    LET(
b,
    @+c:B37,
    x,
    @+c:A37,
    y,
    B2:B37,
    w,
    A2:A37,
    
i,
    (y=b)*(C2:C37-c>15)*
(TEXTAFTER(
    w,
    " ",
    -1
)=TEXTAFTER(
    x,
    " ",
    -1
)),
    
VSTACK(
    a,
    IFNA(
        HSTACK(
            x,
            FILTER(
                w,
                i,
                0
            ),
            FILTER(
                y,
                i,
                0
            )
        ),
        x
    )
)))),
    
SORT(
    FILTER(
        r,
        INDEX(
            r,
            ,
            2
        )>""
    ),
    3
))

Excel solution 2 for Identify FIFA father-son pairs by, proposed by محمد حلمي:

=LET(
ss,
    SORT(
        A2:C37,
        3
    ),
    
b,
    INDEX(
        ss,
        ,
        2
    ),
    
vv,
    REDUCE(0,
    UNIQUE(
        b
    ),
    LAMBDA(a,
    d,
    VSTACK(a,
     LET(
v,
    FILTER(
        ss,
        b=d
    ),
    
o,
    TEXTAFTER(
        INDEX(
            v,
            ,
            1
        ),
        " ",
        -1
    ),
    
REDUCE(0,
    UNIQUE(
        o
    ),
    LAMBDA(qq,
    ww,
    VSTACK(qq,
    LET(
ee,
    FILTER(
        v,
        o=ww
    ),
    
rr,
    TAKE(
        ee,
        ,
        1
    ),
    
bb,
    TAKE(
        rr,
        1
    ),
    
dd,
    INDEX(
        ee,
        ,
        3
    ),
    
IF((DROP(
    dd,
    1
)-TAKE(
    dd,
    1
))>15,
    
IFNA(
    HSTACK(
        
        IFNA(
            HSTACK(
                bb,
                DROP(
        rr,
        1
    )
            ),
            bb
        ),
        d
    ),
    d
),
    NA()))))))))),
    
VSTACK(
    HSTACK(
        "Father",
        "Son",
        "Country"
    ),
    
    SORT(
        FILTER(
            vv,
            1-ISNA(
                INDEX(
                    vv,
                    ,
                    3
                )
            )
        ),
        3
    )
))

Excel solution 3 for Identify FIFA father-son pairs by, proposed by محمد حلمي:

=LET(
a,
    A2:A37,
    b,
    B2:B37,
    c,
    C2:C37,
    d,
    b&TEXTAFTER(
        a,
        " "
    ),
    
y,
    (c-TOROW(
        c
    ))>15,
    s,
    d=TOROW(
        d
    ),
    
f,
    LAMBDA(
        e,
        TOCOL(
            IF(
                y*s,
                e,
                NA()
            ),
            3
        )
    ),
    
SORT(
    HSTACK(
        f(
            TOROW(
                a
            )
        ),
        f(
                a
            ),
        f(
            b
        )
    ),
    3
))

Excel solution 4 for Identify FIFA father-son pairs by, proposed by 🇰🇷 Taeyong Shin:

=LET(n,
    A2:A37,
    c,
    B2:B37,
    y,
    C2:C37,
    t,
    TEXTAFTER(
        n,
        " "
    )&c,
    SORT(TEXTSPLIT(TEXTJOIN(1,
    ,
    REPT(TOROW(
        n
    )&0&n&0&c,
    (t=TOROW(
        t
    ))*((y-TOROW(
        y
    ))>=16))),
    0,
    1),
    3))

Excel solution 5 for Identify FIFA father-son pairs by, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _d,
     A2:C37,
    
 _ln,
     TEXTAFTER(
         INDEX(
             _d,
              ,
              1
         ),
          " ",
          -1
     ),
    
 _rw,
     SEQUENCE(
         ROWS(
             _d
         )
     ),
    
 _calc,
     REDUCE(
 "",
    
 _rw,
    
 LAMBDA(a,
     b,
    
 LET(
 f,
     FILTER(
         _d,
          _ln = INDEX(
              _ln,
               b
          )
     ),
    
 r,
     FILTER(
 f,
    
 (INDEX(
     f,
      ,
      2
 ) = INDEX(
     _d,
      b,
      2
 )) *
 ((INDEX(
     f,
      ,
      3
 ) - INDEX(
     _d,
      b,
      3
 )) > 15)
 ),
    
 IFERROR(
     
      VSTACK(
          
           a,
          
           IFNA(
               HSTACK(
                   INDEX(
                       _d,
                        b,
                        1
                   ),
                    r
               ),
                INDEX(
                       _d,
                        b,
                        1
                   )
           )
           
      ),
     
      a
      
& )
 )
 )
 ),
    
 SORT(
     DROP(
         _calc,
          1,
          -1
     ),
      3
 )
)

Solving the challenge of Identify FIFA father-son pairs by with SQL

SQL solution 1 for Identify FIFA father-son pairs by, proposed by Zoran Milokanović:

SELECT
 DF.NAME AS FATHER
, DS.NAME AS SON
, DF.COUNTRY
FROM DATA DF
JOIN DATA DS ON SUBSTR(DF.NAME, INSTR(DF.NAME, ' ') + 1) = SUBSTR(DS.NAME, INSTR(DS.NAME, ' ') + 1)
 AND DF.COUNTRY = DS.COUNTRY
 AND DS.YEAR - DF.YEAR >= 16
ORDER BY
 3, 1, 2

Solving the challenge of Identify FIFA father-son pairs by with Power Query

Solving the challenge of Identify FIFA father-son pairs by with Excel

Solving the challenge of Identify FIFA father-son pairs by with SQL

Leave a Reply Cancel reply