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

[Anonymous]

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

[Anonymous]

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

[MichaelLowden]

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 · Accepted solution]

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]

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]

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

[HJR]

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.