Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

How to convert a power bi report from import to Direct query mode

Hi All,

 

I have built a report in Import mode. Now i would like to change that to Direct Query mode. What is the best way or the shortest way to achieve it?

 

Thanks in Advance

1 ACCEPTED SOLUTION
mussaenda
Super User
Super User

Hi @Anonymous,

 

Direct Query to Import, you can do.

But Import to Direct Query, you cannot.

 

If you really need to change it to direct query,

my suggestion is to create a new one using direct query then copy your applied steps from your previous report.

It will be quite time consuming but it will achieve your requirement.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

I have been able to successfully change my import mode table to direct query mode for some tables within a report with some complex dax measures and UI hacks such as lots of bookmarks etc. other tables in the report from csv and will remain csv source.

note 1:  if you are using SQL Server and previously you were using import model.:  All the tables need to be in direct query mode not just one.
my example will be using sql server:
step 1:  get the m-query code from advanced editor into a note book for safe storage.  because you wil need to delete and replace the tables that you wish to be direct mode.
step 2:  delete the tables to be from direct query.  Ensure no tables exist from the same data source that are in import mode otherwise it will not work.
step 3:  recreate the same tables with queries from sql server and the option will exist to use direct query mode
step 4:  for all visuals and measures to work ensure that the recreated tables contain exactly the same columns and same table name.

note 2:  this method worked conveniently and with minimal efforts because all my measures for the report were in a seperate measures table which was left untouched .  No measures existed underneath the tables that were deleted to be replaed by the direct query table. 
note 3:  It is a seperate a discussion / debate on keeping measures underneath the relevant tables rather then in a seperate measures table (e.g. Q&A and Quick insight benefit from having measures underneath primarty tables the maesure is working against) .

I hope this helps
Akthar

BAM ... from Oracle (Import) to Snowflake (Direct) this worked a charm. Thanks. Now to go muck with 100 PBIXs and ~5 tables per each. I wonder if I can write a script to change the PBIXs direct, now to compare the contents before/after this change. Cheers.

mussaenda
Super User
Super User

Hi @Anonymous,

 

Direct Query to Import, you can do.

But Import to Direct Query, you cannot.

 

If you really need to change it to direct query,

my suggestion is to create a new one using direct query then copy your applied steps from your previous report.

It will be quite time consuming but it will achieve your requirement.

v-gizhi-msft
Community Support
Community Support

Hi,

 

I think you can not change your Import to DirectQuery directly.

So i recommend you rebuild your file.

https://community.powerbi.com/t5/Desktop/Direct-Query-and-Enterprise-migrating-from-import/m-p/11922... 

Hope this helps.

 

Best Regards,

Giotto Zhi 

Pragati11
Super User
Super User

HI @Anonymous ,

 

​In PBI Desktop follow the steps as below:

1. Open your PBIX file for the report.

2. Go to Edit queries.

3. Select 'Data Source Settings'.

4. If your datasource connection supports both IMPORT and DIRECT QUERY connection type, you will be able to modify this here.

 

If this helps appreciate a Kudos and mark it as a Solution! 🙂

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

No you can't. Doesn't work for SQL Server or other data sources.

See https://community.powerbi.com/t5/Desktop/Direct-Query-and-Enterprise-migrating-from-import/m-p/11922...

So please either you know it or you don't tell people wrong stuff and waste their time.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.