Power BI Best Practice: Use Parameters for Connection Information

This best practice is a simple corollary from a software engineering principle: never hard code values in code! I am singling out connection information as in Power BI all work begins by connecting to one or more data sources. You should always use parameters to refer to server addresses, database names, folder paths, and file names, for example. For a tutorial on parameters see this article from Microsoft: http://tinyurl.com/hwftfda.

There are several benefits to this:

More readable code. It’s easier to understand this M code:

Source = Sql.Database ( #”QA Server”, #”CRM Database” )

than this M code:

Source = Sql.Database ( “333.444.55.66”, “DB459” )
Changes are easier to make. If you have several queries against a database, for example, and the server address or the database name changes, then you only need to change parameter values, not each query’s code. Consider a scenario in which you begin report development against data sources in a test environment. When the time comes to test against the production environment it is more convenient to make the switch by editing parameter values.
Dependencies are explicit. By just looking at parameter names and values you can tell what a report’s data source dependencies are.
Parameters can be changed in the Power BI service. If connection information changes after publishing a report, parameters may be edited online in the dataset’s settings. No need to republish the report. Following a previous example, consider a situation in which you don’t have access to production data sources. You may publish the report with the test environment’s parameter values and then edit the connection information after publication. (For this to work the Power BI data gateway needs to have the production environment’s data sources defined.)