Forum Discussion
perform a simple matrix addition between two tables using power query
for my business requirement i need to add two matrix. and get a resultant matrix. i know how to do it if the the number of column is small and manually entering the columns. but for my case the number of columns is high. so i need to dynamically iterate across columns.
(Table 1)Matrix A:
| 1 | 2 |
| 3 | 4 |
(Table 2)Matrix B:
| 5 | 6 |
| 7 | 8 |
I need the following result
Result table:
| 6 | 8 |
| 10 | 12 |
i need to achieve this result without manually using the column names.
If anyone could find the solution , it would be really helpful. Thanks.
.
.
Some notes.
after spending hours on google i was able to find a blog(https://querypower.com/2017/02/24/powerquery-matrix-multiplication/) for performing matrix multiplication. which is not what i want but is slightly relevant to this question
Hi
= Table.FromColumns(
List.Transform(
List.Zip(
{Table.ToColumns(MatrixA),
Table.ToColumns(MatrixB)}),
each List.Transform(
List.Zip({_{0},_{1}}),
List.Sum)
)
)Stéphane
3 Replies
Hi
= Table.FromColumns(
List.Transform(
List.Zip(
{Table.ToColumns(MatrixA),
Table.ToColumns(MatrixB)}),
each List.Transform(
List.Zip({_{0},_{1}}),
List.Sum)
)
)Stéphane
slorin Thanks a lot for your reply , the solution worked like magic(My brain cannot comprehend how loop/iteration works in Power query).
for people who want to use this matrix addition query here is the complete power query. that i copied from slorin 's solution.
//************
let
A = Table.FromRows(
{
{1, 2},
{3, 4}
}),
B = Table.FromRows(
{
{5, 6},
{7, 8}
}),
Addition = (MatrixA , MatrixB) => {
Table.FromColumns(
List.Transform(
List.Zip(
{Table.ToColumns(MatrixA),
Table.ToColumns(MatrixB)}
),
each List.Transform(
List.Zip({_{0},_{1}}),
List.Sum)
)
)
}
,
resultMatrix = Addition(A,B){0}
in
resultMatrix//************
Here's some power query for the two tables. that will save some time,
A = Table.FromRows(
{
{1, 2}
{3, 4}
}),B = Table.FromRows(
{
{5, 6},
{7, 8}
}),Addition = (table1 , table2) => ....Need Help here....
resultMatrix = Addition(A,B)
The below is the expected result.
resultMatrix = Table.FromRows(
{
{6, 8},
{10, 12}
})
