How to define multiple data sources for a single dataset in power BI report.

[divyanshmalik]

We have 3 different environments (UAT, QA, PROD) where we have to use same powerBI report. As per the current behaviour we will have to publish 3 reports with 3 different data sources since the databases for these environments are different. Is there a way to configure a dataset in power BI which can use multiple data sources? It seems incorrect to create 3 instances of same report just to connect with different data sources.

[Greg_Deckler]

@divyanshmalik - Try something like this in your query. This uses a parameter called "Environment" that can either be "Dev" or "Prod"

let
    DevSource = Csv.Document(File.Contents("C:\temp\powerbi\test_dev.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    ProdSource = Csv.Document(File.Contents("C:\temp\powerbi\test_prod.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    
    #"Promoted Headers Dev" = Table.PromoteHeaders(DevSource),
    #"DevResult" = Table.TransformColumnTypes(#"Promoted Headers Dev",{{"Column1", type text}, {"Column2", Int64.Type}}),

    #"Promoted Headers Prod" = Table.PromoteHeaders(ProdSource),
    #"ProdResult" = Table.TransformColumnTypes(#"Promoted Headers Prod",{{"Column1", type text}, {"Column2", Int64.Type}}),

    result = 
        if Environment = "Dev"
        then #"DevResult"
        else #"ProdResult"
in
    #"result"

[Greg_Deckler]

I think that Parameters were introduced for this kind of scenario (among others). However, there was a post earlier where parameters were causing issues with enterprise gateway.

You can have multiple Sources in a query. My blog article here touches on this. What I am wondering is if you could create an if then else statement in M and select between a data source based upon a parameter versus parameterizing the server name. Going to give that a try.