PowerPivot - Consolidating identical data sets from two companies

[j_balsinek]

Hi everyone,


I'm new to this forum and hoping to get help with an issue I've been trying to resolve for a few hours now.


The company I work has two entities with separate sets of accounting books in QuickBooks, the US-based accounting software. I'm trying to combine identical data sets from the two companies in a data model in PowerPivot to analyse it in a single pivot table. For example, I'd like to combine the sales invoices from both companies and look at the consolidated sales data. I'm using ODBC to pull the QuickBooks invoice data into Excel.
Since the two data sets from the two companies have the exact same columns (date, invoice number, customer, amount etc.) and I cannot create direct relationships between them, I'm assuming I have to create another table to link them together. However, I haven't bee able to figure out how exactly to do this and what to use as the key for the relationship(s).

This seems like an obvious and common issue, yet I haven't able to make it work despite having spent a few hours researching solutions on the web. I'm starting to doubt if PowerPivot is even a suitable tool for this.


Any help would be greatly appreciated.

PS: Apologies to those who might be on the powerpivot.com.au forum, I posted the same question on there but then noticed it doesn't seem to have a lot of users.

 

[j_balsinek]

Just to provide some more detail:

1. The reason I'm using PowerPivot, in the first place, is that I want to add some calculated columns to the queries. I've done this in PowerPivot before using DAX, I'm not sure if the same thing is possible in PowerQuery.

2. I have found an article that solves a similar problem by editing the SQL statement for the the queries and combining them into one using UNION. Apparently this isn't an option for me as I can't edit the statement. This is probably related to the fact I'm using a 3rd party ODBC driver to pull the data from QuickBooks into Excel.

 

[alansidman]

If I am understanding correctly, I would load each table into power query and then append one to the other. You now have a single table that you can either bring back to Excel and create a pivot table or you can move directly to power pivot and do the same

 

[j_balsinek]

Yes, I think you're understanding it correctly, and yes, I'll have to append the tables. I eventually came to the same conclusion last night, after posting my original question.

Thanks a lot for your reply!