Reply
Highlighted
Frequent Visitor
Posts: 7
Registered: ‎09-06-2017

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

[ Edited ]

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.

Established Member
Posts: 255
Registered: ‎06-28-2015

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

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

 

Community Support Team
Posts: 5,994
Registered: ‎08-14-2016

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

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

Frequent Visitor
Posts: 7
Registered: ‎09-06-2017

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

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. :-D

 

brgds

odegarun

Established Member
Posts: 255
Registered: ‎06-28-2015

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

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.

Regular Visitor
Posts: 21
Registered: ‎07-27-2017

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

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

Frequent Visitor
Posts: 7
Registered: ‎09-06-2017

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

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

Frequent Visitor
Posts: 2
Registered: ‎10-18-2017

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

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 Smiley Happy

Frequent Visitor
Posts: 2
Registered: ‎10-18-2017

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

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 Smiley Happy

Visitor
Posts: 1
Registered: ‎12-25-2017

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

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.