Change from import to direct query

[KasperJ90]

Hi,

I have a dataset with 40 tables. 1 of these tables are 3 merged views in power query (sales invoice, sales order, sales credit note). So the views are entered in my data model from views on a SQL server and then I merge them in Power Query. How can I make this table work with a live connection to the sql server? End users want to use the refresh button in power bi to get new data. The other tables in my model does not need to change from import mode. How can I archieve this? 

[parry2k]

@KasperJ90 here is a blog post 

[parry2k]

@KasperJ90 You have to be careful about the transformation steps, not all transformations are possible in DQ. If you are using a view, I recommend pushing those transformations back to SQL view instead of doing it in PQ.

[KasperJ90]

@parry2kThanks for the advice, but how can I accomplish direct query by using the existing dataset? Should I create another dataset or how can I do this (not best practice but pragmatic)?

[Greg_Deckler]

@KasperJ90 So, I would highly recommend creating a SQL view that merges the 3 views and then you would remove your import query and create a new query named the same thing that connects to this view in DirectQuery mode. You would end up with a composite data model. Which you have to be careful about, you may have to switch some of your dimensions to Dual mode in order for everything to work properly. 

The reason I recommend creating a view in SQL Server is that it's iffy if your merging in Power Query will be able to fold back to SQL Server.

[KasperJ90]

Hi @Greg_Deckler 

Thanks. I understand your recommendation, but there is a lot of transformation in power query and I would like to see if I can reuse this. Can I create another dataset for this purpose and copy the code via advanced editor? Or is there a more simple way?