Forum Discussion

KasperJ90's avatar
KasperJ90
Icon for Helper III rankHelper III
3 years ago

Change from import to direct query

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? 

Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      KasperJ90
      Icon for Helper III rankHelper III
      3 years ago

      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? 

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      KasperJ90
      Icon for Helper III rankHelper III
      3 years ago

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