Log In
Register
Home
New Member
March 25, 2021
Offline
I want to download a lot of data via API (https://fantasy.premierleague......summary/4/). Number 4 in the source is ID. ID is a number from range 1-700 and I want to download all the data. The data is in JSON format so i'm using power query to download it and translate into table. My solution was to create a table in spreadsheet with ID parameter and write a macro to go from 1 to 700 copy-pasting data to another spredsheet.
I successfully declared a variable from the table
let
Źródło = Excel.CurrentWorkbook(){[Name="Tabela2"]}[Content],
ID = Record.Field(Źródło{0}, "Kolumna1")
in
ID
And then I struggle with the query. I declared ID and included it in source (Źródło in Polish). The error I'm getting is that i can't use & for text & number. I tried to convert Par to text using number.totext or text.from but then i receive message that "Import text.from does not match any export".
Can someone help me or get any idea how to do that in other wat?
let
ID = Par,
Źródło = Json.Document(Web.Contents("https://fantasy.premierleague.com/api/element-summary/" & Par & "/")),
history = Źródło[history],
#"Przekonwertowane na tabelę" = Table.FromList(history, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Rozwinięty element Column1" = Table.ExpandRecordColumn(#"Przekonwertowane na tabelę", "Column1", {"element", "fixture", "opponent_team", "total_points", "was_home", "kickoff_time", "team_h_score", "team_a_score", "round", "minutes", "goals_scored", "assists", "clean_sheets", "goals_conceded", "own_goals", "penalties_saved", "penalties_missed", "yellow_cards", "red_cards", "saves", "bonus", "bps", "influence", "creativity", "threat", "ict_index", "value", "transfers_balance", "selected", "transfers_in", "transfers_out"}, {"Column1.element", "Column1.fixture", "Column1.opponent_team", "Column1.total_points", "Column1.was_home", "Column1.kickoff_time", "Column1.team_h_score", "Column1.team_a_score", "Column1.round", "Column1.minutes", "Column1.goals_scored", "Column1.assists", "Column1.clean_sheets", "Column1.goals_conceded", "Column1.own_goals", "Column1.penalties_saved", "Column1.penalties_missed", "Column1.yellow_cards", "Column1.red_cards", "Column1.saves", "Column1.bonus", "Column1.bps", "Column1.influence", "Column1.creativity", "Column1.threat", "Column1.ict_index", "Column1.value", "Column1.transfers_balance", "Column1.selected", "Column1.transfers_in", "Column1.transfers_out"})
in
#"Rozwinięty element Column1"
July 16, 2010
Online
Hi Andrzej,
Welcome to our forum!
You haven't shown how you tried to convert the ID to text, so I can't troubleshoot what went wrong there. My suggestion is: after the source step in the ID query, set the data type for Par to text before converting to a record.
If that doesn't help, please provide a sample file, or at least sample code of what you have tried.
Mynda
October 5, 2010
Offline
Hi Andrzej,
You can do this by creating a list of the ID numbers
ID_List = {1..686}
Any number over 686 caused a 404 Not Found error on the website.
Then using List.Transform, call a function to get the web pages
WebPages = List.Transform(ID_List, each GetPages(_))
This is the GetPages function
GetPages = (ID) =>
let
Pages = Json.Document(Web.Contents("https://fantasy.premierleague.com/api/element-summary/"& Text.From(ID) & "/")),
History = Pages[history]
in History
The full code and about 18,000 rows of results are in the attached Excel workbook.
Regards
Phil
1 Guest(s)
