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.
Microsoft has many tools that may be used for entering data for data analysis and display in Power BI, including but not limited to:
Power BI may also be used to enter and store data using:
To illustrate this technique, a simple example of an employee records table TblEmployees was created in SQL Server, with the following columns:
(Example data was added to tblEmployees for demonstration)
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:
The parameters may be edited through the Query Editor, accessed by Home – Transform data – Transform data then Manage parameters - Manage parameters.
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.
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.
To enter a new employee, the parameter values are entered and the stored procedure executed.
The parameter values may be entered either:
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.
Finally, to show the content of tblEmployees:
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/