Table1 Table2 Result
NAME SAP NAME NAME SAP NAME
Andrew Smith Asmith Bob Dylan Bdylan
Bob Dylan Bdylan
Chris Pine Cpine
Dave Spensor Dspensor


Code:
// Join
let
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    InnerJoin = Table.NestedJoin(Table1, {"NAME"}, Table2, {"NAME"}, "Table2", JoinKind.Inner),
    TSC = Table.SelectColumns(InnerJoin,{"SAP NAME"})
in
    TSC