Forum Discussion
Helper VMigrating queries from one source/mode to another. Best practice?
I have a report with a query that runs native SQL to Oracle in import mode. I need to switch that query to sourcing from a Power BI dataset which, will only operate in DQ mode.
I have 8 relationships to other dimension tables and numerous measures built off this fact query/table.
I don't see around re-building this report unfortunately. I hope I'm wrong.
Does anyone have any best bractices they've developed going through a similar exercise?
I actually have six of these queries/tables in this report I have to deal with.
Hmmm. I have to think through this a bit. There might be something in there I can use. Of course, and it's always the case, my situation is a bit more complicated I think. I'm actually going to be creating 6 discreet datasets. The reason is, while 2 of the queries need to refresh multiple times a day the others need to refresh weekly, monthly or not at all.
Hi pawlowski6132 ,
If your model started out in Excel, but now you are forced to connect to data in a database, you do not have to stress out. Yes, unless you watch this video, you will have to rebuild everything, but if you do watch this video, I will show you how you can move your table(s) from one data source type to another without having to break anything.
The steps to switch are the following:
- Bring the new table in (do not delete the old one – NEVER delete the old one)
- Disable load for the new table
- Make the new table look as close as possible to the old table (if you can’t, don’t stress, as long as the old column names can still be found – nothing will break)
- Go to Advanced Editor in Transform Data window and copy the code (M code for the techies) into your clipboard then hit escape
- Now go to Advanced Editor for the old table and replace its code with the new code you had just copied into your clipboard, then hit OK
- Do it for all tables you need to switch and then hit Close and Apply
- You are done, everything should still work measures will still work as long as the column names stayed the same and your relationships should not be broken
Please watch the video for more details:
https://www.youtube.com/watch?v=9AG1gIMRADo
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- parry2k
Super User
pawlowski6132 I'm sure this article will help you to do this or it is easier.
- make copy of your pbix file with all the data and measures in it
- remove all the visual from this file, keep blank page, we will use it as a model
- publish this file and it now become a dataset
- ope original file
- in Power Query, remove all the tables from the file
- close and apply
- all visual will fail
- now choose power bi as a dataset and connect it to the published file
- everything should work (visuals are back)
- and this file become your thin report file
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- pawlowski6132
Hmmm. I have to think through this a bit. There might be something in there I can use. Of course, and it's always the case, my situation is a bit more complicated I think. I'm actually going to be creating 6 discreet datasets. The reason is, while 2 of the queries need to refresh multiple times a day the others need to refresh weekly, monthly or not at all.
- parry2k
pawlowski6132 all the best, feel free to reach out if need more help. Cheers!!
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi pawlowski6132 ,
If your model started out in Excel, but now you are forced to connect to data in a database, you do not have to stress out. Yes, unless you watch this video, you will have to rebuild everything, but if you do watch this video, I will show you how you can move your table(s) from one data source type to another without having to break anything.
The steps to switch are the following:
- Bring the new table in (do not delete the old one – NEVER delete the old one)
- Disable load for the new table
- Make the new table look as close as possible to the old table (if you can’t, don’t stress, as long as the old column names can still be found – nothing will break)
- Go to Advanced Editor in Transform Data window and copy the code (M code for the techies) into your clipboard then hit escape
- Now go to Advanced Editor for the old table and replace its code with the new code you had just copied into your clipboard, then hit OK
- Do it for all tables you need to switch and then hit Close and Apply
- You are done, everything should still work measures will still work as long as the column names stayed the same and your relationships should not be broken
Please watch the video for more details:
https://www.youtube.com/watch?v=9AG1gIMRADo
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
