Merge tables

[cocomy]

Hi All,

I still can't figure out how to build relationship.

I have two tables

Table 1 - daily forecast by items(500 records)

• Daily calender
• Daily sales forecast
• Items A B C ...

Table 2 - actual sales by items(300 records)

• Date
• Actual sales
• Items A B C....

How we recode date is... like this. If no sales on the day, no recode.

1 Feb 2018    A     2kg

1 Feb 2018    A     1kg

1 Feb 2018    B      3kg

2 Feb 2018    C      2kg

3 Feb 2018    A      1kg

3 Feb 2018    C      2kg

5 Feb 2018    B       1kg

When I merge table 1 and 2, new table C only shows 300 records. How can I set up to show 500 records even empty?

I assume this must be something about relationship? Please advise!

All the best,

coco

[stretcharm]

if your doing the join in the Query editor there are you can do left outer join as part of the merge.

If your doing it in dax try creating a new table with NATURALLEFTOUTERJOIN

the https://msdn.microsoft.com/en-us/library/dn802527.aspx

Relationships do inner joins so you don't see rows that dont have a matching entry.

Here is a nice powerbi that shows the different join types

https://community.powerbi.com/t5/Data-Stories-Gallery/Visualizing-Merge-Join-Types-in-Power-BI/m-p/2...

[cocomy]

Thank you for your advice. It worked perfectly but I realised my original data was incorrect before merging it and wonder if you could help. New problem keeps coming... unfortunately.

Table 1

Date  (1 Jan 2018, 1 Feb 2018...) - monthly basis

Branch (Tokyo, Osaka...)

Daily forecast for each month(2 per day for Jan, 3 per day for Feb)

To match with other data sets, I want to make Table 1 to daily basis....

New Table 2 I want to create

Date  (1 Jan 2018, 2 Jan 2018, 3 Jan 2018.... 1 Feb 2018..) - daily basis

Branch(Tokyo, Osaka...)

Daily forecast (2,2,2.. till end of Jan, 3,3,3... till end of Feb)

I created Calender table to make monthly Table 1 into daily Table 2 but for some reasons... ended up duplications.

Calender table has

Date (1 Jan 2018, 2 Jan 2018, 3 Jan 2018........)

Date 1 (1 Jan 2018, 1 Jan 2018, 1 Jan 2018.......)

I thought I could use Calender's table Date 1 to create relationship with Table 1's Date but did not work...

Hope this makese sense and appreciate your help!

All the best,

coco

[v-huizhn-msft]

Hi @cocomy,

If you have another new issue, please create a new thread. There is only one problem in one thread. Thanks for understanding.

Best Regards,
Angelia

[cocomy]

OK! Thank you for letting me know.

[v-huizhn-msft · Accepted solution]

Hi @cocomy，

You're welcome, it's my pleasure to help you. Could you please mark the right reply for original issue as answer, so more people will get helpful information from here. 

Thanks,

Angelia