Another Web Scraping Query

[jmillsjmills]

I'm looking at pulling in data from this website:

https://www.betfred.com/sports/event/22986947.2

And it looks as though the embedded call to the data is via this link, which (as per the screenshot) you can see parses nicely in the "Preview" tab when you inspect into the code...

https://www.betfred.com/services/SportsBook/event?language=uk&type=event&eventid=22385490.2&dataflag...

However, when I'm pulling it back, Power Query is determined to treat it as HTML, which it obviously fails to parse. My best attempt at forcing it to treat it as JSON is:

= Json.Document(Web.Contents("https://www.betfred.com/services/SportsBook/event?language=uk&type=event&eventid=22385490.2&dataflag..."))

I feel like it's something to do with needing to force other headers into the query. But PLEASE PLEASE HELP ME.

Thanks so much!

[v-yueyunzh-msft]

Hi , @jmillsjmills 

According to your description, you want your Http request to return a string in JSON format. Right?

First , you can put your request and request parameters into the "Postman" tool for debugging and see if it returns JSON format.
Second, you can try adding in the "Headers"

Accept=application/json 

For how to add "Headers" in Web.Contents , you can refer to :

Web.Contents - PowerQuery M | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jmillsjmills]

Thanks so much for coming back Aniya!

Sorry, I'm not quite following. Are you able to test on your side whether you can pull this data in? The link you've provided exactly the sort of thing I've been playing with but I simply cannot work out how to pull the data.

If you are able to kindly provide what the Power Query code would need to be exactly in this instance, I would be so very grateful!

Thank you so SO much!

[v-yueyunzh-msft]

Hi , @jmillsjmills 

This url is your internal , i cannot test in my workaround, but you can try to use this and replace the url for yours to test .

let
    url = "https://www.betfred.com/services/SportsBook/event?language=uk&type=event&eventid=22385490.2&dataflag...[your url should replace here]",
    headers = [#"Accept"="application/json"],
    response = Web.Contents(
        url,
        [
            Headers = headers
        ]
    ),
    jsonResponse = Json.Document(response)
in
    jsonResponse

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jmillsjmills]

Thanks so much for this Aniya but I'm afraid I'm getting a similar message to before:

It can't seem to read this source code and is insisting that it is HTML.. Do you have any other ideas at all please? Was the above working for you? I inputted https://www.betfred.com/services/SportsBook/event?language=uk&type=event&eventid=22385490.2&dataflag... in place of your URL

Thanks

[v-yueyunzh-msft]

Hi , @jmillsjmills 

For the url , I need to determine if you're using the correct URL.

For URLs, you need to use your URL in "F12" instead of your URL at the top of the browser.

As prompted, your URL returns an HTML format.

You need to make sure that the URL used is the Request URL you showed in the screenshot.

Second, you can also check "F12" to see if there are other "headers" that need to be added.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jmillsjmills]

That's exactly what I am doing yes. When I'm copying and pasting the URL onto here, it seems to be shortening it. But if you click through to the link, that's the full URL I am using. But for whatever reason I'm still getting this same error as shown in yellow on the previous screenshot. I know I am being silly but please spell out how I can get Power Query to process this information as JSON rather than HTML. Do you have this exact URL working on your side please? It clearly provides functional JSON given this is how it previews in the "Inspect" feature on a web browser. But Power Query is determined to treat it as HTML... Thank you so so much for your patience with my slow understanding!

[v-yueyunzh-msft]

Hi , @jmillsjmills 

For the test , it returns the json for this url:

let
    url = "https://www.bing.com/videos/vdasync?w=298&mid=A5DDD429C801C67FCF7CA5DDD429C801C67FCF7C",
    headers = [#"Accept"="application/json"],
    response = Web.Contents(
        url,
        [
            Headers = headers
        ]
    ),
    jsonResponse = Json.Document(response)
in
    jsonResponse

This is return json type , i think you need to check your url and the parameters in your code . For this , you can try to use "postman" to send the request to test if the url return the json type.

Postman API Platform

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jmillsjmills]

Your above code is what I've already tried - and it results in the same error:

DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.

Is there no way you'd be able to please test this specific URL I am trying to use on your side? Thanks so much again

[v-yueyunzh-msft]

Hi ,  @jmillsjmills 

Oh... it is strange , in my side , for this url :

https://www.bing.com/videos/vdasync?w=298&mid=A5DDD429C801C67FCF7CA5DDD429C801C67FCF7C

 it returns the json type:

Is this also an error on your side?

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jmillsjmills]

I get the same as that for your URL example, but my URL example still has the same issue as at the start. Is there no way you would be able to test my URL specifically/directly please? It clearly has something quirkier going on. Is the issue with you accessing this specific URL down to you not being in the UK? Would it be possible to try via VPN somehow? I am so grateful for all your help

[v-yueyunzh-msft]

Hi , @jmillsjmills 

We're sorry, but I don't have access to your internal URLs and can't help you test.

If you are a Power BI Pro licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.

It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support: https://powerbi.microsoft.com/en-us/support/

For how to create a support ticket, please refer to https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/6830...

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jmillsjmills]

Is there anyone please that can directly take a look at this and assess this URL specifically? I can't seem to access Power BI Support - and am wondering if this could be to do with being only free trial on Power BI Pro

[v-yueyunzh-msft]

Hi, @jmillsjmills 

Yes, only Pro license users can apply for a support ticket.

For your question, can you share the url and the “Headers” of this https request to us?Like this :

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jmillsjmills]

Sure! Thanks

I am now working with this one: 

https://www.betfred.com/services/SportsBook/event?language=uk&type=event&eventid=22385578.2&dataflag...

and the headers are:

[v-yueyunzh-msft]

Hi , @jmillsjmills 

Can you try to use this to test?

let
    url = "https://www.betfred.com/services/SportsBook/event?language=uk&type=event&eventid=22385490.2&dataflag...[your url should replace here]",
    headers = [#"accept"="application/json",#"content-type"="application/json",#"referer"="https://www.betfred.com/sports/event/22385490.2",#"user-agent"="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36 Edg/107.0.1418.52",#"cookies"="your cookies"],
    response = Web.Contents(
        url,
        [
            Headers = headers
        ]
    ),
    jsonResponse = Json.Document(response)
in
    jsonResponse

 You need to midify the "url" , "cookies" in this code.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jmillsjmills]

I really appreciate your patience but i'm afraid it's still not working on my end, with the same error. Was that working from your side? 

[v-yueyunzh-msft]

Hi, @jmillsjmills 

This is really strange, for you "F12" screenshot does return data in JSON format. But in PQ it returns HTML, first of all, you can click the "response" to see If there is data returned, and see what the returned data is, whether it is HTML-type data？And can you share the "response" step return in the PQ screenshot?

If the html , can you try to use the "Web.Page(response)" to get the data ?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly