Introduction

Microsoft Power BI is a powerful data analysis and reporting tool. However, it is not generally used for data entry. Sometimes users would like to be able to capture data from a Power BI report. This paper provides a method of entering data entry through Power BI using an SQL stored procedure with parameters.

Data Entry

Microsoft has many tools that may be used for entering data for data analysis and display in Power BI, including but not limited to:

1. Microsoft Forms
2. SharePoint Forms
3. Excel
4. Access
5. SQL Server Management Studio
6. SQL Server Reporting Services.

Power BI may also be used to enter and store data using:

1. parameters
2. a stored procedure
3. a SQL Server table.

To illustrate this technique, a simple example of an employee records table TblEmployees was created in SQL Server, with the following columns:

1. EmployeeID As NVARCHAR(MAX)
2. FirstName As NVARCHAR(MAX)
3. Surname As NVARCHAR(MAX).

(Example data was added to tblEmployees for demonstration)

Parameters

The parameter values entered by the user are passed to the stored procedure for entering into the table.

A new parameter is created by selecting Home – Transform data – Transform Data then Home –Manage Parameters – New Parameter; Figure 1. In the example, three parameters were created:

1. @EmployeeID
2. @FirstName
3. @Surname.

The parameters may be edited through the Query Editor, accessed by Home – Transform data – Transform data then Manage parameters - Manage parameters.

Stored Procedure

To use the parameters to enter user names into TblEmploye and show the results in the report card, a stored procedure spCreateEmployee was created. The stored procedure uses the INSERT INTO function, with the values being the parameters[1]. Note the data types of the table columns and parameters need to match. Further, an issue with inserting data from a PowerBI direct query is that the code runs multiple loops. Thus an IF EXISTS function was used to ensure that only one record is created, with the added befit that records may be updated for a given EmployeeID.

The SQL code for spCreateEmployee is:

USE [WYWM]

GO

ALTER PROC [dbo].[spCreateEmployee]
	(
	@EmployeeID AS nvarchar(max)
	,@FirstName AS nvarchar(max)
	,@Surname AS nvarchar(max)
	)

AS

BEGIN

IF EXISTS (SELECT * FROM [dbo].[TblEmployees] WHERE [EmployeeID] = @EmployeeID)
BEGIN
	UPDATE [dbo].[TblEmployees]
	SET 	[FirstName] = @FirstName
,[Surname] = @Surname
	WHERE [EmployeeID] = @EmployeeID;
END

ELSE
BEGIN
INSERT INTO [dbo].[TblEmployees]
(
		[EmployeeID]
		,[FirstName]
,[Surname]
		)

VALUES
(
		@EmployeeID
		,@FirstName
,@Surname
)
END
END

A tip for using Power BI with parameters is to check View – Parameters – Always allow; Figure 2.

Linking The Parameters To The Stored Procedure

To link the Power BI parameters to the stored procedure spCreateEmployee[2], tblEmployees was linked as a direct query data source in Power BI as tblEmployeeCreate.

The stored procedure spCreateEmployee is then called from tblEmployeeCreate adding the following query by selecting the Query Editor at Home – Transform Data – Transform Data, select the table query and select Advanced Editor.

let    
Source = Sql.Database("STEVEN-DESKTOP", "WYWM", 
[
    Query="
    EXECUTE spCreateEmployee
    @FirstName = '"& FirstName &"', 
    @Surname = '"& Surname &"', 
    @EmployeeID = '"& EmployeeID &"' 
    "]
    )
in
Source

A function fnCreateEmployee is created by selecting tblEmployeeCreate, right-clicking and selecting Create Function.

Entering Data

To enter a new employee, the parameter values are entered and the stored procedure executed.

The parameter values may be entered either:

1. Selecting Home – Transform Data – Edit Parameters, entering the Parameter values and selecting Okay; Figure 3.
2. In the Query Editor, selecting fnCreateEmployee, entering the Parameter values and selecting Invoke; Figure 4.

Native Mode Check

Once the parameter values are submitted, a Native Database Query prompt may be shown; Figure 5.

This request for permission can be turned off by selecting File – Options - Security. The first option in the dialogue is regarding Native Database Queries, by default the Require User Approval checkbox is ticked; Figure 6. Uncheck this box, noting that this changes the setting for all queries.

Results Table

Finally, to show the content of tblEmployees:

1. TblEmployees has linked again as a direct query data source in Power BI as tblEmployees
2. a Power BI table results card was created; Figure 7.

Conclusion

In conclusion, data entry may be conducted directly through Power BI by using an INSERT INTO SQL stored procedure with parameters.

[1] https://www.mssqltips.com/sqlservertutorial/2518/sql-server-insert-command-with-variables/

[2] https://www.c-sharpcorner.com/article/execute-sql-server-stored-procedure-with-user-parameter-in-power-bi/