PQ from web - get URL from a cell that dynamically changes in a template? Any videos?

[AustSportingPix]

I have a template that I use every day.
I rip all my data from google sheets which using import html get its data from 4 or 5 sources.
I have google sheets set up that by entering the date & track everything fetches.

I add the google sheet id to my excel template and get from web.


I’ll love to be able to just copy that Google sheet id into my template and hit refresh and all my data update.
I just can’t find any how to videos on “from web”.

I know bill made one that was for weather and for a heap of days. I’m looking for a single sheet.

There was one a couple of years ago that I now can’t find.

Any idea on where I could find a step by step video?

 

[alansidman]

While this is not an exact means for your specific issue, it will demonstrate how to set up a parameter query that you should be able to adapt.

 

[AustSportingPix]

While this is not an exact means for your specific issue, it will demonstrate how to set up a parameter query that you should be able to adapt.

That’s sort of it.

So imagine if cell L2 has a single item in a table with a URL.

If I update that cell and hit refresh PQ goes out and gets the new info.

I know it can be done with files, but I’ve only ever seen one video and I can’t find it.

 

[jdellasala]

That’s sort of it.

So imagine if cell L2 has a single item in a table with a URL.

If I update that cell and hit refresh PQ goes out and gets the new info.

I know it can be done with files, but I’ve only ever seen one video and I can’t find it.

It works exactly as you need.

Book1
	A	B	C	D	E
1	Base URL:	https://www.mypivots.com/market-holidays/united-states/		Date	Holiday
2	Year:	2022		12/31/2021	New Year's Day
3	Calendar URL:	https://www.mypivots.com/market-holidays/united-states/2022		01/17/2022	Martin Luther King Day
4				02/21/2022	President's Day
5				04/15/2022	Good Friday
6				05/30/2022	Memorial Day
7				07/04/2022	Independence Day
8				09/05/2022	Labor Day
9				11/24/2022	Thanksgiving Day
10				12/26/2022	Christmas Day
Sheet2

Cell Formulas
Range	Formula
B3	B3	=B1&B2

Cells with Data Validation
Cell	Allow	Criteria
B2	List	2020, 2021, 2022, 2023, 2024

Cell B3 is the named range CalendarURL, is built using the Drop Down in B2, and brought into PQ without it having to be converted to an Excel Table:

let
    Source = Excel.CurrentWorkbook(){[Name="CalendarURL"]}[Content],
    Column1 = Source{0}[Column1]
in
    Column1

That could have been done in one line, but this was generated by using Drill Down on the Source. The resulting table is generated here:

let
    Source = Web.Page(Web.Contents(CalendarURL)),
    Data0 = Source{0}[Data],
    ChangedType = Table.TransformColumnTypes(Data0,{{"Date", type date}})
in
    ChangedType

Note that the URL is the variable created by the first query which is named CalendarURL.

 

[AustSportingPix]

Thanks guys

I couldn’t work it out, but found a video and it worked.
Wasn’t for a URL, but gave it a try and bang, it worked.

Now I might be able to sack google sheets