Search code examples
powerbimpowerbi-datasource

How to use the same published PowerBI report with different data sources link?

I am a newbie with PowerBI and I have a need to use the same report created with different link sources, the sources have the same table structure.

I use Other - Web as da data source and use a link like this

http://db.other.com.br/nahar/infinity/rest/entitygrupo

where infinity is the database, and I have many others:

http://db.other.com.br/nahar/rachu/rest/entitygrupo

the data retrieved is exactly the same.

I would like to find a way to somehow keep the report intact with no need to create one just to change the source, since I have many databases. It would be perfect if I could use the same published link and pass some parameter that change the link in use.

Solution

  • Parameters and templates in Power BI serve this use case exactly.

    Assume we have the following data source for demo purpose:

    https://jsonplaceholder.typicode.com/posts/1

    https://jsonplaceholder.typicode.com/posts/2

    Where 1 and 2 are equivalent to the database names (infinity and rachu) in your data source.

    The query will be like:

    let
    Source = Json.Document(Web.Contents("https://jsonplaceholder.typicode.com/posts/1")),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

    Now we can create a new parameter for database_name:

    new parameter

    database_name

    Then we can go to Advanced Editor to modify the query and parameterize the data source:

    let
    Source = Json.Document(Web.Contents("https://jsonplaceholder.typicode.com/posts/" & database_name)),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

    advanced editor

    You'll notice there are no changes at the moment.

    parameterize

    Create a simple table to display the data (id 1):

    table 1

    And then we can export the report as a Power BI template:

    power bi template

    If you open the template file, you will be asked to input the values for parameters. Let's try 2 for database_name and click load:

    enter parameters

    You'll see that the report is loaded with data coming from database 2 (id 2), and it has identical report layout as the first one:

    report 2