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?
