Connect old table to new table (even thought they're the same table!)

[kincaids]

Originally, software that I'm bringing data in from was built on Visual Fox Pro and required an ODBC connection to pull in the information. I spent hours and hours "cleaning" the tables so I could more easily create visualizations.

The software was updated - the newest version being built on SQL Server (MS) and I now have to use a different connection to pull in the very same data from the very same tables. This has pulled in a duplicate of each table (I was hoping that it would see that the tables were the same and just update the information in the current tables, but alas...).

I had "renamed" all but one of the tables in PowerBI, so I wondered if the data being pulled in on the SQL connection didn't recognize the name, thus creating a new table, but the one table that I hadn't renamed also came in as a new table (not updating the old), so that isn't the issue.

I'm just wondering if there's any way to "connect" the tables so that I don't have to spend hours upon hours "cleaning" the new tables and redoing all the visualizations. I'm not a "data scientist" - just a sales manager that wants actionable data, so this sort of thing is killing me. Not to mention that the file now has double the tables and is bogging down my computer. I don't necessarily want to delete the old tables because I want to be able to use them to rebuild the new ones if I have to do it all over again (ie - looking at the old tables and seeing how I built a given visualization and copying that using the new tables).

Let me know if you need any clarification.

[v-yuezhe-msft · Accepted solution]

@kincaids ,

This is an expected behavior that when you connect to different data sources which contain same tables.

In your sceanrio, go to File->Options and Settings->Data source Settings, then delete the SQL Server data source. Then go to Power BI Desktop query editor, click on each query and open advanced editor, change the data source from ODBC to SQL Server following the guide in this similar thread.





Regards,
Lydia

[kincaids]

@v-yuezhe-msft,

I attempted to follow your instructions to delete the SQL Server connection, and I could not, because it did not appear there - only the ODBC connection was present. I tried to delete that connection, but couldn't figure out how to.

I then went and deleted the new tables to free up system resources.

Next, I went to the query editor and tried two different ways to change the source per your instructions, and both times, I wasn't able to do so - see the images below:

In both cases, the windows would not allow me to change from the ODBC source to a SQL Server source, but only parameters within the ODBC source.

Your further assistance would be greatly appreciated!

[v-yuezhe-msft]

@kincaids ,

You would need to change data source in Advanced Editor, please check this similar thread.

Regards,
Lydia