Lookup data from unrelated table

I was able to do it with Grouping and Selecting the relevant rows.

Hopefully the code is "self-documenting" but ask if you have any questions.

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Client", type text}, {"LeadAdvisor", type text}, {"Sales1", type text}, {"Sales1%", type number}, {"Sales2", type text}, {"Sales2%", type any}, {"Sales3", type text}, {"Sales3%", type any}, {"Fees paid", Currency.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Client", "Sales1%", "Sales2%", "Sales3%", "Fees paid"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> " " and [Value] <> "-")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Value"}, {
        {"Total Fees", (t)=>
            let 
                #"Lead Advisor" = Table.SelectRows(t,each [Attribute] = "LeadAdvisor"),
                #"Lead Advisor Fees" = List.Sum(#"Lead Advisor"[Fees paid]),
                #"Remove Lead Advisor Clients" = Table.SelectRows(t, each not List.Contains(#"Lead Advisor"[Client],[Client])),
                #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Remove Lead Advisor Clients", 
                    {"Client", "Fees paid", "Attribute", "Value"}, "Attribute.1", "Value.1"),

    //Match Sales to Sales%
                #"Sales Attrib" = Table.TransformColumns(#"Unpivoted Columns1",{
                    {"Attribute", each Text.Split(_,"s"){1}},
                    {"Attribute.1", each Text.SplitAny(_,"s%"){1}}}),
                Fees = Table.SelectRows(#"Sales Attrib", each [Attribute] = [Attribute.1]),
                #"Sales Fees" = List.Sum
                    (List.Generate(
                        ()=>[f=Fees[Fees paid]{0} * Fees[Value.1]{0}, idx=0],
                        each [idx] < Table.RowCount(Fees),
                        each [f=Fees[Fees paid]{[idx]+1} * Fees[Value.1]{[idx]+1}, idx=[idx]+1],
                        each [f]
    ))
                
            in 
                #"Sales Fees" + #"Lead Advisor Fees", Currency.Type}})
in
    #"Grouped Rows"