populate Column with names from 3 different tabels if....

[nahaku]

TeamPerformanceSmallmanV1.xlsx
	A	B	C	D	E	F	G	H	I
1	Team Members	Team	Product	Sale Date	Region	Login Hours	Total Talk Time	Total Hold Time	Sales
2		Team A	Product 1	01/06/2014	Alabama	7.0	6.0	0.5	22
3		Team A	Product 2	01/06/2014	California	7.5	6.5	0.7	19
4		Team A	Product 2	02/06/2014	Colorado	8.0	7.0	0.5	45
5		Team B	Product 2	03/06/2014	Florida	8.0	6.0	1.2	50
6		Team C	Product 2	04/06/2014	Florida	4.0	3.0	0.5	55
7		Team D	Product 2	05/06/2014	Alabama	8.0	6.0	1.4	60
8		Team E	Product 1	01/06/2014	California	9.0	8.2	0.7	18
9		Team F	Product 1	02/06/2014	Connecticut	7.6	6.6	0.7	18
10		Team G	Product 1	03/06/2014	Hawaii	4.0	3.0	0.5	18
11		Team H	Product 1	01/06/2014	California	7.6	6.6	0.7	18
12		Team I	Product 1	01/06/2014	California	7.6	6.6	0.7	18
13		Team J	Product 2	05/06/2014	Alabama	3.0	2.0	0.5	70
14		Team A	Product 2	05/06/2014	Alabama	8.0	6.0	1.4	80
15		Team B	Product 2	05/06/2014	Alabama	8.0	6.0	1.4	80
16		Team C	Product 1	02/06/2014	Connecticut	3.0	2.0	0.7	24
TeamPerf

I have this table. where i want to somehow get the Names from 3 other tables what contains different type of data, some Names are repeating in those tables some are only in one of those 3 tables. I would like to import those names in Column B, so I can then use those names to populate rest of the columns in this tab based on the logins in column B. I tried to do it by Power Query but it seems to does not want to make relationship between my Querries "Connection only" tables what are filtred to my needs, but I did not put them in Table format to excel.

 

[nahaku]

This is how the 3 tables looks. Picker, Packer and Staff are columns I want put in the Names Column B

