Lookup data from unrelated table

[Anonymous]

I have one table of employee names: 

EmployeeName

Sally

John

Joey

Barbara

and a table of clients (below), along with their LeadAdvisor, along with up to three people who could have been involved with the sales for this client. What I am tasked with is for each employee in the above table, I have to say: 

• If they are LeadAdvisor, then give me 100% of the fees paid
• If they are LeadAdvisor AND a sales person, then I still just want 100% of the fees paid (so don't double-count fees)
• If they are only a Salesperson and not also a LeadAdvisor, then I want fees * the percent of sales they are responsible for

Client	LeadAdvisor	Sales1	Sales1%	Sales2	Sales2%	Sales3	Sales3%	Fees paid
Client1	Sally	Sally	50%	Joey	50%	-	-	$5,000
Client2	Joey	-	100%	-	-	-	-	$10,000
Client3	John	Sally	10%	Barbara	10%	John	80%	$75,000
Client4	Joey	Joey	100%	-	-	-	-	$3,000
Client5	Barbara	Sally	75%	John	25%	-	-	$17,000
Client6	Sally	John	35%	Sally	50%	Barbara	15%	$8,000
Client7	Sally	Barbara	75%	Joey	25%			$45,000

So for example, Sally would total $78,250, which consists of

• $5k for Client1
• $8k for Client6
• $45k for Client7
• $7,500 for Client3 (10% of $75k)
• $12,750 for Client5 (75% of $17k)

But I can't figure out how to make this work efficiently. I suspect the answer is in how I model this, but even with unpivoting the LeadAdvisor and Sales roles columns didn't solve things for me. Any help is much appreciated. 

[Anonymous]

This is great, and it appears I was headed in the right direction - not sure I would have gotten there without your help. Thank you so much. 

I had to tweak it a little - ran into some errors with nulls, but once I figured that out, this started working great. 

I understand most of it, but am wondering if you could describe in layman's terms what's happening with the "Grouped Rows" section. Some of that syntax is brand new to me. 

[ronrsnfld]

That is where the magic happens.

Which part or parts don't you understand?

(t) represents the "sub table" from each group that the Table.Group function passes to the aggregation list. The other steps should be describable by their names.

[ronrsnfld · Accepted solution]

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"