Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cjgonzalez
Regular 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

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

View solution in original post

9 REPLIES 9
amurias
Frequent Visitor

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!

 

 

ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

vjnvinod
Impactful Individual
Impactful Individual

@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

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

vjnvinod
Impactful Individual
Impactful Individual

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

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).

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

I've written about this method here: https://www.thebiccountant.com/2017/08/18/how-to-import-from-excel-with-cell-coordinates-in-power-qu...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

v-qiuyu-msft
Community Support
Community Support

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors