cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cjgonzalez Visitor
Visitor

Change Data Source but Keep Models and Visualizations

Hello,

 

I am using DirectQuery to get data from a data warehouse that we built that we are constantly making updates to. We currently have a single data warehouse that we are connected to, made models off of, and created visualizations for in Power BI.

 

However, we have multiple data warehouses (with the same data) where we test in some and make changes and then copy it over to another (for example: a production data warehouse, a testing data warehouse, a quality data warehouse, etc.)

 

My question is, because it is the same data and tables (generally just added tables or fields are the only changes), is it possible to change the data source in Power BI but keep the same data models and visualizations that we created for the original data warehouse source that we used? Or is it not possible because it is a completely different data source?

 

Currently when I try I get an error that you cannot use more than one source for DirectQuery, I am wondering if there is a workaround for this.

 

Thanks!

CJ

1 ACCEPTED SOLUTION

Accepted Solutions
mow700 Regular Visitor
Regular Visitor

Re: Change Data Source but Keep Models and Visualizations

I like this approach for automation, thanks for suggesting the approach.  For manual updates I just use the Change Source dialog in Data Source Settings:

 

snip_20170922092717.png

9 REPLIES 9
Moderator v-qiuyu-msft
Moderator

Re: Change Data Source but Keep Models and Visualizations

Hi @cjgonzalez,

 

You can try to open a new Power BI desktop window, then get data from production data warehouse in DirectQuery mode, go to Query Editor -> Advanced Editor, copy the Power Query here. Then go to the report you want to change data source, paste this Power Query in Advanced Editor. 

 

w5.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Change Data Source but Keep Models and Visualizations

Hi CJ,

best practice for this scenario is to use a parameter for your connection-string to the database (or at least for the database name) that you use/reference in all your queries.

That way you only have to change the parameter once and all your data that uses/references this parameter will be switched to the new datasource at once.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




mow700 Regular Visitor
Regular Visitor

Re: Change Data Source but Keep Models and Visualizations

I like this approach for automation, thanks for suggesting the approach.  For manual updates I just use the Change Source dialog in Data Source Settings:

 

snip_20170922092717.png

amurias Frequent Visitor
Frequent Visitor

Re: Change Data Source but Keep Models and Visualizations

how to do it, when you have reports developed direcly in powerBI "online", and you change the server conection.

Imagine direct query using enterprise gateway to a SSAS multidimensional data base.

There is no way?

Trying to download de PBIX from the portal is neither available (option appear grayed out)

Thks!

 

 

vjnvinod Member
Member

Re: Change Data Source but Keep Models and Visualizations

@ImkeF

 

how would this work with Excel Pivot data. i am not able to find a solution where Power BI connects to an excel (Pivotted excel) and can extract the data behind that pivot.

 

any suggestion on this would be highly appreciable

Super User
Super User

Re: Change Data Source but Keep Models and Visualizations

If you want to extract the data behind a pivot-table in Excel, you would have to adress that source directly instead of the pivot. The Pivot table in Excel doesn't carry any metadata about it that could be retrieved by Power BI.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




vjnvinod Member
Member

Re: Change Data Source but Keep Models and Visualizations

@ImkeF

 

I see your point, but when i double click on the excel pivot, it generates the data behind that pivot, apparently i was wondering why Power BI is not capable of the same technique

 

in my case i have files stored in xlsm pivoted format, every time when i need to load that file into Power BI, i would need to unpivot the data first(by double clicking) and then load it to Power BI. there is an additional step which is coming in and making things looks weird.

 

Do you have any solution to avoid that double clicking and generate the data within Power BI?

Super User
Super User

Re: Change Data Source but Keep Models and Visualizations

I don't know why Microsoft has implemented it that way. There are a couple of other data as well (like formatting for example) that don't come with the standard Power Query-import.

 

Only thing you can do is to hack the Excel-file and re-assemble the xml to the table's content: https://github.com/ImkeF/Power-Query-Excel-Formats 

But that might get very cumbersome (no support on that from me here currently).

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Change Data Source but Keep Models and Visualizations