merge XLSX files for same sheet name based on specific files names

[abdo meghari]

Hi Guys
I have files contains Table 1 sheet for each file and each files names contain BRIDGESTONE word( so just search for files contains BRIDGESTONE word) .

BRIDGESTONE-4.xlsx
	A	B	C	D	E	F
1	µa฻Jl jaཬJl	İ฽iaJl S1¿	$9iJl	aiaJl jli¿	aiaJl a࣫µ	q µ
2	2,700.000	JAPAN	BRIDGESTONE	BS 1200R20 18PR G580 JAP		1
3	2,680.000	JAPAN	BRIDGESTONE	BS 1200R20 18PR R187 JAP		2
4	2,800.000	JAPAN	BRIDGESTONE	BS 13R22.5 18PR L328 JAP		3
5	4,280.000	JAPAN	BRIDGESTONE	BS 1400R20VSJ TCF JAP		4
6	420.000	THILAND	BRIDGESTONE	BS 175/65R14 EP150		5
7	180.000	TURKEY	BRIDGESTONE	BS 175/70R13 EP150 82S TURK		6
8	280.000	THILAND	BRIDGESTONE	BS 175/70R13 EP150 THI		7
9	215.000	THILAND	BRIDGESTONE	BS 175/70R14 MY02 THI		8
10	260.000	Indonesia	BRIDGESTONE	BS 185/65R14 EP150 IND		9
11	315.000	JAPAN	BRIDGESTONE	BS 185/65R15 B250 JAP		10
12	290.000	Indonesia	BRIDGESTONE	BS 185/70R13 EP150 IND		11
13	360.000	THILAND	BRIDGESTONE	BS 185/70R14 B250 THI		12
14	435.000	TURKEY	BRIDGESTONE	BS 185R14C R660 102R100R 8 TURK		13
15	288.000	THILAND	BRIDGESTONE	BS 195/60R15 EP150 THI		14
16	267.000	JAPAN	BRIDGESTONE	BS 195/60R15 T001 JAP		15 16 17 18 19 20 21 22 23 24 25 26 27 28
17	335.000	JAPAN	BRIDGESTONE	BS 195/65R15 EP150 JAP
18	740.000	JAPAN	BRIDGESTONE	BS 195/70R15C R623 JAP
19	342.000	JAPAN	BRIDGESTONE	BS 195R14C 613 JAP
20	490.000	THILAND	BRIDGESTONE	BS 195R14C R623 THI
21	340.000	TURKEY	BRIDGESTONE	BS 195R14C R624 HD 106P104P 8 TURK
22	488.000	TURKEY	BRIDGESTONE	BS 195R14C R660 TURK
23	428.000	JAPAN	BRIDGESTONE	BS 195R15C 613V JAP
24	500.000	THILAND	BRIDGESTONE	BS 195R15C R623 THI
25	490.000	TURKEY	BRIDGESTONE	BS 195R15C R660 TURK
26	409.000	TURKEY	BRIDGESTONE	BS 205/65R15 EP150 TURK
27	300.000	THILAND	BRIDGESTONE	BS 205/65R15 T005 THI
28	525.000	THILAND	BRIDGESTONE	BS 205/70R15C R623 THI
29	443.000	TURKEY	BRIDGESTONE	BS 205/70R15C R660 TURK
30	645.000	THILAND	BRIDGESTONE	BS 205R16C D840 THI		29
31	525.000	THILAND	BRIDGESTONE	BS 215/45R17 RE003 THI		30
32	430.000	THILAND	BRIDGESTONE	BS 215/50R17 EP300 THI		31
33	400.000	Indonesia	BRIDGESTONE	BS 215/55R17 AR20 IND		32
34	395.000	THILAND	BRIDGESTONE	BS 215/55R17 EP300 THI		33
35	435.000	JAPAN	BRIDGESTONE	BS 215/55R17 T005 JAP		34
36	507.000	JAPAN	BRIDGESTONE	BS 215/60R16 ER30 JAP		35
37	522.000	JAPAN	BRIDGESTONE	BS 215/60R16T005A JAP		36
38	484.000	JAPAN	BRIDGESTONE	BS 215/65R16 D688 JAP		37
39	600.000	THILAND	BRIDGESTONE	BS 215/65R16C R611 THI		38
40	560.000	THILAND	BRIDGESTONE	BS 215/70R15C R623 THI		39
41	515.000	TURKEY	BRIDGESTONE	BS 215/70R15C R624 HD109S107S 8 TURK		40
42	505.000	TURKEY	BRIDGESTONE	BS 215/70R15C R660 TURK		41
43	843.000	Indonesia	BRIDGESTONE	BS 215/70R16 D697 IND		42
44	598.000	JAPAN	BRIDGESTONE	BS 225/45R18 T005A JAP		43 44 45 46 47 48 49 50 51 52 53 54 55 56
45	1,340.000	JAPAN	BRIDGESTONE	BS 295/35R20 S001 JAP
46	1,225.000	JAPAN	BRIDGESTONE	BS 315/35R20 DHPA JAP	RFT
47	2,600.000	JAPAN	BRIDGESTONE	BS 315/80R22.5 R248 JAP
48	2,545.000	JAPAN	BRIDGESTONE	BS 315/80R22.5-18PR G580 JAP
49	2,410.000	THILAND	BRIDGESTONE	BS 315/80R22.5-18PR G582 THI
50	2,750.000	JAPAN	BRIDGESTONE	BS 385/65 R22.5 R164 JAP
51	3,150.000	JAPAN	BRIDGESTONE	BS 425/65R22.5 R164 JAP
52	3,710.000	JAPAN	BRIDGESTONE	BS 445/65R22.5 R164 JAP		113
53	757.000	JAPAN	BRIDGESTONE	BS 650R16 R230 JAP		114
54	1,180.000	JAPAN	BRIDGESTONE	BS 750R16 VSJ JAP		115
55	938.000	JAPAN	BRIDGESTONE	BS 750R16 VSJ TCF JAP	18	116
56	1,405.000	JAPAN	BRIDGESTONE	BS 8.25R16 R180 TCF JAP		117 118 119 120
57	870.000	JAPAN	BRIDGESTONE	BS LT285/75R16 AT001 JAP
58	348.000	JAPAN	BRIDGESTONE	FS 225/45R17 TZ700 JAP
59	522.000	Indonesia	BRIDGESTONE	FS 31*10.50R15 DEST AT IND
Table 1

