Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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. 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.
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.
I had to do this again today, went fairly smoothly.
I found you can now skip the SOURCE file altogether. Just rename the TARGET file as a zip, and using Total Commander delete the "Datamodel" file, and rename it back to a .pbix extension.
Then when you open it in Power BI Desktop, the Fields pane will have a link to "Get data". Use that to choose your Power BI Dataset.
Hi @odegarun,
Thank you for sharing this. I tried this in my pbix file. But it seems I have to create all measures. but for Dimensions also it is not showing anything. My guess is because when I had created pbix file with 'Import' option, that time the table names were 'T2' and 'T3'
for 'v_scrm_loa' and 'LOA_measures' respectively and after that they got changed so I had used Navigaton option instead of creating new reports and navigated those tables to new table names respectively. So now when I am applying the above steps to switch from 'import' to 'Live' it is not recognizing all fields as previoulsy all fields were like T2[field]/T3[field] not it became v_scrm_loa[field] and v_LOA_measure[Field]. Is there any way so I can atleast recover all fields. I will create measures but can I cover all fields (Dimension) again?
Thanks!
Regards,
Poonam
Getting some questions on the steps involved.
To be clear:
When I say DELETE/COPY I mean that you shall navigate INTO the .zip file (.pbix file renamed to extension .zip) with a suitable tool for the job. Also, I have found that Total Commander works nicely for this and that the buildt-in windows compression support does not play nice with the file, rendering it unusable when it is renamed back to .pbix and attemting to open it in powerBi desktop...
So; rename .pbix file to .zip extension, navigate into the file deleting and copying files as described below.
Works for me 🙂
Thank you! Very smart solution. However, I still have one tile that is only loading and not showing up. I will delete it and recreate it. Just wanted to inform you.
I got lost here:
=> DELETE "Datamodel" from TARGET
=> COPY "Datamashup" from SOURCE to TARGET, overwrite.
When you say to delete the datamodel do you mean to remove the whole data source, or replace the source information in advanced editor query with that of the SOURCE?
Thanks in advance 🙂
I got lost here:
=> DELETE "Datamodel" from TARGET
=> COPY "Datamashup" from SOURCE to TARGET, overwrite.
When you say to delete the datamodel do you mean to remove the whole data source, or replace the source information in advanced editor query with that of the SOURCE?
Thanks in advance 🙂
I got the same problem as well. How to we delete DATA Model and Copy data mashup while the file already renamed as .zip?
@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?
It might be the .zip operation which causes it.
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....
Hi @odegarun,
First, thanks for your sharing.
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
Regards,
Xiaoxin Sheng
submitted, thanks for the tip.
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. 😄
brgds
odegarun
Here's the Idea:
Please vote and comment if you would like to see this function added to Power BI Desktop.
This worked very nicely for me (especially Step 11) - thanks for taking the time to write it up so thoroughly.
Can I suggest you create an "Idea" for this - if it can be expressed in a few fairly simple steps, then I reckon it should be considered for inclusion in the product.
https://ideas.powerbi.com/forums/265200-power-bi?WT.mc_id=Community
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |