on 07 Sep 2017 17:36:25
Create a way to automate the following manual steps..
Hi all
I recently wanted to change the datasource from Import to Live Query using the "Power BI service" and had a good lookaround without turning up much other than "you can not"...
However I managed to get it going, effectively maintaining my designs/visuals and changing datasource so that I can get away with publishing one report whenever my data changes.
Prerequisites:
* TARGET: A .pbix file with designs/visuals using import, no measures.
* SOURCE: A clean .pbix file using Live Query to a report/dataset published to app.powerbi.com (no designs/visuals)
-This .pbix file must have the same table as the TARGET file (ie: using the same db)
Steps:
1. Rename .pbix files to .zip
2. Copy from SOURCE => TARGET these files: Connections , DataMashup
(I used Total Commander for this as it was simplest and it supports archive files)
3. Rename TARGET file back to .pbix
4. Open TARGET file in Power BI
5. The design/visuals and the Old table structure should be visible under "Fields"
6. A yellow warning reading "There are pending changes in your queries that haven't been applied [Apply changes]"
should also be displayed
7. Click [Apply changes]
8. All designs/visuals should get an (X) and the message "Something's wrong..."
9. Now go to "Get Data" and select your datasource (i wanted to use data from Power BI services, and a previously published
reports dataset. Select your datasource and Load it.
10. The designs/visuals should get the proper data filled inn (connection in model checks out) assuming that the previously
published report/dataset holds the same table (same name etc).
11. PROSPER!!
If you in your TARGET file which is relying on datasource import has created measures and such, these should probably be re-created in the published report/dataset prior to following the steps above (untested).
Note: If you are unable to open the .pbix file in PowerBI after renaming it back to .zip, there might be an issue with your zip program. For my part it failed when using windows inbuildt functionality but worked when using Total Commander to copy files between the .zip files...
Hope this is of value and help.
- Comments (12)
RE: Way to change datasource import to direct/live query
SAME IDEAS EFFECTIVELY: https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=092d297b-314a-4055-b9b9-54612da7fa90 - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=583aebfa-28e8-ee11-a73d-6045bd7ec416 - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=5a31afd5-2fde-ed11-9139-281878e6855d - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=5cff6c7b-4749-ec11-a3ee-281878bd6ffd - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=5d7750ca-02ec-4ecc-80c9-c9002bd99483 - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=71b6a580-6a4a-4ae0-b30c-055382be84f1 - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=7dd9e8f0-836a-4e1f-90ab-a45e9f789d35 - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=826baedb-37b1-4213-be9f-2119e127c347 - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=a172bf22-6dca-4fa8-9ff1-e63bdace8f2e - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=a210eee1-6f5d-ec11-a3ef-281878bd2252 - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=bbbf8b73-072a-ef11-8ee8-000d3a0dfcaf - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=d4743ce5-2ffa-4645-909e-e57ecbaa3c7f - https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=f57712dc-948d-4645-b5b3-4595ad6d2bff
RE: Way to change datasource import to direct/live query
I'm going to be blasting this same comment in all of these ideas. Idea consolidation is clearly in order. I recommend others who agree to upvote all the listed ideas.This really needs to happen. The issue of being unable to switch from IMPORT-to-DIRECTQUERY is truly a problem. I know it's possible to switch from IMPORT-to-DIRECTQUERY. The lack of IMPORT-to-DIRECTQUERY is a productivity killer. Field renaming. Measure recreation. Calculated column recreation. Visuals coping and pasting. It's an awfully unproductive exercise.I understand there are differences between the two storage modes and that there are some things that might break if one switches from one direction to another. That's fair. But why not just alert the developer? As it is, there's no alert or guidance to make it happen because the entire concept of switching is not possible. Stranger still, what of those situations where there are no confounding problems? Even then it's just not possible.Look at all these tickets saying the same thing as early as 2018!Please, would someone please chart a path forward to finally allow switching from IMPORT-to-DIRECTQUERY as you've previously provided the ability to go from DIRECTQUERY-to-IMPORT?
RE: Way to change datasource import to direct/live query
Thanks for this guidance - still relevant!I have found that I only need to delete the datamodel in the Target, Open the Target (re-saved as a pbix) in Power BI Desktop, and choose 'Get Data' from the Power BI dataset. No copying from the Source necessary.Note for Microsoft: I see this an an essential functionality now that we are trying to maintain centralized datasets in a Premium (PPU) environment. I don't understand how else we could develop a new dataset (in Power BI Desktop as an import model), develop an initial report to ensure the model is working as expected, publish the new dataset to our centralized Dataset workspace, and then reconnect our report to the new powerbi:// connection string. Alternatively I suppose I could copy and paste all of the visuals over to a clean report, but golly that can take some time. I guess someday we won't be publishing so many new datasets, but this is definitely an issue now!
RE: Way to change datasource import to direct/live query
Thanks I think it works! But my interaction and data table relationships all disappeared ... Wondering if it is normal or not?
RE: Way to change datasource import to direct/live query
Even though I don't have "Connections" file, I have deleted "Datamodel" in the TARGET and copied "Datamashup" from SOURCE and it worked perfectly. Thanks a lot!!!
RE: Way to change datasource import to direct/live query
I could not get this to work. There were no "Connections" files in the zip files. I copied over the DataMashup but when I opened the SOURCE .pbix, all the visualizations were broken, and remained broken no matter what I did with Get Data or queries, etc.
RE: Way to change datasource import to direct/live query
This WORKS! Thank you SO Much for sharing!!!
RE: Way to change datasource import to direct/live query
A more general idea to be able to swap all datasources from Import to Direct Query (or presumably vice versa) would be better.
RE: Way to change datasource import to direct/live query
I do not have the Connections file.
Do I have to erase all measures and calculate tables from source in order for this procedure to work?
Once I rename target from .zip to .pbix again it loads everything as in Import Mode again :(
RE: Way to change datasource import to direct/live query
When I tried extract the zip it doesn't have TARGET folder.. Can you help?