SumProduct in Excel's Power Query

[daedalos]

Dear Excel Gurus,
I am seeking help how to replicate Excel's formula SumProduct in Power Query.
In one table I have payroll data (1), i.e. list of employees with their wage components.
In second table (2) I have information, if the wage component belongs to specific summary or not. Here I am using 3 states: 0 = do not use, 1 = add to summary, -1 = substract from summary. Where in this example I am creating 3 sums: Gross Income, Gross Salary, Net Salary.
My final table (3) has all original data and added summaries.

Normally I have over 100 wage components and over 4500 employees, so my concern is speed of the query. I believe that the answer is behind one or two List functions, but after 4 hours I gave up for the moment and created a primitive solution: Unpivot wage components. Merge with table with rules. Multiply value from source with value from rules. Summarized per EID and merge with original source. I know that something more elegant has to exist, but I only started learning Power Query 2 weeks ago, so the solution eludes me.
If someone can give me at least a hint which way to go, it would be great. If someone would be willing to create a solution, I am more than happy to buy them a couple of coffees via PayPal.
Thanks in advance to anyone willing to help.

 

[alansidman]

Cannot manipulate data in a picture. Please reload your samples using XL2BB so that we don't have to retype all your data to test our assumptions for a solution. Without processing, it looks like you will need to Unpivot your data and then do a Group By on the eid. With sample data I can give you actual Mcode.

 

[daedalos]

Hi alansidman, this is my first try with XL2BB so fingers crossed and thanks in advance for your help.

GTN_testing_PQ_v01.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	Source of data							Rules for creating summaries					Original table						Newly created summaries
3	EID	Name	Base	Bonus	Gift	Tax		Header	Gross Income	Gross Salary	Net Salary		EID	Name	Base	Bonus	Gift	Tax	Gross Income	Gross Salary	Net Salary
4	eid01	employee 01	20000		2100	500		EID	0	0	0		eid01	employee 01	20000		2100	500	22100	20000	19500
5	eid02	employee 02	25000	2500		600		Name	0	0	0		eid02	employee 02	25000	2500		600	27500	27500	26900
6	eid03	employee 03	24000	2000	2400	400		Base	1	1	1		eid03	employee 03	24000	2000	2400	400	28400	26000	25600
7	eid04	employee 04		3000	2100	700		Bonus	1	1	1		eid04	employee 04		3000	2100	700	5100	3000	2300
8	eid05	employee 05			2800			Gift	1	0	0		eid05	employee 05			2800		2800	0	0
9	eid06	employee 06	32500	3500	2400	500		Tax	0	0	-1		eid06	employee 06	32500	3500	2400	500	38400	36000	35500
10	eid07	employee 07	20000	1500	2100	600							eid07	employee 07	20000	1500	2100	600	23600	21500	20900
Data

 

[daedalos]

This is the query I was capable to came up. Nothing fancy and I am afraid what it will do once I try it on real data. I wanted to use something more elegant like List.Accumulate, but I failed so far.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Change_Type = Table.TransformColumnTypes(Source,{{"EID", type text}, {"Name", type text}, {"Base", type number}, {"Bonus", type number}, {"Gift", type number}, {"Tax", type number}}),
    Unpivote_Columns = Table.UnpivotOtherColumns(Change_Type, {"EID", "Name"}, "Attribute", "Value"),
    Merge_Queries = Table.NestedJoin(Unpivote_Columns, {"Attribute"}, Master, {"Header"}, "Master", JoinKind.LeftOuter),
    Expand_Master = Table.ExpandTableColumn(Merge_Queries, "Master", {"Gross Income", "Gross Salary", "Net Salary"}, {"Master.Gross Income", "Master.Gross Salary", "Master.Net Salary"}),
    Add_Custom1 = Table.AddColumn(Expand_Master, "Gross Income", each [Value]*[Master.Gross Income]),
    Add_Custom2 = Table.AddColumn(Add_Custom1, "Gross Salary", each [Value]*[Master.Gross Salary]),
    Add_Custom3 = Table.AddColumn(Add_Custom2, "Net Salary", each [Value]*[Master.Net Salary]),
    Remove_Columns = Table.RemoveColumns(Add_Custom3,{"Name", "Attribute", "Value", "Master.Gross Income", "Master.Gross Salary", "Master.Net Salary"}),
    Group_Rows = Table.Group(Remove_Columns, {"EID"}, {{"Sum Gross Income", each List.Sum([Gross Income]), type number}, {"Sum Gross Salary", each List.Sum([Gross Salary]), type number}, {"Sum Net Salary", each List.Sum([Net Salary]), type number}})
in
    Group_Rows

 

[JGordon11]

If the rules table is not going to change then just add three hard-coded columns:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EID", type text}, {"Name", type text}, {"Base", Currency.Type}, {"Bonus", Currency.Type}, {"Gift", Currency.Type}, {"Tax", Currency.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Base", "Bonus", "Gift", "Tax"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Gross Income", each [Base]+[Bonus]+[Gift]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Gross Salary", each [Base]+[Bonus]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Net Salary", each [Gross Salary] - [Tax])
in
    #"Added Custom2"

 

