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
odegarun
Advocate I
Advocate I

Solution: Changing datasource from Import to Direct Query -> SUCCESS

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.

14 REPLIES 14
mike_honey
Memorable Member
Memorable Member

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.

Anonymous
Not applicable

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

Capture.PNG

 

odegarun
Advocate I
Advocate I

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 🙂

Anonymous
Not applicable

Hi @odegarun,
Thank you for providing these steps.
I am facing issue after 9 step that is when I am trying to get data, all my table/views are getting new name and are not getting replaced by previous fields set available.
All new set of fields is created with new name.
Kindly suggest how to replace existing set of views which is already available.
Nina
Regular Visitor

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.

mwhite226
New Member

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 🙂

mwhite226
New Member

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?

latheesh89
Helper II
Helper II

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

v-shex-msft
Community Support
Community Support

Hi @odegarun,

 

First, thanks for your sharing.Smiley Happy

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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:

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/31272475-way-to-change-datasource...

 

Please vote and comment if you would like to see this function added to Power BI Desktop.

mike_honey
Memorable Member
Memorable Member

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

 

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.