How to join two Fact Tables in PowerPivot?

[mrxlsx]

Hi,

Is there any way to combine two Fact Tables in PowerPivot? Actually I have more than 5 Fact Tables with other Dimension tables like Date, Stores, Customers.

Thank you.

 

[ImkeF]

This sounds like a job for Power Pivots ETL-engine, called "Power Query". Have you heard about it?

 

[mrxlsx]

I have found some information about my requirement but I couldn't end up at an easy solution on this. Could you please put me on the right page catching my scruff?

 

[ImkeF]

You import the data into Power Query, merge/join it there and then load into the Power Pivot data model. So it’s like a diversion where the transformations take place:
https://support.office.com/en-us/ar...or-Excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605

1) Import depending on your source: https://support.office.com/en-us/ar...86c-a168-b68e9e616f5a?ui=en-US&rs=en-US&ad=US

2) Vertical join (merge) or horizontal join (UNION, append), depending on your requirement:

a. Horizontal join/UNION: make sure that the columns have the same name: https://support.office.com/en-sg/ar...a43-b37f-99e2b2a4813a?ui=en-US&rs=en-SG&ad=SG

b. If vertical join: You can take multiple fields to connect, so no need to create a concatenated key like in Power Pivot: https://support.office.com/en-za/ar...c0f-b132-7ca2616d17f9?ui=en-US&rs=en-ZA&ad=ZA

3) Load directly to PP data model instead of excel sheet: https://support.office.com/en-sg/ar...er-Query-ca69e0f0-3db1-4493-900c-6279bef08df4

If this doesn't help, please specify which problems you actually have.