Power BI Parameters for Efficient Data Source Configuration

Introduction

In a recent project involving Power BI, our focus was on establishing connections to an on-premise Business Central instance. To access the business sandbox, the customer opened the sandbox service port for us. Additionally, for production purposes, we configured a Data Gateway within the Power BI service. However, upon completion of development, we encountered an issue during the deployment of the Power BI report to the Power BI service. Specifically, we struggled with modifying the connection string. In this post, I will outline the steps we took to address and resolve this issue effectively using Power BI Parameters.

Details

When we were done with the development and tested was over, thought of changing the source from File-> Options and settings -> Data source settings

But as soon as we were changing the URL, Power BI detected it and showed the following error:

When we used the apply changes options it failed as we were not able to access the production service of our development machine just like sandbox because its port was not opened. First, we thought of publishing it without saving changes but in the Power BI service there was no option to change the data source and our report was still connected to the sandbox only.

To solve this issue, we utilized, Parameters! Power BI Parameters serve as dynamic placeholders for values that can be modified directly within the Power BI Desktop or through the Power BI Service. We used it for data source connections. We can use Transform data options and can set Parameter like the below

After that, we can use a Parameter under source for our table like below.

After this change, we can refresh our Power BI report and deploy it to Power BI Service. In the Power BI service while scheduling refresh we can update parameters and use Product URL.

Summary

In this post, we discussed how we can leverage the Power BI Parameter to make data source configurable. This is very useful when you want to connect to different data sources.

Hope it will help someone !

Keep learning and Keep Sharing !