Powerquery extracted Data from website not populating in designated cells

[Doofus1]

Hi Folks,

Re: Vlookup or formula to return a number related to a word that is typed into a cell

Hi Olly or anyone else who understands Powerquery and data extraction from a website.
this spreadsheet was created for me some time ago.
This is a Powerquery spreadsheet onto which I could extract data from the website http://www.tennislive.co.uk/
I used to be able to extract data for any two male and/or female players onto the sheet. (ATP and WTA)
However, it appears to have stopped working for the female players.
It only seems to extract the data for the males..
Anywho, could you take a look at the M code and tell me why it is no longer picking up the data for the females?
Or offer a fix or solution?

How this PQ page is supposed to work using the player profile data on the above mentioned website:

Example of a male player profile page ATP - https://www.tennislive.co.uk/atp/dominic-thiem/
Example of a female player profile page WTA https://www.tennislive.co.uk/wta/timea-babos/

In cell A1 I would type the name of today's surface (or the surface on which I'd like to compare the two players) eg Grass, Clay, Hard, I. Hard (indoor hard), Acrylic, Synthetic etc
In cell A2, I would type the name of a player (Player 1)
In cell A3 I would type the name of another player (Player2).
Then, I would select DATA > REFRESH ALL and the relevant player profile data would populate (from the above mentioned website's player profile page) into cells C2:K3.
The date in column C will be the names of the players (this is a theoretical safeguard to make sure that the data is for the correct player profile).
Column D contains the individual ages of the two players (I don't need the exact birthdate - just the age). (numeric data)
Column E contains the total number of wins. (numeric data)
Column F contains the total number of matches or total number of official games played. (numeric data)
Column G contains the result of the total number of games played minus the total number of games won ie the total number of losses. (numeric data)
Column H contains the player's win percentage on today's surface (based on the surface originally designated in cell A1). (numeric data)
Column I contains the name of the surface on which the player has the highest/best winning percentage.
Column J contains the player's win percentage for the surface on which the player has the highest winning percentage. (numeric data)
Column K contains the total of the total prize money won by that player. (numeric data)
* Nice to have in column L, the player's ATP or WTA ranking number. (numeric data)

If there is anyone out there who is conversant enough with Powerquery fix this or create a new excel sheet on which I can do this, I'd be grateful.

Thank you
Doofus

[AliGW]

In your title, please refer to what the PQ is required to do. Thanks!

[AliGW]

Mmm ... You are not very good at thread titles, are you? Your title should tell us what the problem is - i.e. that you are no longer able to access one of the two pages referenced in the query!

This bit of code:

Please Login or Register  to view this content.

refers to http://www.tennislive.co.uk/atp/ and http://www.tennislive.co.uk/wta/ - these URLs no longer exist.

Instead, they are:

https://www.tennislive.co.uk/atp-men/

and:

https://www.tennislive.co.uk/wta-women/

I would suggest you change these and see what happens. I cannot, as the company firewall will not let me test the query.

[Doofus1]

Hi AiGW,
I hope I have made the change, to the title, as requested.
This was a spreadsheet that Forum Guru/Expert Olly created for me.
I hadn't used it for female players for some time.
But today, when I tried it, it no longer extracted the data for them - onto the excel spreadsheet - and into the designated cells.
I thought it might be because I use Excel 2010 and 2019.
But it did not work on either version.
Thank you
Doofus.

[Doofus1]

Hi AliGW,
Thank you.
Will test that.
Doofus

[Doofus1]

Hi AliGW,
I tried adding those two addresses

Please Login or Register  to view this content.

But that did not seem to work.
I have uploaded a file with the text added.
Will take this up again tomorrow.
Gotta catch some sleep.
Thank you
Doofus

[Doofus1]

Hi Folks,
I have re-edited my post as best I can - to give you more info on what I am trying to achieve.
I hope that there is a Powerquery Wizard out there can fix or help me with getting this right.
Thank you all.

[p45cal]

Not sure; the attached works but sometimes it just hangs. Maybe you'll have better luck.

[Doofus1]

Hi p45cal,
Fantastic!!
That works.
Yes. it does hang. But hey, something is better than nothing.
Now comes the fun part.
What did you do to fix this?
Please feel free to write as much as you want.
Thank you
Doofus

[p45cal]

changed:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

I don't know what I'm doing.

[Doofus1]

Hi p45cal,
Well, it's a damn sight more than I know.
but what does [manualStatusHandling = 404] mean?
Explain as you would - to a toddler.

Also, do you know how to extract the player's ATP or WTA ranking number to the respective cells in Column L?

Thank you again.
Doofus

[p45cal]

"The default behavior can be overridden in your extension using the ManualStatusHandling field"
See:
https://www.tonymcgovern.com/blog/we...rror-handling/
https://docs.microsoft.com/en-us/pow...ingstatuscodes
https://social.technet.microsoft.com...on-power-query

It would be a akin to a toddler explaining to another…

[Doofus1]

Hi p45cal,
You may be a precocious toddler who has learned to count. I am still eating crayons.

I also wanted to ask - do you or anyone else know how to extract the player's ATP or WTA ranking number to the respective cells in Column L?
Or do you or does anyone else know how and/or where to incorporate that query code into the existing M code so that the data can be extracted onto the cells in Column L?

Thank you
Doofus

[p45cal]

maybe this

[Doofus1]

Hi p45cal,
Sorry I did not respond earlier.
Thank you. This works!!
I will go over the code shortly.
I tried adding to your rep but was told I have to spread some rep around etc.
But I thank you for helping me on my Powerquery journey.
Best regards and stay safe.
Doofus.

OK. This works for me. Thank you, again

[Doofus1]

Hi p45cal,
If you have the time, do you mind giving me step by step instructions on how you did that, please?
Thank you
Doofus

[p45cal]

Hi p45cal,
If you have the time, do you mind giving me step by step instructions on how you did that, please?
Thank you
Doofus

I'm sorry Doofus, it would take me far too long.

[Doofus1]

Hi p45cal,
No worries.
Thank you
Best regards
Doofus