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.
* 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)
1. Rename .pbix files to .zip
2. Do this [edited steps]:
=> DELETE "Datamodel" from TARGET
=> COPY "Datamashup" from SOURCE to TARGET, overwrite.
[ =>Not necessary to copy "Connections" based on latest test, but it does not seem to hurt]
(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]"
may or may not be displayed
7. Click [Apply changes] if it appears
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). If Not all visuals are correct, the proper measures may not be present in the datasource, re-create them there and open the TARGET pbix again to check.
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...
As mike_honey mentioned, I'd like to suggest your submit this requirement ideas to get the official support for this solution. Since you are modify and replace internal files, I'm not so sure if your operation has caused any hidden broken of the source file. It may caused the broken your data and hard to troubleshooting and recover the losses
Community Support Team _ Xiaoxin Sheng If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
For learning resources/Release notes, please visit: | |
I can not see that my method has caused any problems for me, not yet atleast ;-)
Changed the datasource in this way on a pbix having a import of roughly 43.000 rows (27 columns) to using Power BI services connecting the pbix to a published pbix which had the same import (among others (totalling about 30 tables with roughly 300k rows and numerous columns distributed on the tables) ). I simply added the missing table required to my datasources and re-published my master report (it has about 16 pages with a lot of visual on each page)...
In this way I was able to publish the pbix with a connection to the Power BI services once, and it is updated whenever I publish my master report. :-D
@odegarun: Thanks for your solution. I am trying a smiliar thing in my project, I want to edit "Connections" file and modify the initial catalog info. I followed your steps. 1. Change to .zip extension 2. Edit "Connections" file and save 3. again convert back to .pbix extension.
But when I try to opent the file, I am getting error saying the file has been corrupted. Any suggestions or alternative?
I had some similar problems myself whenever using windows inbuildt zip functionality (and 7zip for that matter)... Only way I could get it going was by using total commander which makes it possible to navigate the zip as a normal folder since total commander directly supports archive files. Using total commander it was just a matter of browsing down into the pbix file renamed to .zip and making the changes, navigate out of the .zip and renaming it back to the original file extension.
However; I did get that exact same error when attempting some other changes, so it might be that the changes you make needs to be reflected in other files which you do not change and thus triggering the "corrupt file" message....