Hi, I would be interested in ways to achieve a column by row multiplication in excel SUM( TRANSPOSE( {2;3;4} ) *{1,1,0})*5 = 25
I have done this through Table to rows and then zipping them together and taking the product, but would like to know if there is a simpler way especially using unpivot which I have tried but end up with more steps than the original, which is;
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
S = let alist =
Table.ToRows ( Table.RemoveColumns(Source,{"Item", "Unit"})) ,
zipl = List.Transform( alist, (A)=>
List.Zip({A, TaxT[Amount]} )),
prod = List.Transform( zipl, (A)=> List.Sum( List.Transform(A, (B)=>
List.Product(B) ) )),
rcol = List.Transform( List.Zip( {prod, Source[Unit]} ) ,
(A)=> List.Product(A) )
in Table.FromColumns(
Table.ToColumns(Source) &{rcol} ,
Table.ColumnNames(Source) &{"Result"} )
in
S
I have included this a step by step version along with a unpivot, if anyone has ideas as a way to get the result without a lot of nested lists I would be interested.
Richard.
Bookmarks