Matrix match across identical tables in Power Query

[rkaczano]

Hi I want to know how to do matrix math in Power Query. I have a query (Query1) that produced a table (Connection only) and It is time series data in Column 1 with a large number of columns representing data specific to each column header. The column headers for column 2 onward all have unique headers. I have a second query (Query2) that produced similar table (Connection only) with the exact dimensions as the table is Query1. These two tables will always have the same dimensions and same date time data in column 1, and same column headers.

I want to now create a third query (Query3) to do matrix addition/subtraction/multiplication/division across the data columns in both tables in Query1 and Query2. Such that the resulting table in Query3 has the same dimensions with its table data representing the results of the matrix math. I need to preserve the column 1 date time data for that third table so the matrix math is occult form column 2 onward.

Can anyone provide with the M Code to construct that matrix math.

Thanks in advance!

 

[smozgur]

This might not be the best approach with so many rows and columns, I am not sure, but here is mine. Curious to see other approaches.
(By the way, I think this is much easier with DAX formulas in Power BI).
Assumptions: Same date column names, same dimensions.

let
    // 1- Change the source paths below or directly assign connection only query names
    // Sample: Source1 = Query1 
    Source1 = Excel.Workbook(File.Contents("C:\Users\smozgur\Desktop\matrix\matrix1.xlsx"), null, true),
    Source2 = Excel.Workbook(File.Contents("C:\Users\smozgur\Desktop\matrix\matrix2.xlsx"), null, true),

    Source1_Sheet = Source1{[Item="Sheet1",Kind="Sheet"]}[Data],
    PromotedHeaders1 = Table.PromoteHeaders(Source1_Sheet, [PromoteAllScalars=true]),
    Matrix1 = Table.TransformColumnTypes(PromotedHeaders1,  
        List.Transform(
            List.RemoveFirstN(Table.ColumnNames(PromotedHeaders1), 1), each {_, type number}
        )
    ),
    ColumnNames = Table.ColumnNames(PromotedHeaders1),
    DataColumnNames = List.RemoveFirstN(ColumnNames, 1),
    FirstColumnName = List.First(ColumnNames),

    Source2_Sheet = Source2{[Item="Sheet1",Kind="Sheet"]}[Data],
    PromotedHeaders2 = Table.PromoteHeaders(Source2_Sheet, [PromoteAllScalars=true]),
    Matrix2 = Table.TransformColumnTypes(PromotedHeaders2,  
        List.Transform(
            List.RemoveFirstN(Table.ColumnNames(PromotedHeaders2), 1), each {_, type number}
        )),

    Join = Table.NestedJoin(Matrix1, {FirstColumnName}, Matrix2, {FirstColumnName}, "Matrix2", JoinKind.Inner),
    Matrix3 = Table.ExpandTableColumn(Join, "Matrix2", DataColumnNames, List.Transform(DataColumnNames, each "Matrix2." & _)),

    BuffTable = Table.Buffer(Matrix3),

    MatrixCalc = List.Generate(
        () => [i = 0],
        each [i] < Table.RowCount(BuffTable),
        each [i = [i] + 1],
        each Table.Transpose(Table.FromList(
            List.Combine({{Table.Column(Matrix3, FirstColumnName){[i]}},
            List.Generate(
            () => [j = 0, i = [i]],
            each [j] < List.Count(DataColumnNames),
            each [j = [j] + 1, i = [i]],
            each let 
                    M1 = Table.Column(BuffTable, DataColumnNames{[j]}){[i]},
                    M2 = Table.Column(BuffTable, "Matrix2." & DataColumnNames{[j]}){[i]},
                    // 2- Change the operator for addition/subtraction/multiplication/division accordingly
                    // It is currently addition
                    Result = M1 + M2
                in
                    Result
        )}), Splitter.SplitByNothing(), null, null, ExtraValues.Error), ColumnNames)
    ),
    ConvertToTable = Table.FromList(MatrixCalc, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Result = Table.ExpandTableColumn(ConvertToTable, "Column1", ColumnNames)
in
    Result

 

[JGordon11]

a custom function approach

(tbl1 as table, tbl2 as table, optional operation as nullable text, optional skipcolumns as nullable number) as table =>
let 
    op = if operation = null then "add" else operation,
    sk = if skipcolumns = null then 1 else skipcolumns,
    ttc1 = Table.ToColumns(tbl1),
    lst1 = List.Skip(ttc1, sk),
    lst2 = List.Skip(Table.ToColumns(tbl2), sk),
    lst3 = List.Zip({lst1,lst2}),
    lst4 = List.Transform(lst3, each List.Zip(_)),
    tbl3 = Table.FromColumns(lst4),
    pos = List.PositionOf({"add", "subtract", "multiply", "divide"}, op),
    operations = {  (x)=> x{0} + x{1},   (x)=> x{0} - x{1},   (x)=> x{0} * x{1},   (x)=> x{0} / x{1}  },
    fn = if pos>-1 then operations{pos} else (x)=> "Error",
    ColumnOps = List.Zip({Table.ColumnNames(tbl3), List.Repeat({fn},Table.ColumnCount(tbl3))}),
    tbl4 = Table.TransformColumns(tbl3, ColumnOps),
    Result = Table.FromColumns(List.FirstN(ttc1, sk) & Table.ToColumns(tbl4), Table.ColumnNames(tbl1))
in
    Result

 

[smozgur]

List.Zip! Right!

Nice approach!