How to Switch Your Power BI Table to a Different Data Source Type Without Breaking Anything

~ Business Intelligist

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:

  1. Bring the new table in (do not delete the old one – NEVER delete the old one)
  2. Disable load for the new table
  3. 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)
  4. Go to Advanced Editor in Transform Data window and copy the code (M code for the techies) into your clipboard then hit escape
  5. 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
  6. Do it for all tables you need to switch and then hit Close and Apply
  7. 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:

Published by Business Intelligist

3 thoughts on “How to Switch Your Power BI Table to a Different Data Source Type Without Breaking Anything”

  1. Pingback: Converting Power BI Desktop Reports to Enterprise Ready Components

  2. hi…Thanks for the solution.
    For me I have the columns and tables names completely different when compared with my Old data source to New data source.
    So can you suggest please

    Reply

  3. Yea, this is great and I figured out how to do this and it works. My problem is when I publish it to the service, PBI pro still thinks the original data source is what I originally set up. Originally it was a folder path. I did what you recommended and edited all the M to use SharePoint folder. All great. Reports all great. But after publish it still thinks it needs a gateway to get to the local folder path. So somewhere in the pbix it still has the original data source I set up thru the gui. Any ideas??? I can’t find any way to change the data source to SharePoint folder than the way you specified but the pro service doesn’t like me messing with the advanced editor apparently.

    Reply

Leave a comment