Calculating the Inverse of Matrix in Power Query/Query Editor?

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?