Join 2 tables or connect 2 tables

[uwmarkyo]

I have 2 tables
dataset

table A:

table B:

My goal is to merge or append table B's last 2 columns to table A.
Please be aware, these 2 tables are imported from other databases.
That's why I'm trying power query or power pivot to tidy this data.
I tried to merge them in power query, but it didn't work, and it generated a lot of duplicated numbers from table A.
Can someone give me a guidance of this one, thanks.

 

[Alex Blakenburg]

We need a better understanding of what the 2 tables represent.
The way it looks both are summarised to be unique on the combination of Hour (code) and Date.
If this is correct did you try to do a join on both Hour and Date ?

If one of the tables is more likely to have all records in it so that you can make it the primary in a Left Outer Join.

Using the method above.
Make Table A the main table with a left outer join to Table B on Hour & Date
(Data type on both tables for the date field as "Date)
The result is below:

20211215 PQ Join sample2.xlsx
	A	B	C	D	E	F	G	H	I
1	Month	Week	Day	Date	Segment	LOB	Hour	order	%
2	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	12a-1a
3	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	12a-1a	21	49%
4	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	1a-2a
5	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	2a-3a
6	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	3a-4a
7	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	4a-5a
8	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	5a-6a
9	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	6a-7a
10	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	7a-8a
11	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	8a-9a
12	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	9a-10a
13	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	10a-11a
14	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	11a-12p
15	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	12p-1p
16	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	1p-2p
17	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	2p-3p
18	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	3p-4p
19	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	4p-5p
20	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	5p-6p
21	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	6p-7p
22	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	7p-8p
23	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	8p-9p
24	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	9p-10p
25	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	10p-11p
26	1/11/2020	1	Sun	1/11/2020	Fruit	Mongo	11p-12a
27	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	1a-2a	1	7%
28	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	2a-3a	9	56%
29	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	3a-4a	6	38%
30	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	4a-5a	1	3%
31	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	5a-6a	2	13%
32	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	6a-7a	24	56%
33	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	7a-8a	2	67%
34	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	8a-9a	3	100%
35	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	9a-10a	2	8%
36	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	10a-11a	17	63%
37	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	11a-12p	17	41%
38	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	12p-1p	6	100%
39	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	1p-2p	0	0%
40	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	2p-3p	8	21%
41	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	3p-4p	29	59%
42	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	4p-5p	5	14%
43	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	5p-6p	7	15%
44	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	6p-7p	34	94%
45	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	7p-8p	24	77%
46	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	8p-9p	25	52%
47	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	9p-10p	1	20%
48	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	10p-11p	26	100%
49	1/11/2020	1	Mon	2/11/2020	Fruit	Mongo	11p-12a	3	60%
50	1/11/2020	1	Tue	3/11/2020	Fruit	Mongo	12a-1a
51	1/11/2020	1	Tue	3/11/2020	Fruit	Mongo	1a-2a
52	1/11/2020	1	Tue	3/11/2020	Fruit	Mongo	2a-3a
53	1/11/2020	1	Tue	3/11/2020	Fruit	Mongo	3a-4a
Merge1

 

[uwmarkyo]

We need a better understanding of what the 2 tables represent.
The way it looks both are summarised to be unique on the combination of Hour (code) and Date.
If this is correct did you try to do a join on both Hour and Date ?

If one of the tables is more likely to have all records in it so that you can make it the primary in a Left Outer Join.

Using the method above.
Make Table A the main table with a left outer join to Table B on Hour & Date
(Data type on both tables for the date field as "Date)
The result is below:

Hi Alex,

Thanks for the responding.
And sorry for not explaining it clear enough.

I think you're right. I discovered my table A didn't show my issue I presented.
I fixed my dataset as below:

Table A:

table B:

Both tables contains 2021 record, but I only list a small sample size to display my issue.

These 2 tables are from different workbooks(excel files)
I used power query or power pivot to import them, and then I use merge to join both tables
however, it caused the following issue:

It did join table B, but it also caused duplications on table A.
Can you teach me how to fix it?

Thanks,

 

[Alex Blakenburg]

As I mentioned previously we need to understand what the to tables represent.
What is the description of the report you run for each table ? Is one a summary and one a transaction table or a summary at a different level ?

Both tables have date and hour (code) but TableA also has Folder.
What does folder mean and how does that tie back to TableB ?

Your last image in the previous post, is for a different period than the TableA & TableB images which make it impossible to see the how they relate.

I can't work with pictures.
You either need to give me an XL2BB for both TableA & TableB for at least one full day (the same day on both) or provide that in a spreadsheet via a share service (dropbox, google drive, onedrive etc). Using a share service make the file available to anyone with the link and post the link here. Obviously get rid of anything confidential.

Perhaps also mock up what output you are expecting eg if TableB is a summary and has figures in it and TableA needs to be grouped at a lower level then the figures on TableB will repeat on each group in TableB.

PS: what date format are you using dd/mm/yy or mm/dd/yy ?