Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone
Trying to start a new query "from web" in excel for the following url
https://www.masters.com/en_US/scores/feeds/scores.json
but it seems to be timing out but all seems ok when I access it on the web.
Anyone have any suggestions?
Thanks
Solved! Go to Solution.
@Anonymous ,
Based on my test, the website you want to visit has added anti-spider system, you should add an "User Agent" session in the web request statement. But unfortunately Web.Contents() doesn't support such kind of level. So power query will return timeout error.
As a workaround, I would suggest you to write a simple python script instead of Web.Contents() like pattern below:
let Source = Table.FromRecords({[Key = 1, Value = 1]}), #"Run Python script" = Python.Execute("# 'dataset' holds the input data for this script#(lf)import requests#(lf)import pandas as pd#(lf)import json#(lf)headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36'}#(lf)json_html = requests.get(""https://www.masters.com/en_US/scores/feeds/scores.json"", headers = headers)#(lf)dataset = pd.read_json(json_html.text)",[dataset = Source]), dataset = #"Run Python script"{[Name="dataset"]}[Value], #"Changed Type1" = Table.TransformColumnTypes(dataset,{{"data", type text}}) in #"Changed Type1"
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The following code has worked in the past but it can't seem to connect to the website now and I don't have the knowledge to figure out what has changed!
Thanks
let Source = Json.Document(Web.Contents("https://www.masters.com/en_US/scores/feeds/scores.json")), data = Source[data], player = data[player], #"Converted to Table" = Table.FromList(player, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "first_name", "last_name", "pos", "teetime", "today", "thru", "topar", "r1", "r2", "r3", "r4"}, {"id", "first_name", "last_name", "pos", "teetime", "today", "thru", "topar", "r1", "r2", "r3", "r4"}), #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Name", each [first_name]&" "&[last_name]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"first_name", "last_name"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "id", "pos", "teetime", "today", "thru", "topar", "r1", "r2", "r3", "r4"}) in #"Reordered Columns"
@Anonymous ,
Based on my test, the website you want to visit has added anti-spider system, you should add an "User Agent" session in the web request statement. But unfortunately Web.Contents() doesn't support such kind of level. So power query will return timeout error.
As a workaround, I would suggest you to write a simple python script instead of Web.Contents() like pattern below:
let Source = Table.FromRecords({[Key = 1, Value = 1]}), #"Run Python script" = Python.Execute("# 'dataset' holds the input data for this script#(lf)import requests#(lf)import pandas as pd#(lf)import json#(lf)headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36'}#(lf)json_html = requests.get(""https://www.masters.com/en_US/scores/feeds/scores.json"", headers = headers)#(lf)dataset = pd.read_json(json_html.text)",[dataset = Source]), dataset = #"Run Python script"{[Name="dataset"]}[Value], #"Changed Type1" = Table.TransformColumnTypes(dataset,{{"data", type text}}) in #"Changed Type1"
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |