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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
KasperJ90
Helper III
Helper III

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? 

5 REPLIES 5
parry2k
Super User
Super User

@KasperJ90 here is a blog post 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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? 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.