BRIDGESTONE-6.xlsx
	A	B	C	D	E	F
1	µa฻Jl jaཬJl	İ฽iaJl S1¿	$9iJl	aiaJl jli¿	aiaJl a࣫µ	q µ
2	2,800.000	JAPAN	BRIDGESTONE	BS 1200R24 20PR G582 JAP		1
3	2,640.000	JAPAN	BRIDGESTONE	BS 13R22.5 18PR R187 JAP		2
4	4,060.000	JAPAN	BRIDGESTONE	BS 1400R20 TCF R180 JAP		3
5	4,160.000	JAPAN	BRIDGESTONE	BS 1400R20VSJ TCF JAP		4
6	393.000	THILAND	BRIDGESTONE	BS 175/65R14 EP150		5
7	168.000	TURKEY	BRIDGESTONE	BS 175/70R13 EP150 82S TURK		6
8	261.000	THILAND	BRIDGESTONE	BS 175/70R13 EP150 THI		7
9	200.000	THILAND	BRIDGESTONE	BS 175/70R14 MY02 THI		8
10	242.000	Indonesia	BRIDGESTONE	BS 185/65R14 EP150 IND		9
11	295.000	JAPAN	BRIDGESTONE	BS 185/65R15 B250 JAP		10
12	270.000	Indonesia	BRIDGESTONE	BS 185/70R13 EP150 IND		11
13	335.000	THILAND	BRIDGESTONE	BS 185/70R14 B250 THI		12
14	405.000	TURKEY	BRIDGESTONE	BS 185R14C R660 102R100R 8 TURK		13
15	268.000	THILAND	BRIDGESTONE	BS 195/60R15 EP150 THI		14
16	250.000	JAPAN	BRIDGESTONE	BS 195/60R15 T001 JAP		15 16 17 18 19 20 21 22 23 24 25 26 27 28
17	690.000	JAPAN	BRIDGESTONE	BS 195/70R15C R623 JAP
18	325.000	JAPAN	BRIDGESTONE	BS 195R14C 613 JAP
19	455.000	THILAND	BRIDGESTONE	BS 195R14C R623 THI
20	305.000	TURKEY	BRIDGESTONE	BS 195R14C R624 HD 106P104P 8 TURK
21	320.000	TURKEY	BRIDGESTONE	BS 195R14C R660 TURK
22	423.000	JAPAN	BRIDGESTONE	BS 195R15C 613V JAP
23	455.000	THILAND	BRIDGESTONE	BS 195R15C R623 THI
24	445.000	TURKEY	BRIDGESTONE	BS 195R15C R660 TURK
25	380.000	TURKEY	BRIDGESTONE	BS 205/65R15 EP150 TURK
26	380.000	THILAND	BRIDGESTONE	BS 205/65R15 T005 THI
27	407.000	JAPAN	BRIDGESTONE	BS 205/65R16 T05A JAP
28	490.000	THILAND	BRIDGESTONE	BS 205/70R15C R623 THI
29	410.000	TURKEY	BRIDGESTONE	BS 205/70R15C R660 TURK
30	595.000	THILAND	BRIDGESTONE	BS 205R16C D840 THI		29
31	490.000	THILAND	BRIDGESTONE	BS 215/45R17 RE003 THI		30
32	407.000	THILAND	BRIDGESTONE	BS 215/50R17 EP300 THI		31
33	350.000	Indonesia	BRIDGESTONE	BS 215/55R17 AR20 IND		32
34	365.000	THILAND	BRIDGESTONE	BS 215/55R17 EP300 THI		33
35	405.000	JAPAN	BRIDGESTONE	BS 215/55R17 T005 JAP		34
36	472.000	JAPAN	BRIDGESTONE	BS 215/60R16 ER30 JAP		35
37	485.000	JAPAN	BRIDGESTONE	BS 215/60R16 T005A JAP		36
38	450.000	JAPAN	BRIDGESTONE	BS 215/65R16 D688 JAP		37
39	565.000	THILAND	BRIDGESTONE	BS 215/65R16C R611 THI		38
40	520.000	THILAND	BRIDGESTONE	BS 215/70R15C R623 THI		39
41	475.000	TURKEY	BRIDGESTONE	BS 215/70R15C R624 HD109S107S 8 TURK		40
42	470.000	TURKEY	BRIDGESTONE	BS 215/70R15C R660 TURK		41
43	785.000	Indonesia	BRIDGESTONE	BS 215/70R16 D697 IND		42
44	905.000	THILAND	BRIDGESTONE	BS 215/70R17.5 R265 JAP		43 44 45 46 47 48 49 50 51 52 53 54 55 56
45	555.000	JAPAN	BRIDGESTONE	BS 225/45R18 T005A JAP
46	495.000	THILAND	BRIDGESTONE	BS 225/50R17 EP300 THI
47	495.000	JAPAN	BRIDGESTONE	BS 225/55R17 T005A JAP
48	860.000	JAPAN	BRIDGESTONE	BS 225/55R18 AL001 JAP
49	445.000	Indonesia	BRIDGESTONE	BS 225/60R16 EP300 IND
50	572.000	JAPAN	BRIDGESTONE	BS 225/60R17 D-SPORT JAP
51	610.000	JAPAN	BRIDGESTONE	BS 225/60R18 ALENZA001 JAP
52	580.000	JAPAN	BRIDGESTONE	BS 225/70R15C R623 JAP
53	495.000	TURKEY	BRIDGESTONE	BS 225/70R15C R660 112S110S 8 TURK
54	492.000	JAPAN	BRIDGESTONE	BS 225/70R16 D687 JAP
55	460.000	THILAND	BRIDGESTONE	BS 225/70R16 H005 THI
56	835.000	JAPAN	BRIDGESTONE	BS 225/70R17 D697 8PR JAP
57	2,680.000	JAPAN	BRIDGESTONE	BS 385/65 R22.5 R164 JAP		117
58	2,475.000	THILAND	BRIDGESTONE	BS 385/65 R22.5 R164 THI		118
59	2,930.000	JAPAN	BRIDGESTONE	BS 425/65R22.5 R164 JAP		119
60	3,460.000	JAPAN	BRIDGESTONE	BS 445/65R22.5 R164 JAP		120
61	705.000	JAPAN	BRIDGESTONE	BS 650R16 R230 JAP		121 122 123 124 125 126 127 128
62	1,080.000	JAPAN	BRIDGESTONE	BS 750R16 8PR VSJ JAP
63	920.000	JAPAN	BRIDGESTONE	BS 750R16 R230 TCF JAP
64	795.000	JAPAN	BRIDGESTONE	BS 750R16 VSJ TCF JAP	2018
65	1,310.000	JAPAN	BRIDGESTONE	BS 8.25R16 R180 TCF JAP
66	810.000	JAPAN	BRIDGESTONE	BS LT285/75R16 AT001 JAP
67	325.000	JAPAN	BRIDGESTONE	FS 225/45R17 TZ700 JAP
68	485.000	Indonesia	BRIDGESTONE	FS 31*10.50R15 DEST AT IND
Table 1

result will be

TRT.xlsm
	A	B	C	D	E	F
1	ITEM	BRAND	MARK	ORIGIN	UNIT PRICE	UNIT PRICE
2	1	BS 1200R20 18PR G580 JAP	BRIDGESTONE	JAPAN	2,700.000
3	2	BS 1200R20 18PR R187 JAP	BRIDGESTONE	JAPAN	2,680.000
4	3	BS 1200R24 20PR G582 JAP	BRIDGESTONE	JAPAN	0.000	2,800.000
5	4	BS 13R22.5 18PR L328 JAP	BRIDGESTONE	JAPAN	2,800.000	2,640.000
6	5	BS 1400R20VSJ TCF JAP	BRIDGESTONE	JAPAN	4,280.000	4,160.000
7	6	BS 1400R20 TCF R180 JAP	BRIDGESTONE	JAPAN	0.000	4,060.000
8	7	BS 175/65R14 EP150	BRIDGESTONE	THILAND	420.000	393.000
9	8	BS 175/70R13 EP150 82S TURK	BRIDGESTONE	TURKEY	180.000	168.000
10	9	BS 175/70R13 EP150 THI	BRIDGESTONE	THILAND	280.000	261.000
11	10	BS 175/70R14 MY02 THI	BRIDGESTONE	THILAND	215.000	200.000
12	11	BS 185/65R14 EP150 IND	BRIDGESTONE	Indonesia	260.000	242.000
13	12	BS 185/65R15 B250 JAP	BRIDGESTONE	JAPAN	315.000	295.000
14	13	BS 185/70R13 EP150 IND	BRIDGESTONE	Indonesia	290.000	270.000
15	14	BS 185/70R14 B250 THI	BRIDGESTONE	THILAND	360.000	335.000
16	15	BS 185R14C R660 102R100R 8 TURK	BRIDGESTONE	TURKEY	435.000	405.000
17	16	BS 195/60R15 EP150 THI	BRIDGESTONE	THILAND	288.000	268.000
18	17	BS 195/60R15 T001 JAP	BRIDGESTONE	JAPAN	267.000	250.000
19	18	BS 195/65R15 EP150 JAP	BRIDGESTONE	JAPAN	335.000	0.000
20	19	BS 195/70R15C R623 JAP	BRIDGESTONE	JAPAN	740.000	690.000
21	20	BS 195R14C 613 JAP	BRIDGESTONE	JAPAN	342.000	325.000
22	21	BS 195R14C R623 THI	BRIDGESTONE	THILAND	490.000	455.000
23	22	BS 195R14C R624 HD 106P104P 8 TURK	BRIDGESTONE	TURKEY	340.000	305.000
24	23	BS 195R14C R660 TURK	BRIDGESTONE	TURKEY	488.000	320.000
25	24	BS 195R15C 613V JAP	BRIDGESTONE	JAPAN	428.000	423.000
26	25	BS 195R15C R623 THI	BRIDGESTONE	THILAND	500.000	455.000
27	26	BS 195R15C R660 TURK	BRIDGESTONE	TURKEY	490.000	445.000
28	27	BS 205/65R15 EP150 TURK	BRIDGESTONE	TURKEY	409.000	380.000
29	28	BS 205/65R15 T005 THI	BRIDGESTONE	THILAND	300.000	380.000
30	29	BS 205/65R16 T05A JAP	BRIDGESTONE	JAPAN	0.000	407.000
31	30	BS 205/70R15C R623 THI	BRIDGESTONE	THILAND	525.000	490.000
32	31	BS 205/70R15C R660 TURK	BRIDGESTONE	TURKEY	443.000	410.000
33	32	BS 205R16C D840 THI	BRIDGESTONE	THILAND	645.000	595.000
34	33	BS 215/45R17 RE003 THI	BRIDGESTONE	THILAND	525.000	490.000
35	34	BS 215/50R17 EP300 THI	BRIDGESTONE	THILAND	430.000	407.000
36	35	BS 215/55R17 AR20 IND	BRIDGESTONE	Indonesia	400.000	350.000
37	36	BS 215/55R17 EP300 THI	BRIDGESTONE	THILAND	395.000	365.000
38	37	BS 215/55R17 T005 JAP	BRIDGESTONE	JAPAN	435.000	405.000
39	38	BS 215/60R16 ER30 JAP	BRIDGESTONE	JAPAN	507.000	472.000
40	39	BS 215/60R16T005A JAP	BRIDGESTONE	JAPAN	522.000	485.000
41	40	BS 215/65R16 D688 JAP	BRIDGESTONE	JAPAN	484.000	450.000
42	41	BS 215/65R16C R611 THI	BRIDGESTONE	THILAND	600.000	565.000
43	42	BS 215/70R15C R623 THI	BRIDGESTONE	THILAND	560.000	520.000
44	43	BS 215/70R15C R624 HD109S107S 8 TURK	BRIDGESTONE	TURKEY	515.000	475.000
45	44	BS 215/70R15C R660 TURK	BRIDGESTONE	TURKEY	505.000	470.000
46	45	BS 215/70R16 D697 IND	BRIDGESTONE	Indonesia	843.000	785.000
47	46	BS 215/70R17.5 R265 JAP	BRIDGESTONE	THILAND	905.000	905.000
48	47	BS 225/45R18 T005A JAP	BRIDGESTONE	JAPAN	598.000	555.000
49	48	BS 225/50R17 EP300 THI	BRIDGESTONE	THILAND	495.000	495.000
50	49	BS 225/55R17 T005A JAP	BRIDGESTONE	JAPAN	495.000	495.000
51	50	BS 225/55R18 AL001 JAP	BRIDGESTONE	JAPAN	860.000	860.000
52	51	BS 225/60R16 EP300 IND	BRIDGESTONE	Indonesia	445.000	445.000
53	52	BS 225/60R17 D-SPORT JAP	BRIDGESTONE	JAPAN	572.000	572.000
54	53	BS 225/60R18 ALENZA001 JAP	BRIDGESTONE	JAPAN	610.000	610.000
55	54	BS 225/70R15C R623 JAP	BRIDGESTONE	JAPAN	580.000	580.000
56	55	BS 225/70R15C R660 112S110S 8 TURK	BRIDGESTONE	TURKEY	495.000	495.000
57	56	BS 225/70R16 D687 JAP	BRIDGESTONE	JAPAN	492.000	492.000
58	57	BS 225/70R16 H005 THI	BRIDGESTONE	THILAND	460.000	460.000
59	58	BS 225/70R17 D697 8PR JAP	BRIDGESTONE	JAPAN	835.000	835.000
60	59	BS 295/35R20 S001 JAP	BRIDGESTONE	JAPAN	1,340.000	0.000
61	60	BS 315/35R20 DHPA JAP	BRIDGESTONE	JAPAN	1,225.000	0.000
62	61	BS 315/80R22.5 R248 JAP	BRIDGESTONE	JAPAN	2,600.000	0.000
63	62	BS 315/80R22.5-18PR G580 JAP	BRIDGESTONE	JAPAN	2,545.000	0.000
64	63	BS 315/80R22.5-18PR G582 THI	BRIDGESTONE	THILAND	2,410.000	0.000
65	64	BS 385/65 R22.5 R164 JAP	BRIDGESTONE	JAPAN	2,750.000	2,680.000
66	65	BS 385/65 R22.5 R164 THI	BRIDGESTONE	THILAND	2,475.000	2,475.000
67	66	BS 425/65R22.5 R164 JAP	BRIDGESTONE	JAPAN	3,150.000	2,930.000
68	67	BS 445/65R22.5 R164 JAP	BRIDGESTONE	JAPAN	3,710.000	3,460.000
69	68	BS 650R16 R230 JAP	BRIDGESTONE	JAPAN	757.000	705.000
70	69	BS 750R16 VSJ JAP	BRIDGESTONE	JAPAN	1,180.000	1,080.000
71	70	BS 750R16 R230 TCF JAP	BRIDGESTONE	JAPAN	0.000	920.000
72	71	BS 750R16 VSJ TCF JAP	BRIDGESTONE	JAPAN	938.000	795.000
73	72	BS 8.25R16 R180 TCF JAP	BRIDGESTONE	JAPAN	1,405.000	1,310.000
74	73	BS LT285/75R16 AT001 JAP	BRIDGESTONE	JAPAN	870.000	810.000
75	74	FS 225/45R17 TZ700 JAP	BRIDGESTONE	JAPAN	348.000	325.000
76	75	FS 31*10.50R15 DEST AT IND	BRIDGESTONE	Indonesia	522.000	485.000
Table 1

the result should be:
1-the column F should be in column A in TRT file
2-the column D should be in column B in TRT file
3-the column C should be in column C in TRT file
4-the column B should be in column D in TRT file
5- the column A should be in column E,F,..... so on
as POINT 5 will there are many prices for the same BRAND or there are new brands for each file , then will brings all of brands from all of file and insert new column for each file contains prices so should put price for each file in adjacent brand without forgetting insert new columns contains prices for each file alone
all of files will be in this device "C:\Users\abmg\Desktop\REP"
the TRT file will be open and the others files will be closed
I hope some body help

 

[Sergius]

Hello! Made using Power Query.

let
    Source = Folder.Files("C:\Users\user\Documents\REP"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"µa฻Jl jaཬJl", type number}, {"İ฽iaJl S1¿", type text}, {"$9iJl", type text}, {"aiaJl jli¿", type text}, {"aiaJl a࣫µ", type any}, {"q µ", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","BRIDGESTONE","UNIT PRICE",Replacer.ReplaceText,{"Source.Name"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Replaced Value", "Source.Name", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Source.Name.1", "Source.Name.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Source.Name.2", "aiaJl a࣫µ"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Source.Name.1]), "Source.Name.1", "µa฻Jl jaཬJl", List.Sum),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"q µ", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"İ฽iaJl S1¿", "ORIGIN"}, {"$9iJl", "MARK"}, {"aiaJl jli¿", "BRAND"}, {"Index", "ITEM"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ITEM", "BRAND", "MARK", "ORIGIN", "q µ", "UNIT PRICE-3", "UNIT PRICE-4", "UNIT PRICE-6"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"q µ"})
in
    #"Removed Columns1"

Here is the resulting table.

ITEM	BRAND	MARK	ORIGIN	UNIT PRICE-2	UNIT PRICE-3	UNIT PRICE-4	UNIT PRICE-6
1	BS 1200R24 20PR G582 JAP	BRIDGESTONE	JAPAN		2800		2800
2	BS 1200R20 18PR G580 JAP	BRIDGESTONE	JAPAN	2700		2700
3	BS 13R22.5 18PR R187 JAP	BRIDGESTONE	JAPAN		2640		2640
4	BS 1200R20 18PR R187 JAP	BRIDGESTONE	JAPAN	2680		2680
5	BS 1400R20 TCF R180 JAP	BRIDGESTONE	JAPAN		4060		4060
6	BS 13R22.5 18PR L328 JAP	BRIDGESTONE	JAPAN	2800		2800
7	BS 1400R20VSJ TCF JAP	BRIDGESTONE	JAPAN	4280	4160	4280	4160
8	BS 175/65R14 EP150	BRIDGESTONE	THILAND	420	393	420	393
9	BS 175/70R13 EP150 82S TURK	BRIDGESTONE	TURKEY	180	168	180	168
10	BS 175/70R13 EP150 THI	BRIDGESTONE	THILAND	280	261	280	261
11	BS 175/70R14 MY02 THI	BRIDGESTONE	THILAND	215	200	215	200
12	BS 185/65R14 EP150 IND	BRIDGESTONE	Indonesia	260	242	260	242
13	BS 185/65R15 B250 JAP	BRIDGESTONE	JAPAN	315	295	315	295
14	BS 185/70R13 EP150 IND	BRIDGESTONE	Indonesia	290	270	290	270
15	BS 185/70R14 B250 THI	BRIDGESTONE	THILAND	360	335	360	335
16	BS 185R14C R660 102R100R 8 TURK	BRIDGESTONE	TURKEY	435	405	435	405
17	BS 195/60R15 EP150 THI	BRIDGESTONE	THILAND	288	268	288	268
18	BS 195/60R15 T001 JAP	BRIDGESTONE	JAPAN	267	250	267	250

 

[abdo meghari]

Despite of I don't like PQ because it doesn't succeed for many cases, but I test it to show what I get.
as usual show me error when use power query

= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

Expression error: The name 'Sample File' is not recognized. Make sure you type it correctly.

 

[Sergius]

What are the files in the REP catalog?

 

[abdo meghari]

XLSX as in OP !

 

[Sergius]

Have you replaced your path to your folder in this line?

Source = Folder.files ("C: \ Users \ User \ Documents \ Rep")

When hovering a mouse at the request "Sample File", what does it show? Should show the file from your folder (as on the screenshot).

Show a photo with PQ, where is the step "Expanded Table Column1". It should be similar to the fact that on the screen

 

[abdo meghari]

Have you replaced your path to your folder in this line?

yes

When hovering a mouse at the request "Sample File", what does it show? Should show the file from your folder (as on the screenshot).

how show this menu ?
if shows error, it won't load data inside sheet to see this menu!

 

[Sergius]

Open your final file. In the "data" menu, select "Queries & Connections". When the window opens on the right, bring the mouse on Query "Sample File". A pop-up window will appear on the left, in which there will be a file name, for example, "Bridgestone-4.xlsx" (see the photo). You can have different it. The request was made based on the name of your files that in the first message: Bridgestone-4.xlsx and Bridgestone-6.xlsx.

 

[abdo meghari]

this is different wit comparison for your picture!

 

[Sergius]

Let's do this. I will describe the sequence of actions, and you will do it step by step.
1. Create a new file.
2. On the "Data" tab, select "Get Data", and then "from File". In this menu, select "From Folder". Such a window will appear

3. In this window, on the "Combine" button, click the arrow and select the "Combine & Transform Data" in the menu.
4. In the next window, select the sheet (see the photo) on which you have a table (in the example of Table 1) and click "OK".

5. Open editor Power Query

6. The editor of Power Query opens. Here we select the first column (in it we have the names of files). Then on the Home tab, select the item "Replace Values" and in the window we replace the first part of "Bridgestone" with "Unit Price"

As a result, in the first column the name is taken by this type of Unit Price-4.xlsx, Unit Price-6.xlsx, etc.

7. Without removing the discharge from the first column Select Home> Split Column> by Number of Characters and in the window opened we enter the number "5" (the number of characters starting from the point). Choose the second item "Once, As Far Right as Possible" and Select OK.

As a result, we divided the first column into two: in one name to the point, in the second from the point.

8. At this step, we delete the second column. Choose it, click "Remove Columns", click "Remove Columns"

9. Choose the first column, Select Transform> Pivot Column. In the default window that opened, we have chosen (look at the photo) the second column with the amounts, which we need. Further Select OK.

As a result, we will have additional UNIT Price-4 columns at the end of the table, etc.

10. At this step, we make a column sorting (see the photo)

11. At this step, we add a new column index and choose to start with "1" (see the photo).

12. At this step, we must rename the names of the columns (see the photo).

13. At this step, we change the sequence of columns, i.e. Build them in the right order (see the photo).

14. At this step we delete unnecessary columns (see photo).

15. At this step we replace "null" with "0" in the columns where we have amounts (see photo).

After that, all that's left is to load the query onto the sheet. Here's what happened after loading the query.

I hope this helps you create your query from scratch.