Dynamic Power Query (web)

[Abdmujib]

I want to use power query to extract a table from the web.

In the picture below, I loaded a table from this website Premier League Form Table
there are multiple websites that has the same table structure and with the same table position, that I will love to switch to without having to go to power query, maybe by pasting the website in the cell B2 and then the table for the particular link load.

so, I paste the link for spain in Cell B2 , the table loads for , spain without having to go to power query to put spain link into it.

pls kindly help. thanks

 

[Alex Blakenburg]

Would something like this work for you ?
Note: A9 has the range name SelectedCountry (used in the PQ datasource)
The country table used in the validation drop down box is called tblCountry
You will still need to hit refresh on the table / query after selecting the country.

let
    WebURL = Excel.CurrentWorkbook(){[Name="SelectedCountry"]}[Content]{0}[Column1],
    Source = Web.Page(Web.Contents(WebURL)),
    Data4 = Source{4}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data4, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Opponents PPG", "Opponents PPG_2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Pts", Int64.Type}, {"GP", Int64.Type}, {"W", Int64.Type}, {"D", Int64.Type}, {"L", Int64.Type}, {"GF", Int64.Type}, {"GA", Int64.Type}, {"GD", Int64.Type}, {"", Int64.Type}})
in
    #"Changed Type"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3	Input Select Country			Column1	_1	GP	W	D	L	GF	GA	GD	Pts
4	Italy			1	Inter Milan	3	3	0	0	8	0	8	9
5				2	AC Milan	3	3	0	0	8	2	6	9
6				3	Juventus	3	2	1	0	6	1	5	7
7				4	Lecce	3	2	1	0	6	3	3	7
8	Concatenated URL Used By Query Range Name SelectedCountry			5	Atalanta	3	2	0	1	6	2	4	6
9	https://www.soccerstats.com/formtable.asp?league=Italy			6	Napoli	3	2	0	1	6	3	3	6
10				7	Hellas Verona	3	2	0	1	4	4	0	6
11	Base URL			8	Fiorentina	3	1	1	1	6	7	-1	4
12	https://www.soccerstats.com/formtable.asp?league=			9	Bologna	3	1	1	1	3	4	-1	4
13				10	Frosinone	3	1	1	1	3	4	-1	4
14				11	Torino	3	1	1	1	2	4	-2	4
15	Country			12	Lazio	3	1	0	2	3	4	-1	3
16	England			13	Sassuolo	3	1	0	2	3	5	-2	3
17	Italy			14	Genoa	3	1	0	2	2	5	-3	3
18	Spain			15	Monza	3	1	0	2	2	5	-3	3
19				16	Salernitana	3	0	2	1	3	5	-2	2
20				17	Udinese	3	0	2	1	1	4	-3	2
21				18	AS Roma	3	0	1	2	4	6	-2	1
22				19	Cagliari	3	0	1	2	1	4	-3	1
23				20	Empoli	3	0	0	3	0	5	-5	0
24
Sheet1

Cell Formulas
Range	Formula
A9	A9	=$A$12&$A$4

Cells with Data Validation
Cell	Allow	Criteria
A4	List	=INDIRECT("tblCountry")

 

[Abdmujib]

Thank you for this.

It will surely work, but the problem I might have with that is that,

1. I might decide to duplicate the sheet into multiple sheets. I am not sure if the each sheet will have it's own macro.

2. I might decide to have two or three tables on a sheet from different link and different table structures, so it might be tedious.


I would have prefer to be able to use Power Query instead if it's possible.

 

[Alex Blakenburg]

I don't understand what you are asking.
There is no macro here the code I gave you is for Power Query.

You asked for:

I will love to switch to without having to go to power query, maybe by pasting the website in the cell B2 and then the table for the particular link load.

Picking a country is the switch and you don't need to go into PQ to after the switch, you just need to refresh the table/query

 

[Abdmujib]

Wow, I thought it was a macro code. Now I realized it is the source code for power query. Thank you so much

 

[Alex Blakenburg]

You're welcome. Glad I could help.

 

[Abdmujib]

Would something like this work for you ?
Note: A9 has the range name SelectedCountry (used in the PQ datasource)
The country table used in the validation drop down box is called tblCountry
You will still need to hit refresh on the table / query after selecting the country.

