Hi I am trying to replicate in power query what in excel could be ;
=MMULT(Table32[@[One]:[Three]],Table4[Amount])*[@Units]
I have managed this but it took a lot of nesting list transformation, I have include a version where I named all the steps individually for clarity
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
rechead = ( tax [tax]),
startlist = List.Transform( Table.ToRows( Source ),
(x)=> List.Skip( List.RemoveLastN( x ,1) ,1)
),
ziplist = List.Transform( startlist, (x)=> List.Zip ( { x, tax [Amount] } ) ),
product = List.Transform( ziplist, (x)=> List.Transform( x , (y)=> List.Product(y) )),
sum = List.Transform( product, (x)=> List.Sum( x ) ),
result = List.Transform( List.Zip( { sum, Source [Units] } ), (x)=> List.Product( x ) ),
Custom3 = result
in
Custom3
if anyone has any ideas on simplifying this I would like to know, I have thought of using records, but have not tried yet.
Richard
Bookmarks