How to Handle Complex Calculations with Power Apps, Flow and SQL

Power Apps can deal with basic anthemics. But unless your requirement is to build a four function calculator, you will need to incorporate few other pieces from Microsoft’s arsenal.

In the following solution, I will calculate linear regression in Power Apps with user defined axis. This will be achieved with the following combination:

Power Apps: Handle user inputs and shows calculated value
Flow: Connect Power Apps to Stored Procedure
Stored Procedure: Calculates Linear Regression

Beef up low-code with SQL

Full disclaimer, full credits to SILOTA for providing the query to calculate linear regression. I recall getting an A in my econometrics class (hold the applause please), but that with a 20+ point curve.

First step is to create a stored procedure. It will need input and output variables to fully integrate with Power Apps. Following query will:

1. Accept x and y axis from the user
2. Create temp table to hold results
3 & 4. Calculate linear regression based on the columns
5. Set result to output variable
6. Sanity check

/******************************************************************************************

	Linear Regression Coefficients
	Table: SalesLT.Product
	Available Columns: StandardCost, ListPrice, Weight
	Sample Usage:

	EXECUTE PA.CalculateLinearReg 'StandardCost', 'ListPrice', NULL, NULL;

******************************************************************************************/

CREATE PROCEDURE PA.CalculateLinearReg
(
	-- 1. Define Variables
	@x_column	NVARCHAR(50)
	,@y_column	NVARCHAR(50)
	,@Slope		FLOAT NULL OUTPUT
	,@Intercept	FLOAT NULL OUTPUT
)
AS

SET NOCOUNT ON;

DECLARE @sql		NVARCHAR(2000);

-- 2. Create Temp Table to Hold Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
	DROP TABLE ##Results;

CREATE TABLE ##Results 
(
	Slope FLOAT
	,Intercept FLOAT
);

-- 3. Structure SQL Query
SET @sql = '

	INSERT INTO ##Results
	(
		Slope
		,Intercept
	)

	SELECT	Slope , y_bar_max - x_bar_max * slope AS Intercept
	FROM (
		SELECT	SUM((x - x_bar) * (y - y_bar)) / SUM((x - x_bar) * (x - x_bar)) AS Slope
			,MAX(x_bar) AS x_bar_max
			,MAX(y_bar) AS y_bar_max    
		FROM (
			SELECT	' + @x_column + ' AS x
					,AVG(' + @x_column + ') OVER () AS x_bar
					,' + @y_column + ' AS y
					,AVG(' + @y_column + ') OVER () AS y_bar
			FROM	SalesLT.Product
		) AS s
	) AS dt
	'

-- 4. Execute Query
EXECUTE SP_EXECUTESQL @sql;

-- 5. Set Output Variables
SELECT	@Slope = Slope
		,@Intercept = Intercept
FROM	##Results;

-- 6. Preview
PRINT	'Slope: ' + CAST(@Slope AS NVARCHAR(10));
PRINT	'Intercept: ' + CAST(@Intercept AS NVARCHAR(10));

RETURN 0;

Let your data flow through the Flow

Microsoft Flow is what extends Power Apps beyond low-code / no-code development. In our Flow, we are simply acting as a glue between Power Apps and SQL stored procedure. Performance has been great (9 ms to 53 ms) and auto-logging makes debugging really easy. Here is the breakdown:

1. PowerApps (Trigger): Responds to PowerApps action (onSelect, onLoad, and etc.)
2. Initialize variable – X Column (Variable – Initialize variable): Ask in PowerApps obtains first input variable, x-axis column in our example.
3. Initialize variable – Y Column (Variable – Initialize variable): Ask in PowerApps obtains first input variable, y-axis column in our example.
4. Execute stored procedure (V2) (SQL Server): Executes [PA].[CalculateLinearReg] with user defined x and y axis columns. Output variables does not have to be defined.
5. Respond to a PowerApps or flow (PowerApps): Returns output variable from SQL to PowerApps. Important Note, If Respond to a PowerApps is not defined, PowerApps will not wait for the stored procedure to complete and could lead to an error!

Bring the Power to the users

Now to bring it all together:

I tried to provide all the details in the screenshot below, but do not get discouraged if you have difficulty juggling string, table, and record types in PowerApps. To make it clear as mud, output.output is a record within a record. First(FirstN()) is the function to call specific index of a record. This stuff still has me pulling out my hair and Power Apps data types deserves its own post (multiple, maybe a novel).

Hopefully, this provides a roadmap to incorporate SQL (Rest APIs, Azure Functions, and all the other goodness) to your applications. Feel free to shoot me your questions and comments.