none
Calculating the Inverse of Matrix in Power Query/Query Editor? RRS feed

  • Question

  • Hi all,

     

    I'm trying to determine if there is a way to calculate the inverse of a square matrix in Power Query?

     

    I've built on some of the work of Gil Raviv and others for table/matrix manipulations: 

    https://datachant.wordpress.com/2016/06/07/faster-matrix-mulitplication/

     

    I've been able to generalize his matrix multiplication algorithm into a function and create functions for addition and generating an identity and lambda matrix.

    - fnMatrixMult

     

     

    let fnMatrixMult = (FirstMatrix as table, SecondMatrix as table) =>
    
      let
          
          MatrixA = fnPrepareMatrix(FirstMatrix),
          MatrixB = fnPrepareMatrix(SecondMatrix),
    
          #"Merged Queries" = Table.NestedJoin(MatrixA,{"Column"},MatrixB,{"Row"},"NewColumn",JoinKind.LeftOuter),
          #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"NewColumn", "B"}}),
          #"Expanded B" = Table.ExpandTableColumn(#"Renamed Columns", "B", {"Column", "Value"}, {"B.Column", "B.Value"}),
          #"Added Custom" = Table.AddColumn(#"Expanded B", "AB", each [Value] * [B.Value]),
          #"Grouped Rows" = Table.Group(#"Added Custom", {"Row", "B.Column"}, {{"AB", each List.Sum([AB]), type number}}),
          #"Renamed Columns1" = Table.RenameColumns(#"Grouped Rows",{{"B.Column", "Column"}}),
          #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Renamed Columns1", {{"Column", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Renamed Columns1", {{"Column", type text}}, "en-US")[Column]), "Column", "AB")
      in
          #"Pivoted Column"
    
    in fnMatrixMult

     

     

    - fnPrepareMatrix (helper function)

     

     

    let fnPrepareMatrix = (Matrix as table) =>
        
        let
            
            #"Added Index" = Table.AddIndexColumn(Matrix, "Index", 1, 1),
            #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
            #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Column","",Replacer.ReplaceValue,{"Attribute", "Value"}),
            #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Column","",Replacer.ReplaceText,{"Attribute"}),
            #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Attribute", Int64.Type}}),
            #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute", "Column"}, {"Index", "Row"}})
        in
            #"Renamed Columns"
    
    in fnPrepareMatrix

     

     

     

    - fnMatrixAdd

     

    let fnMatrixAdd = (FirstMatrix as table, SecondMatrix as table) =>
    let 
    
        A = fnPrepareMatrix(FirstMatrix),
        B = fnPrepareMatrix(SecondMatrix),
    
        #"Merged Queries" = Table.NestedJoin(A,{"Row", "Column"},B,{"Row", "Column"},"B",JoinKind.Inner),
        #"Expanded test2" = Table.ExpandTableColumn(#"Merged Queries", "B", {"Value"}, {"B.Value"}),
        #"Added Custom" = Table.AddColumn(#"Expanded test2", "Sum", each [Value] + [B.Value]),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Value", "B.Value"}),
        #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Column", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Column", type text}}, "en-US")[Column]), "Column", "Sum", List.Sum)
    in
        #"Pivoted Column"
    in 
        fnMatrixAdd

     

    - fnIdentityMatrix

     

    let fnIdentityMatrix = (Dimensions as number) =>
    
    let
       res = List.Generate(()=>1, each _ <= Dimensions , each _ + 1),
        #"Converted to Table" = Table.FromList(res, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Custom"},#"Added Custom",{"Custom"},"Added Custom",JoinKind.FullOuter),
        #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Column1"}, {"Added Custom.Column1"}),
        #"Added Custom1" = Table.AddColumn(#"Expanded Added Custom", "Values", each if [Column1] = [Added Custom.Column1] then 1 else 0),
        #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom1", {{"Added Custom.Column1", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Custom1", {{"Added Custom.Column1", type text}}, "en-US")[#"Added Custom.Column1"]), "Added Custom.Column1", "Values", List.Sum),
        #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom", "Column1"})
    in
        #"Removed Columns"
    
    in fnIdentityMatrix

     

    - fnLambdaMatrix - this is like an identity matrix, but the first row is all zeros

     

    let fnLambdaMatrix = (Dimensions as number) =>
    
    let
        Dimensions = 5,
        res = List.Generate(()=>1, each _ <= Dimensions , each _ + 1),
        #"Converted to Table" = Table.FromList(res, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Custom"},#"Added Custom",{"Custom"},"Added Custom",JoinKind.FullOuter),
        #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Column1"}, {"Added Custom.Column1"}),
        #"Added Custom1" = Table.AddColumn(#"Expanded Added Custom", "Values", each if [Column1] = [Added Custom.Column1] then 1 else 0),
        #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom1", {{"Added Custom.Column1", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Custom1", {{"Added Custom.Column1", type text}}, "en-US")[#"Added Custom.Column1"]), "Added Custom.Column1", "Values", List.Sum),
        #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom", "Column1"}),
        #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",1,0,Replacer.ReplaceValue,{"1"})
    in
        #"Replaced Value"
    
    in fnLambdaMatrix



    My ultimate goal is to be able to perform a linear regression using a regularized normal equation (so matrix multiplication) in Power Query to use in a forecast that will be charted in a Power BI file.  R may be the only way to do this, but I was interested if there was a way to handle this in straight power query?

    Friday, August 24, 2018 3:19 PM

Answers