Forum Discussion
Lookup data from unrelated table
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.
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"
3 Replies
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"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.
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.