Power query again - multiple files from a website

[GorD]

Hi there another question as i try and learn a bit about PQ.

This relates to the howdidido website - which many golf clubs (uk at least) use to show competition results.

Power query web options seem to have improved a lot recently, and i can now quite easily get the results from a single competition, loaded to a spreadsheet.
let
Source = Web.Page(Web.Contents("http://www.howdidido.co.uk/ClubDetails.aspx?section=24&pagesection=compresultsdetails&cid=5102&compid=784")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Player", type text}, {"Score", type text}, {"New Exact Handicap", type text}, {"Comments", type text}})
in
#"Changed Type"

works well

But i would like to load up all the competitions for the year, which seems to be controlled by the compid number

Does this sound possible

 

[Comfy]

Hi GorD,

I think it's possible but because my PQ knowledge is very limited (learning as I go) and you can't access the website without logging in I can only provide a limited answer.

Setup your Query to extract one compid, then turn that query into a Function.

Here's my original query

let
Source = Web.Page(Web.Contents("http://www.howdidido.co.uk/ClubDetails.aspx?section=24&pagesection=compresultsdetails&cid=5102&compid=784")),
Data0 = Source{0}[Data]
in
Data0

And then added the parts in bold.

let GetDetails=(cID) =>
let
Source = Web.Page(Web.Contents("http://www.howdidido.co.uk/ClubDetails.aspx?section=24&pagesection=compresultsdetails&cid=5102&compid="&Number.ToText(cID))),
Data0 = Source{0}[Data]
in
Data0
in GetDetails

In the Query Pane I renamed the Query to GetData.

Next in a sheet of my workbook I created a simple table listing all the cID I wanted to return data for:

Excel 2010

	A	B
1	cID
2	1
3	2
4	3
5	4
6	784

<tbody>
</tbody>

Sheet1​

And use the "From Table" PQ function to import the table into PQ.

Next add a custom column to this table, I called mine "Website Data" and the code for the column is:

=GetData([cID])

You should then see two columns in your table, in the "Website Data" header you should see a button with two opposite facing arrows. click that and select the columns you want to return from the query, you'll have to play around with this part because I can't go any further.

 

[GorD]

Wow thanks so much for trying and i have learned some things there. Like you i'm just trying to teach myself a bit of this , with my M is for Data Monkey book in hand.

I got so far, but i can't quite get the appropriate results to be returned an i realise this is at the point where a log in is required. I am for every chasing the double headed arrrows with the expand childeren, but don't get to th eactual results.

This was as far as i ever got a few months ago, but when i tried recently, suddenly all the results were available- i assumed this was down to Pq improvments

Thanks for trying again

GorD

 

[Comfy]

Hi GorD,

I think the "double headed arrow" chasing is due to the following:

* PQ cannot find any tables to Parse
* Because of this it returns the whole Document and your drilling down through all the elements of the website.

Can you try something?

Open the website in a browser and login.

Then try to import data into PQ, are you able to select a table of results and can you use my function to return multiple tables?

I think one of the issues is how their website is built, it looks as though it's generated using Javascript, the problem with this is that PQ cannot reliably identify when the website has finished loading and so might be snap shotting the website too early??

 

[GorD]

The initial query in post 1 returned a full set of results for that competition - they appeared in a table in the preview and then could be loaded. However if i re-try the same thing now i am not getting results - just the -document which then displays kind, type, children etc - so i am now at a loss to why it worked earlier this week .

 

[Comfy]

Do you have a web broswer open and an active session with the website?

It's possible that you did when first creating the query as a reference.

 

[GorD]

I thought it might have been that too, but no, makes no difference - either open or closed - makes no sense