PowerPivot - Consolidating identical data sets from two companies

j_balsinek

New Member
Joined
Sep 5, 2019
Messages
3
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

j_balsinek

New Member
Joined
Sep 5, 2019
Messages
3
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

Well-known Member
Joined
Feb 26, 2007
Messages
7,142
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 5, 2019
Messages
3
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!
 

Forum statistics

Threads
1,176,298
Messages
5,902,365
Members
434,968
Latest member
TutanRamon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top