Connect two tables with order dates and SKU numbers to form a visual chart on one visual

[ccmaomao]

Hi there, 

I am combining two tables together, and want to keep all the data in both tables.

One is an order summary table:

Order Date	SKU Nbr	Order Qty
1/1/2020	A	20
2/4/2020	B	10
1/3/2020	D	20

The other one is a summary of the planned order:

Planned Order Date	SKU Nbr	Planned Order Qty
1/4/2020	B	20
2/4/2020	C	10
3/4/2020	D	20

We want to put them in a chart comparing the order quantity and the axis will be the Date. So both SKU Nbr and order date need to be connected, but Power BI could not do multiple relationships between two tables. Since the order dates and SKUs numbers are not matching for each row, Concat will make me lose data rows.

Does anyone have any idea how to combine the two tables on SKU number and order date and keep all the information?

Thanks!

CC

[amitchandak · Accepted solution]

@ccmaomao , You can create a common date and SKU and use these dimensions to analyze.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Common Table - https://www.youtube.com/watch?v=Bkf35Roman8

[ccmaomao]

Solved it. Thanks 🙂

[ccmaomao]

Hi Amit,

Thanks for your reply and the videos. 

Shall I do the following based on your suggestions and video?

A bridging table with all distinct SKU number and connect to the two tables,

and another briding table with distinct date and connect to the two tables?

Thanks a lot!!!!

CC

[jthomson]

Make custom columns in each table in Power Query which look something like Text.From([Order Date])&"_"&[SKU Nbr] and link using that?

[ccmaomao]

Thanks, jthomas!

Will concat the two keys and then join keep all the information, or it is a inner join?

Thanks,

CC