PowerQuery - Dynamically Pass Database Name as Parameter to fetch the Records from Different Datasets using Managed Parameters and Functions

Overview

In this article, we will talk about how we can get the database name using Stored Procedure based on the Customer ID and pass that Database Name to another stored procedure as a Parameter to load the records from that particular dataset using Functions.

So, now let’s get started!

Real-Life Scenario

I have the following dataset mapping table in the Database named “Mapping”.

In the above database, I have a stored procedure which will find the name of the database based on the Customer ID.

USE [Mapping]
GO
/****** Object: StoredProcedure [dbo].[GetCustomerInformation] Script Date: 11/4/2019 6:53:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create PROCEDURE [dbo].[GetCustomerInformation]
-- Add the parameters for the stored procedure here
@CID nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT [Database] from [Mapping].[dbo].[MappingID] where [CID] = @CID
END

Below is the output of the stored procedure.

If we pass “1” as a CustomerID, it will return the database name as “A”. Similarly, for “2” it will return “B”.

Below is the schema of the database table “TSICustomers” in database A and database B with a different value.

Download Code

The code can be downloaded from the GitHub.

Now, let’s get started!

Step 1: Create a Dynamic Parameter for Customer ID to get Database Name using Stored Procedure

1. Create SQL Server Connection using Get Data.

2. Add the Server name, database name and Execute Stored Procedure in a Static Manner

Below is the code to execute the Stored Procedure

EXEC GetCustomerInformation @CID = '1'

3. Change “Query1” name as “Database”.

4. Click on Close and Apply and reopen Query Editor.

5. From “Manage Parameters”, select “New Parameter”.

6. Add the following information.

Name = Name of the Parameter
Type = Datatype of the parameter
Suggested Values = Select “Any Values”
Current Value = Pass any Static Value

7. The manage parameter has been created successfully.

8. Select “Database” from the Queries section and click on the Advanced Editor.

9. Below is the default code.

10. Change the above code with the following line of the code.

let
Source = Sql.Database("DHRUVIN\DEV", "Mapping", [Query="EXEC GetCustomerInformation @CID = '"& Text.From(CustomerID)&"'"])
in
Source

11. Now, test the dynamic value.

12. Go to parameters and pass the value as 1.

13. The database will show value “A”.

14. If we pass 2 it will return database B.

15. Click on Close and Apply.

Step 2: Create a Function to Get Database Name

1. Right-click on “Database” and click on Create Function

2. Add the name of the function as “GetDatabaseName”. Click on OK

3. The function has been created successfully.

4. Click on Close and Apply.

Step 3: Pass Database Name as a Parameter

Now, let’s pass the name of the database as a parameter in the connection.

1. Add the static SQL connection to load the data from database “A”.

Select * from TSICUSTOMERS

2. Click on OK button.

3. Click on the Added table and click on Advanced Editor.

4. Below is the static code to fetch the record from database “A”.

5. Now, change the code of the database name with the following line of code.

let
Source = Sql.Database("DHRUVIN\DEV", Table.FirstValue(GetDatabaseName(CustomerID)), [Query="Select * from TSICUSTOMERS"])
in
Source

Here, GetDatabseName = function which we have created in Step 2.

CustomerID = Parameter which we have created in Step 1.

6. Click on Close and Apply.

Testing

1. Pass Customer Id as “1”.

2. Dynamic database name “A” passed and load records from “A” database.

3. Pass Customer Id as “2”.

4. Dynamic database name “B” passed and load records from “B” database.

Create a Power BI Template for End-User

1. Go to File Menu, From Export, click on Power BI template.

2. Enter the template description.

3. Save the template file.

4. Open the template file as an end user.

5. Enter Customer ID. Let’s add 1.

6. Click on Ignore privacy level.

7. Records from database “A” has been loaded successfully.

Conclusion

This is how we can pass the database name dynamically to fetch the records from the database.

Isn’t it amazing?

Stay connected with me for more amazing articles.