cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
divyanshmalik
New Member

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

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.

2 REPLIES 2
Greg_Deckler
Super User IV
Super User IV

@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"

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Greg_Deckler
Super User IV
Super User IV

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.

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors