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
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?
@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.
@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)?
@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.
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?
User | Count |
---|---|
123 | |
96 | |
89 | |
73 | |
63 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |