Forum Discussion
Merge tables
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
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
5 Replies
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
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
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
