cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
otravers
Continued Contributor
Continued Contributor

How to migrate an existing PBIX from Import to Service Live Connection?

We're starting to scale PBI use with one of my clients, which raises the usual lifecycle management issues. Within the production workspace, I'd like to use my existing dataset as a data source so that we can source several reports from it, e.g. regular PBI/SP reports, email reports built programmatically, etc, without redoing all the visualization work [edit: I added that last part for clarity].

 

I'm talking about what's described here:

https://docs.microsoft.com/en-us/power-bi/desktop-report-lifecycle-datasets

 

I'd like to jumpstart these new PBIX files from the one I already have. However I cannot see how to preserve the visualization work I've done in my existing PBIX (Import mode), given that:

 

1. You can't add a Live Connection to a PBIX already connected to other sources (i.e. Import)

 

2. Saving the original file as a PBIT template won't work as the template includes the existing data connections, so we're back to the situation in step 1. 

 

3. You can't copy and paste charts from one PBIX file to another. 

 

I've googled and checked books on this but didn't find a solution. Is there a migration path that I'm missing, maybe by editing the PBIX zip internals?

1 ACCEPTED SOLUTION
otravers
Continued Contributor
Continued Contributor

Here's the solution someone gave me on the PBI PUG. You might need a zip editor that integrates well with your file explorer in Windows (e.g. Total Commander, Directory Opus + Bandizip). Unsupported but worked brilliantly for me:

 

1. Create a new PBIX file with the connection to PBI Service. (No visuals or nothing to add) let's save as "LiveQuery.pbix"

2. Let's assume your file with visuals, import model is "Original.pbix".

3. Rename both the files to .zip (LiveQuery.zip and Original.zip)

4. Open both the zip files in "total commander"

5. Delete "DataModel" from "Original.zip"


6. Move "DataMashup" from "LiveQuery.zip" to "Original.zip" and click on "override" on the pop-up window.

7. Rename "Original.zip" to "Original.pbix" and open the "Original.pbix" in Power-BI Desktop.

8. There will be no connections, so click on get Data and choose "Power BI Datasets"

9. Your visuals should automatically get updated.

 

There are other ways to do it, as detailed in this blog post:

https://xxlbi.com/blog/switching-from-imported-data-to-directquery-or-live-connection-in-power-bi/

View solution in original post

7 REPLIES 7
otravers
Continued Contributor
Continued Contributor

There are a couple of other options these days:

1. Load a copy of the Import PBIX, delete all the queries in Power Query then switch to Live Connection. Don't worry that all the visualizations will be broken after deleting the queries.

2. Use this third-party tool (I didn't test it but it should work): https://powerbi.tips/2020/08/hot-swap-report-connections-external-tools/

Conarl_on_BI
Helper III
Helper III

@otravers,

This is the approach I would suggest. responses in RED.

 "I'd like to use my existing dataset as a data source so that we can source several reports from it"----This can be done.
Here is how: Open Power BI desktop--->File-->Open then nagivate to the location where your existing PowerBI pbix exist double click on it. This would invoke a new session with that dataset separate from the visualization that exists already. 

Once done...create your new reports against this dataset then publish the new reports to the Power BI services.

 

By doing the above it also solve this question "I'd like to jumpstart these new PBIX files from the one I already have."

 

2. Saving the original file as a PBIT template won't work as the template includes the existing data connections, so we're back to the situation in step 1. ---No need to do this if the above option works for you. 

 

Hope this helps. 

 

Thanks,

Conarl_On_BI

otravers
Continued Contributor
Continued Contributor

@Conarl_on_BI I must be confused by the steps you're describing because I don't see how they help with my requirement. If I open my local PBIX file twice, I just have it loaded twice in Power BI. How does that help me change the data source from the original data sources (i.e. Import) to the Power BI service while keeping my visualizations?

 

Another way to look at what I'd like to accomplish: I'd like to substitute the underlying data model in my PBIX while preserving the visualizations already set up in my PBIX. After all, it's supposed to be the same data model, but loaded in the Power BI service.

 

Recreating all my charts with their exact settings and layout would be hours of work if I have to start from a blank report connected to the dataset in the PBI service.

otravers
Continued Contributor
Continued Contributor

Here's the solution someone gave me on the PBI PUG. You might need a zip editor that integrates well with your file explorer in Windows (e.g. Total Commander, Directory Opus + Bandizip). Unsupported but worked brilliantly for me:

 

1. Create a new PBIX file with the connection to PBI Service. (No visuals or nothing to add) let's save as "LiveQuery.pbix"

2. Let's assume your file with visuals, import model is "Original.pbix".

3. Rename both the files to .zip (LiveQuery.zip and Original.zip)

4. Open both the zip files in "total commander"

5. Delete "DataModel" from "Original.zip"


6. Move "DataMashup" from "LiveQuery.zip" to "Original.zip" and click on "override" on the pop-up window.

7. Rename "Original.zip" to "Original.pbix" and open the "Original.pbix" in Power-BI Desktop.

8. There will be no connections, so click on get Data and choose "Power BI Datasets"

9. Your visuals should automatically get updated.

 

There are other ways to do it, as detailed in this blog post:

https://xxlbi.com/blog/switching-from-imported-data-to-directquery-or-live-connection-in-power-bi/

View solution in original post

sokg
Solution Supplier
Solution Supplier

OMG, this is great.

And working!!! (tried it myself)

Greg_Deckler
Super User
Super User

To the best of my knowledge, there currently is no way to do what you are looking to do. Currently.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Since this was 2018, I'm wondering if there's now an easier solution. Sounds like @otravers had an answer then - and there are other solutions per the helpful collection of posts - but maybe there's a better way now?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.