let
    WebURL = Excel.CurrentWorkbook(){[Name="SelectedCountry"]}[Content]{0}[Column1],
    Source = Web.Page(Web.Contents(WebURL)),
    Data4 = Source{4}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data4, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Opponents PPG", "Opponents PPG_2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Pts", Int64.Type}, {"GP", Int64.Type}, {"W", Int64.Type}, {"D", Int64.Type}, {"L", Int64.Type}, {"GF", Int64.Type}, {"GA", Int64.Type}, {"GD", Int64.Type}, {"", Int64.Type}})
in
    #"Changed Type"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3	Input Select Country			Column1	_1	GP	W	D	L	GF	GA	GD	Pts
4	Italy			1	Inter Milan	3	3	0	0	8	0	8	9
5				2	AC Milan	3	3	0	0	8	2	6	9
6				3	Juventus	3	2	1	0	6	1	5	7
7				4	Lecce	3	2	1	0	6	3	3	7
8	Concatenated URL Used By Query Range Name SelectedCountry			5	Atalanta	3	2	0	1	6	2	4	6
9	https://www.soccerstats.com/formtable.asp?league=Italy			6	Napoli	3	2	0	1	6	3	3	6
10				7	Hellas Verona	3	2	0	1	4	4	0	6
11	Base URL			8	Fiorentina	3	1	1	1	6	7	-1	4
12	https://www.soccerstats.com/formtable.asp?league=			9	Bologna	3	1	1	1	3	4	-1	4
13				10	Frosinone	3	1	1	1	3	4	-1	4
14				11	Torino	3	1	1	1	2	4	-2	4
15	Country			12	Lazio	3	1	0	2	3	4	-1	3
16	England			13	Sassuolo	3	1	0	2	3	5	-2	3
17	Italy			14	Genoa	3	1	0	2	2	5	-3	3
18	Spain			15	Monza	3	1	0	2	2	5	-3	3
19				16	Salernitana	3	0	2	1	3	5	-2	2
20				17	Udinese	3	0	2	1	1	4	-3	2
21				18	AS Roma	3	0	1	2	4	6	-2	1
22				19	Cagliari	3	0	1	2	1	4	-3	1
23				20	Empoli	3	0	0	3	0	5	-5	0
24
Sheet1

Cell Formulas
Range	Formula
A9	A9	=$A$12&$A$4

Cells with Data Validation
Cell	Allow	Criteria
A4	List	=INDIRECT("tblCountry")

I was unable to replicate how you did it. i got the rest except for imported table

 

[Alex Blakenburg]

Here is a link to Google Drive to my test file:

20230910 PQ Source URL from RangeName Abdmujib.xlsx

Data Input Select Country,Column1,_1,GP,W,D,L,GF,GA,GD,Pts Italy,1,Inter Milan,3,3,0,0,8,0,8,9 2,AC Milan,3,3,0,0,8,2,6,9 3,Juventus,3,2,1,0,6,1,5,7 4,Lecce,3,2,1,0,6,3,3,7 Concatenated URL Used By Query Range Name SelectedCountry,5,Atalanta,3,2,0,1,6,2,4,6 <a href="https://www.soccerstats.com/...

docs.google.com

 

[Alex Blakenburg]

If you need it there is an explanation on using a Named Range to provide the Data Source to Power Query here:

Change the Power Query source based on a cell value

Discover how to change the Power Query source based on a cell value. Amazing technique to build simple solutions for others to use.

exceloffthegrid.com

 

[Abdmujib]

Here is a link to Google Drive to my test file:

20230910 PQ Source URL from RangeName Abdmujib.xlsx

Data Input Select Country,Column1,_1,GP,W,D,L,GF,GA,GD,Pts Italy,1,Inter Milan,3,3,0,0,8,0,8,9 2,AC Milan,3,3,0,0,8,2,6,9 3,Juventus,3,2,1,0,6,1,5,7 4,Lecce,3,2,1,0,6,3,3,7 Concatenated URL Used By Query Range Name SelectedCountry,5,Atalanta,3,2,0,1,6,2,4,6 <a href="https://www.soccerstats.com/...

docs.google.com

I duplicated the sheet named "DATA" and I switched the country from the dropdown button and refreshed in order to load for the choosen country but, it didn't effect the changes.