get data from disbanded database and combine with new one

[jessewysong]

Hey all,

Hoping someone can start me down the right path with a link to a tutorial:

We had to start over with our SQL database, so I have one database that has 8 years of data up until mid July, and then another that is just mid July on. The field and table names are the same in both databases. The database is the backend on our inventory and sales order managment software program. 

Is it possible to query both databases so I can see seamless historical trends in my reports and dashbaords? I was just starting to feel like I had made some great progress in Poer BI and now all my reports can only show data from July 2016. When we started over I had to repoint all the reports to the new database in Power BI desktop, and we connect to the on premsises database via the Enterprise Gateway. 

help much appreciated. 

Thank you. 

[KGrice]

Hi @jessewysong. You should be able to bring in both queries, and then append one to the other, since the table structure is all the same. Bring in each dataset separately first, then while in the query editor for one, go to the Home tab in the ribbon and find Append Queries at the far right. Select your other query and click OK.

[jessewysong]

huh... could it really be that simple!? Seemed to work on the first report I tried. Do you think the Enterprise Gateway will need editing as well?

Thank you. 

[KGrice]

Hooray for simple! I don't have experience with the enterprise gateway yet. Form what I know, you shouldn't have to change any of the settings/permissions. Someone else might chime in with their experience, but I would just give it a test and verify your results.

[jessewysong]

I guess I might have jumped the gun a bit. I can add more tables ot the same query, but now I have invoice and invoice (2) for example, and one points to the old database and the other to the new. Im going to research this a bit more on my own. If I drop something into a visual, I can't drop the same field, "total amount" from both the tables (invoice and invoice(2)) and see total amount by month from both datasbes over the last year. 

[KGrice · Accepted solution]

If you're appending one table to the other, you shouldn't end up with two separate tables. All of the data should be usable in one table. For example, I created Table1 in Excel with Dates from 7/1/2016 - 7/31/2016, and Table2 with Dates from 8/1/2016-8/3/2016. I bring tables in, then from the Table1 query, I append Table2. Now all of my amounts from both tables are in my Table1 query.

You can then go to Data View and hide Table2 entirely, leaving you one table with everything in it.