Results 1 to 4 of 4

Thread: PowerPivot - Consolidating identical data sets from two companies
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default PowerPivot - Consolidating identical data sets from two companies

    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.

  2. #2
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot - Consolidating identical data sets from two companies

    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.

  3. #3
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,935
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot - Consolidating identical data sets from two companies

    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
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot - Consolidating identical data sets from two companies

    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •