Changing The Db Location and/or DB Migration


#1

Dear Sam

One of the hottest things that might scare and a nightmare at least to me when you create a complete project and the customer decide suddenly any of the following cases:

1-Changing (Migrating) the Tables type e.g. From (Excell sheets) to (SQL Server, MySql or Oracle).
2-Transferring the DB address from Location A into location B with completely different address locations.

In both cases above the structure of the data and the map (ER Diagram) didn’t change all we have to do is to consider it when we will refresh the Data On timely based (Weekly or Monthly), With enough guidance by example if possible how can we solve the two cases issues mentioned above?


#2

This is not difficult as all you need to do is sub in new ‘M’ code into the query editor.

If the data structure is exactly the same, it’s just a simple substitute in the top couple of line of code for that table.

What I would recommend to do it create a new query first to see the exact code and then copy it into the initial one you already have that is connected to excel.

Really simple, I do this all the time for clients.


#3

Can you guide me by example?


#4

Whenever your create a query in Power BI, M code is produced that writes out exactly what you are doing.

This includes the location of the data etc.

See below for an example.

If the data table is exactly the same structure, then all you need to do is create a brand new query pointing to the new data location.

Then copy the code that is produced pointing to that location…then paste it into your original query.

This way you don’t need to change anything post this point.

This is very common when you may start with data in excel but then want to re-point it to a database.

It’s just a matter of playing around with the code in the advanced editor a little bit


#5

Ok I understand now , what about if the Data Migrated let’s say from Excel to Sql Server or MySql ?


#6

Same strategy. No different