PowerPivot Join By Date Column

[ronenbitman]

Hi,

i have 3 tables:

ProductA
Trade | Category | Expiration Date | Quantity

ProductB
Trade | Category | Expiration Date | Quantity


Categories
CategoryID | Category

i have created a join on Category Between the 3 tables, how am i able to do so on the Expiration Date so i will be able to see a pivot table view that would be looking like this (where the table would consist only unique records of Category | ExpirationDate combinations


Category | Expiration Date | ProductA Qty Sum | ProductB Qty Sum

kind regards,

Ronen

 

[illusionek]

I believe you need to create Calendar table and connect A & B through this table.

 

[ronenbitman]

I believe you need to create Calendar table and connect A & B through this table.

i do agree to the idea however the issue is that the data resides in excel table, i am not familiar of a way to create a distinct list of merged dates (between table a & b) that would not use a macro / array formula - is there a way to do it behind the scene using sql?

 

[ImkeF]

Another way would be to consolidate both product tables into one (UNION-wise). You can do this easily with Power Query also on non-SQL-data. Have a look at the file.

Advantages:
- No need for any additional lookup/merge tables (calendar, category) then, if it's just a simple SUM that you want to perform
- Easily adjustable to additional products (if you have dozens of them, you could also transform this into a function) without floading you data model with additional tables
- ...

Anyway - if you want to stick to our current setup, the file also contains a way to create your calendar table based on the date range of your Excel-product tables

You will not be able to extract this information from Excel-tables using SQL statements.

 

[ronenbitman]

thanks your answer rocks!!!

 

[ImkeF]

so does Power Query

Thanks for you feedback!