[daedalos]

If the rules table is not going to change then just add three hard-coded columns:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EID", type text}, {"Name", type text}, {"Base", Currency.Type}, {"Bonus", Currency.Type}, {"Gift", Currency.Type}, {"Tax", Currency.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Base", "Bonus", "Gift", "Tax"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Gross Income", each [Base]+[Bonus]+[Gift]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Gross Salary", each [Base]+[Bonus]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Net Salary", each [Gross Salary] - [Tax])
in
    #"Added Custom2"

Hi JGordon11, rules table is changing constantly. Either new wage codes are added, or the rules itself change (e.g. what wasn't in gross income, now is). This is why i have rule/master table, so my colleagues can easily add new wage types or change rules (in the case I did it incorrectly in the first place :D)
When I was searching for an answer, I stumble on you reply
Matrix match across identical tables in Power Query
I tried to understand it, but it is way above my pay grade.

 

[daedalos]

My original idea was to pivot my rule table. So I have two same tables with same headers. Then do matrix multiplication between them. Each time for separate summary, but I failed so hard
I checked one dataset I have and there I have almost 300 wage types, plus 4500 employees and 4 different summaries, where even the sumproduct can crunch a while.
To all of willing souls, thanks for your help.

 

[JGordon11]

let
    Data = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Rules = Table.Buffer(Excel.CurrentWorkbook(){[Name="Rules"]}[Content]),
    ChangeType = Table.TransformColumnTypes(Data,{{"EID", type text}, {"Name", type text}, {"Base", Currency.Type}, {"Bonus", Currency.Type}, {"Gift", Currency.Type}, {"Tax", Currency.Type}}),
    ReplaceValue = Table.Buffer(Table.ReplaceValue(ChangeType,null,0,Replacer.ReplaceValue,{"Base", "Bonus", "Gift", "Tax"})),
    Result = List.Accumulate(List.Skip(Table.ColumnNames(Rules)), ReplaceValue, (s,c)=> Table.AddColumn(s,c, each 
            let  
                Rules1 = Table.SelectRows(Rules, (x)=> Record.Field(x, c) <>0),
                cols = Rules1[Header],
                mlist = Table.Column(Rules1, c),
                zip = List.Buffer(List.Zip({cols,mlist}))
            in 
                List.Sum(List.Transform(zip, (y)=> Record.Field(_, y{0})*y{1}))
    ))
in
    Result

add cols.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Data							Rules					Query Output
2	EID	Name	Base	Bonus	Gift	Tax		Header	Gross Income	Gross Salary	Net Salary		EID	Name	Base	Bonus	Gift	Tax	Gross Income	Gross Salary	Net Salary
3	eid01	employee 01	20000		2100	500		EID	0	0	0		eid01	employee 01	20000	0	2100	500	22100	20000	19500
4	eid02	employee 02	25000	2500		600		Name	0	0	0		eid02	employee 02	25000	2500	0	600	27500	27500	26900
5	eid03	employee 03	24000	2000	2400	400		Base	1	1	1		eid03	employee 03	24000	2000	2400	400	28400	26000	25600
6	eid04	employee 04		3000	2100	700		Bonus	1	1	1		eid04	employee 04	0	3000	2100	700	5100	3000	2300
7	eid05	employee 05			2800			Gift	1	0	0		eid05	employee 05	0	0	2800	0	2800	0	0
8	eid06	employee 06	32500	3500	2400	500		Tax	0	0	-1		eid06	employee 06	32500	3500	2400	500	38400	36000	35500
9	eid07	employee 07	20000	1500	2100	600							eid07	employee 07	20000	1500	2100	600	23600	21500	20900
10
Sheet1

 

[daedalos]

let
    Data = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Rules = Table.Buffer(Excel.CurrentWorkbook(){[Name="Rules"]}[Content]),
    ChangeType = Table.TransformColumnTypes(Data,{{"EID", type text}, {"Name", type text}, {"Base", Currency.Type}, {"Bonus", Currency.Type}, {"Gift", Currency.Type}, {"Tax", Currency.Type}}),
    ReplaceValue = Table.Buffer(Table.ReplaceValue(ChangeType,null,0,Replacer.ReplaceValue,{"Base", "Bonus", "Gift", "Tax"})),
    Result = List.Accumulate(List.Skip(Table.ColumnNames(Rules)), ReplaceValue, (s,c)=> Table.AddColumn(s,c, each
            let 
                Rules1 = Table.SelectRows(Rules, (x)=> Record.Field(x, c) <>0),
                cols = Rules1[Header],
                mlist = Table.Column(Rules1, c),
                zip = List.Buffer(List.Zip({cols,mlist}))
            in
                List.Sum(List.Transform(zip, (y)=> Record.Field(_, y{0})*y{1}))
    ))
in
    Result

This is unbelievable. Working like a charm. Now I just need to understand the code, so I can replicate it for similar scenarios
How can I buy you a coffee?

 

[JGordon11]

Glad it's working. No coffee needed, I enjoy an interesting PQ challenge.