TeamPerformanceSmallmanV1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF
1	Picking																					Packing																	Putaway
2	warehouse_code	picker	pick_no	task_type	region_name	pick_type	oc_type	date_source	begin_time	end_time	times	location_cnt	repick_qty	sku_variety	oc_qty	pcs_qty	weight	volume				warehouse_code	pack_no	pack_table_no	packer	task_type	oc_type	date_source	begin_time	end_time	times	oc_qty	pcs_qty	weight	volume				warehouse_code	consignment_no	customer_code	sku_id	status	carrier_type	transport_type	data_source	staff	region_name	street	cell_type	receive_time	putaway_begin	putaway_end	times	PCS	weight	volume
3		GBS0085		MOS	LE	PDA_Pick	Normal	FB4	2019-11-24 19:37:25	2020-01-09 08:15:38	4E+06	2	0	2	2	4	0.002	0.02							GBL0001	MOM	others	FB4	2019-12-31 09:19:11.000000	2020-01-03 08:45:31.000000	####	1	1	0	0								Complete			GRS	GBS0197		999			2020-02-04 11:16:16	2020-02-04 11:16:25	9	1	0.42	0.0007
4		GBS0105		MOS	B2,LE	PDA_Pick	Normal	FB4	2020-02-02 08:06:42	2020-02-02 08:07:13	31	4	0	2	2	6	0.011	0.021							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:31:36.000000	2020-01-02 07:31:47.000000	11	1	4	0	0.02								Complete			GRS	GRS-GBS0060		999			2020-01-06 15:34:54	2020-01-06 15:41:10	376	3	3.3	0.0099
5		GBS0063		MODS	LE	offline	others	FB4	2019-11-28 11:28:25	2020-01-21 12:00:03	5E+06	4	0	3	1	9	0.007	0.024							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:31:50.000000	2020-01-02 07:32:01.000000	11	1	4	0	0.02								Complete			GRS	GRS-GBS0060		999			2020-01-06 15:06:55	2020-01-06 15:15:30	515	1	1.895	0.0065
6		GBS0089		SO	A	offline	Normal	FB4	2019-12-02 14:36:49	2020-01-14 08:11:56	4E+06	1	0	1	1	1	0.03	0.019							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:32:11.000000	2020-01-02 07:32:28.000000	17	1	8	0	0.02								Complete			GRS	GBS0135		999			2020-02-26 15:31:58	2020-02-26 15:33:28	90	4	17.918	0.0311
7		GBS0089		SO	LE	offline	Normal	FB4	2019-12-03 15:13:56	2020-01-25 10:35:54	5E+06	3	0	3	20	20	0.161	1.938							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:32:30.000000	2020-01-02 07:32:41.000000	11	1	8	0	0.02								Complete			GRS	GRS-GBS0060		999			2020-02-05 10:29:51	2020-02-05 10:29:52	0	11	6.82	0.03
8		GBS0089		SO	LE	offline	Normal	FB4	2020-01-14 10:45:05	2020-01-14 10:45:36	31	2	0	6	9	9	0.016	0.082							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:32:42.000000	2020-01-02 07:32:58.000000	16	1	16	0	0.02								Complete			GRS	GBS0157		999			2020-02-14 14:37:47	2020-02-14 14:37:48	0	175	367.5	0.882
9		GBS0089		SO	LE	offline	Normal	FB4	2020-01-14 08:15:26	2020-01-14 08:16:37	71	6	0	11	17	17	0.212	1.743							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:32:59.000000	2020-01-02 07:33:15.000000	16	1	4	0	0.02								Complete			GRS	GRS-GBS0060		999			2020-01-02 12:14:47	2020-01-02 12:15:09	22	2	0.66	0.0036
10		GBS0089		SO	LE	offline	Normal	FB4	2019-12-06 13:51:07	2020-01-14 08:15:12	3E+06	1	0	1	1	1	0.001	0.007							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:33:17.000000	2020-01-02 07:33:38.000000	21	1	5	0	0.01								Complete			GRS	GRS-GBS0060		999			2020-01-02 12:06:11	2020-01-02 12:14:47	0	2	1.22	0.0047
11		GBS0110		MODS	LE	offline	FBA	FB4	2019-12-27 13:39:37	2020-01-07 09:17:02	9E+05	3	0	3	1	38	0.004	0.026							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:33:41.000000	2020-01-02 07:33:51.000000	10	1	4	0	0.02								Complete			GRS	GRS-GBS0060		999			2020-01-02 12:30:01	2020-01-02 12:31:54	113	8	10.48	0.097
12		GBS0068		MOS	A	offline	FBA	FB4	2019-12-30 08:20:39	2020-01-05 11:33:33	5E+05	4	0	1	1	4	0.006	0.059							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:33:53.000000	2020-01-02 07:34:05.000000	12	1	5	0	0.02								Complete			GRS	GRS-GBS0060		999			2020-01-02 11:58:22	2020-01-02 12:06:11	469	56	302.4	2.3272
13		GBS0191		MODS	A	offline	others	FB4	2019-12-31 07:30:57	2020-01-02 11:31:47	2E+05	31	0	22	1	3280	0.181	0.775							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:34:06.000000	2020-01-02 07:34:16.000000	10	1	5	0	0.02								Complete			GRS	GRS-GBS0060		999			2020-02-05 16:06:48	2020-02-05 16:06:49	0	97	74.205	0.3213
14		GBS0179		SO	B1	offline	Normal	FB4	2020-01-02 08:12:41	2020-01-02 08:36:46	1445	1	0	1	20	20	0.099	0.294							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:34:18.000000	2020-01-02 07:34:43.000000	25	1	4	0	0.02								Complete			GRS	GBS0157		999			2020-02-01 11:13:11	2020-02-01 11:13:12	0	14	4.9	0.0273
15		GBS0179		SO	B1	offline	Normal	FB4	2020-01-02 08:12:41	2020-01-02 08:40:43	1682	1	0	1	20	20	0.099	0.294							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:35:10.000000	2020-01-02 07:35:29.000000	19	1	10	0	0.02								Complete			GRS	GRS-GBS0060		999			2020-01-02 12:27:35	2020-01-02 12:28:06	31	3	0.9	0.0011
16		GBS0179		SO	B1	offline	Normal	FB4	2020-01-02 08:12:41	2020-01-02 08:44:02	1881	1	0	1	19	19	0.094	0.279							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:35:30.000000	2020-01-02 07:35:48.000000	18	1	5	0	0.02								Complete			GRS	GBS0135		999			2020-02-26 15:13:04	2020-02-26 15:31:58	1134	1	0.285	0.0005
17		GBS0083		SO	C1	PDA_Pick	Normal	FB4	2020-01-02 06:10:05	2020-01-02 06:31:52	1307	2	0	1	29	29	0.867	2.226							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:35:50.000000	2020-01-02 07:36:07.000000	17	1	4	0	0.01								Complete			GRS	GRS-GBS0157		999			2020-01-08 12:27:29	2020-01-08 12:28:05	0	5	1.5	5E-06
18		GBS0128		SO	C1	PDA_Pick	Normal	FB4	2020-01-02 06:10:48	2020-01-02 06:15:28	280	3	0	1	28	28	0.179	0.407							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:36:09.000000	2020-01-02 07:36:29.000000	20	1	6	0	0.01								Complete			GRS	GBS0157		999			2020-02-19 06:53:51	2020-02-19 06:54:30	39	3	4.29	0.0369
19		GBS0088		SO	B2	PDA_Pick	Normal	FB4	2020-01-02 06:24:38	2020-01-02 06:24:42	4	1	0	1	28	28	0.168	0.659							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:36:30.000000	2020-01-02 07:36:45.000000	15	1	4	0	0.01								Receiving			GRS	GRS-GBS0060		999			2020-01-02 12:31:54	2020-01-02 12:33:24	90	1	0.66	0.0022
20		GBS0083		SO	A	PDA_Pick	Normal	FB4	2020-01-02 06:37:03	2020-01-02 06:37:12	9	1	0	1	27	27	0.116	0.831							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:36:47.000000	2020-01-02 07:37:01.000000	14	1	4	0	0.01								Receiving			GRS	GRS-GBS0060		999			2020-01-02 12:40:05	2020-01-02 12:41:34	89	1	4.7	0.0423
21		GBS0128		SO	B1	PDA_Pick	Normal	FB4	2020-01-02 06:26:47	2020-01-02 06:35:29	522	3	0	1	28	28	0.723	4.469							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:37:02.000000	2020-01-02 07:37:24.000000	22	1	5	0	0.01								Receiving			GRS	GRS-GBS0060		999			2020-01-02 11:50:14	2020-01-02 11:50:15	0	1	1.5	0.0005
22		GBS0179		SO	B1	offline	Normal	FB4	2020-01-02 08:12:41	2020-01-02 08:43:40	1859	2	0	1	25	25	0.061	0.364							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:37:26.000000	2020-01-02 07:37:44.000000	18	1	2	0	0.01								Complete			GRS	GRS-GBS0060		999			2020-01-03 17:15:53	2020-01-03 17:23:55	482	1	0.9	0.012
23		GBS0179		SO	B1	offline	Normal	FB4	2020-01-02 08:12:41	2020-01-02 08:47:30	2089	1	0	1	12	12	0.029	0.175							8A9E83C06F131DDB	MOM	Normal	FB4	2020-01-02 07:37:46.000000	2020-01-02 07:37:54.000000	8	1	2	0	0.01								Receiving			GRS	GBS0157		999			2020-02-19 06:53:06	2020-02-19 06:53:51	0	3	4.29	0.0369
Data

 

[sandy666]

like this ?

part of data

Team Members	B	Team	Product	Sale Date	Region	Login Hours	Total Talk Time	Total Hold Time	Sales
	GBS0085	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0105	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0063	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0089	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0110	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0068	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0191	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0179	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0083	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0128	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0088	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBL0001	Team A	Product 1	41791	Alabama	7	6	0.5	22
	8A9E83C06F131DDB	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0197	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GRS-GBS0060	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0135	Team A	Product 1	41791	Alabama	7	6	0.5	22
	GBS0157	Team A	Product 1	41791	Alabama	7	6	0.5	22

 

[nahaku]

like this ?

part of data

Hard to say, but I think so. All I need to do with those 3 tabs is get them in 1 table from where I will take the data to Graphs and reports.
I do not know how to explain it, but I will try. I do not need the ONE Table to consolidate those data in any way. The best would be if I can get data from those 3 tables in to THE ONE table if the names are found in my list of names, what were working on those days. Unfortunately Our data shows some people who are not considered in our calculations. Then It would populate this big table where I would get columns like:
Date , Login, TaskTypePicking, TaskTypePacking, Pick Type, OCtypePicking, OCtypePacking.....
Then I can get all those data by using known functions... It would be much more easier with Power BI, but company is not using it. And the Power Query in Excel does not seems to be so powerfull as Power BI app.

 

[sandy666]

your source tables has not any key column to join them somehow.
the result table from post#3 is incoherent, as you can see each value from B columns has repeated values from main table so you need to add any key column for all four tables

the Power Query in Excel does not seems to be so powerfull as Power BI app.

who said that ????
PBI has more visual bells&whistles , nothing more

 

[nahaku]

your source tables has not any key column to join them somehow.
the result table from post#3 is incoherent, as you can see each value from B columns has repeated values from main table so you need to add any key column for all four tables


who said that ????
PBI has more visual bells&whistles , nothing more

for example in Excel Power Query, I cant create relationship many to many, in PBI I can . and all 3 tabs share the colum content of Picker, Packer, Staff. This 3 columns have more or less same Values in them.

 

[sandy666]

independent from everything else you need key column/columns. it doesn't matter it will be PBI, Power Query or whatever
I suggest to learn more about Power Query and/or Power Pivot or even Power BI (but your company doesn't use it as you said)

 

[nahaku]

in the Power Query editor I cant find the options to create relationship between my tables, it is not there?

https://imgur.com/DCVwK7r

 

[sandy666]

Power Query is NOT a Power Pivot
you need to use Merge
in PBI relationship is under Pivot layout not Power Query

 

[nahaku]

well I manage to get it together, previously it was showing error that it is not possible to merge many to many, but now it worked, but I think it is wrong. it has over 1.3 milion rows, even if it would list all the rows from original, not filtred data it would not be over 250 000. what Option to use when I connecting them by Login Column, have in mind that I do not want to filter those logins because it could filter data I need. but I would preffer to have logins in 1 column and other data next to it.
I used Option from drop menu in Merge window: Full Outter. Default is Left Outter.
I am now thinking, can i make one column with only Logins I want to extract and merge this would it work? if i create only list of unique logins...

https://imgur.com/PeExIfo