# Calculating the Inverse of Matrix in Power Query/Query Editor? • ### 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"}),
#"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

#"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```

```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"}),
#"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

- 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),
#"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),
#"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

• 