Re-using an existing query

[Tigerexcel]

Can you apply an existing query without having to go into the Advanced Editor and amend the "M" programming?
I'll illustrate with an example, the first table has a simple query applied to it:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Subject", type text}, {"Score", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Subject"}, {{"Count", each [Name]}}),
#"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Count", each Text.Combine(List.Transform(_, Text.From), " "), type text})
in
#"Extracted Values"

I receive another table which is in columns H to J, I would like the same output as in columns E to F. I could do it by copying the above query and then manually editing the Table name to whatever the new table is called but I suspect there's a more efficient way of doing this.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Subject	Score					New table
2	Jack	Maths	84		Subject	Count		Name	Subject	Score
3	Fred	Chem	73		Maths	Jack Mary Eddie Jake		Ted	Maths	20
4	Pete	English	89		Chem	Fred Sally Sara Noel		Ian	English	30
5	Harry	French	88		English	Pete Don Joel		Ken	Maths	40
6	Mary	Maths	60		French	Harry Ken Tom		Mary	English	50
7	Sally	Chem	62					Bart	Maths	60
8	Don	English	79					Bert	English	70
9	Ken	French	91					Herb	Maths	80
10	Eddie	Maths	70
11	Sara	Chem	92
12	Joel	English	75
13	Tom	French	88
14	Jake	Maths	94
15	Noel	Chem	95
Sheet1

 

[smozgur]

One way I can think of is storing the source table name in a cell that you'd like to change as the source table, reading it in the Power Query into a variable, and use that variable instead of hard coding the table name in M code. This way, you won't need to edit M code.

Say you put the table name in F1, and name the cell as TableName. So, the F1 cell contains the table name and it is a named range called "TableName".

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Subject	Score		Source Table	Table2		New table
2	Jack	Maths	84		Subject	Count		Name	Subject	Score
3	Fred	Chem	73		Maths	Jack Mary Eddie Jake		Ted	Maths	20
4	Pete	English	89		Chem	Fred Sally Sara Noel		Ian	English	30
5	Harry	French	88		English	Pete Don Joel		Ken	Maths	40
6	Mary	Maths	60		French	Harry Ken Tom		Mary	English	50
7	Sally	Chem	62					Bart	Maths	60
8	Don	English	79					Bert	English	70
9	Ken	French	91					Herb	Maths	80
10	Eddie	Maths	70
11	Sara	Chem	92
12	Joel	English	75
13	Tom	French	88
14	Jake	Maths	94
15	Noel	Chem	95
Sheet1

And change the M code as below to use the table name from the named range:

let
TableName = Excel.CurrentWorkbook(){[Name="TableName"]}[Content],
Source = Excel.CurrentWorkbook(){[Name=TableName{0}[Column1]]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Subject", type text}, {"Score", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Subject"}, {{"Count", each [Name]}}),
#"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Count", each Text.Combine(List.Transform(_, Text.From), " "), type text})
in
#"Extracted Values"

Change "Table2" to "Table3" in the F1 cell and refresh the resulting table.

Just an idea.

 

[Tigerexcel]

Thanks Smozgur, that is clever but still requires some amendment to M code.

I have been playing with the my original file and the closest I can get is to Duplicate query and then go to the where the file is located, select the table which needs to be grouped, it then performs all the steps except the grouping.