• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Merge two tables to get one output

L

lwilt

Member
Is it possible within PP to have two tables get dumped together to work with at the same time in PP? Our sales data comes from two separate tables depending on if it's been invoiced or not and I would like to take all the data as a whole to work with in PP at the same time.
 
Sure it is. You just need to create relationship between the tables in data model.

Edit: Make sure it's one-to-many relationship. Meaning column used as primary key should hold unique values.
 
But two tables wouldn't have anything related other then the fact that they're orders. There's not a common link between the two since there would be different orders in each table. And since our system separates invoiced and non-invoiced orders I'd like data getting dumped into excel to have both at the same time without having to do two separate PP and combining once in excel.
 
What columns do they have? If same columns are present (at least the ones you are interested in) in both table. Then you can merge two tables using PowerQuery and load to PowerPivot data model.

Otherwise, I'd keep the two tables in separate model. No point in mixing unrelated data in same model.
 
They'd be the same columns in both. Are running the PQ would the PP just be pulling the data from excel within other sources to get the PQ's data? Or do you have the PQ place the data somewhere else for the PP to grab?
 
Load both Query to PQ as connection only.

In one of the query, use Append function in ribbon tool to combine two tables.

Then save as connection only. Right click in Query pane and load to -> Check Data model.

When you open PP wizard combined table will be there for you to use.

Edit: Above is true for Excel 2013 or later. If using Excel 2010. You will need to go into PP wizard after creating combined table as connection only, and find the query in existing connections list and bring it into data model.
 
You must log in or register to reply here.
Back
Top