Post
Ask Question

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.
 
 
 
  1. USE [Mapping]  
  2. GO  
  3. /****** Object: StoredProcedure [dbo].[GetCustomerInformation] Script Date: 11/4/2019 6:53:10 AM ******/    
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. -- =============================================  
  9. -- Author: <Author,,Name>  
  10. -- Create date: <Create Date,,>  
  11. -- Description: <Description,,>  
  12. -- =============================================    
  13. Create PROCEDURE [dbo].[GetCustomerInformation]  
  14. -- Add the parameters for the stored procedure here  
  15. @CID nvarchar(50)  
  16. AS  
  17. BEGIN  
  18. -- SET NOCOUNT ON added to prevent extra result sets from  
  19. -- interfering with SELECT statements.  
  20. SET NOCOUNT ON;  
  21. -- Insert statements for procedure here  
  22. SELECT [Database] from [Mapping].[dbo].[MappingID] where [CID] = @CID  
  23. 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
  1. 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.
  1. let  
  2. Source = Sql.Database("DHRUVIN\DEV", "Mapping", [Query="EXEC GetCustomerInformation @CID = '"& Text.From(CustomerID)&"'"])  
  3. in  
  4. 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”.
  1. 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.
  1. let  
  2.   
  3. Source = Sql.Database("DHRUVIN\DEV", Table.FirstValue(GetDatabaseName(CustomerID)), [Query="Select * from TSICUSTOMERS"])  
  4.   
  5. in  
  6.   
  7. 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.


Similar Articles