Forum Discussion
Power Query and dynamic URL
Hi Mike,
Sorry, but I didn't catch what exactly doesn't work. Based on your code you have in your sheet table URL with the column named Value from first row of which you take your url as parameter.
For only one cell which you use as parameter I'd not use table, just name that cell (let say as URL) and get parameter as
... GetParameter = Excel.CurrentWorkbook(){[Name="URL"]}[Content], URL = GetParameter[Column1]{0}, ...but in general that's only cosmetic
Hi Mike,
As variant you may create list of Indexes, convert it to table, add custom column to connect JSON pages for each index, after that sequentially combine/expand result. Generated script is
let
PagesIndex = {1..7},
#"Converted to Table" = Table.FromList(
PagesIndex,
Splitter.SplitByNothing(), null, null, ExtraValues.Error
),
#"Renamed Columns" = Table.RenameColumns(
#"Converted to Table",
{{"Column1", "Index"}}
),
#"Added Custom" = Table.AddColumn(
#"Renamed Columns",
"Custom",
each Json.Document(
Web.Contents("https://statsapi.web.nhl.com/api/v1/teams/" & Number.ToText([Index]) & "/roster")
)
),
#"Expanded Custom" = Table.ExpandRecordColumn(
#"Added Custom",
"Custom",
{"roster"}, {"roster"}
),
#"Expanded roster" = Table.ExpandListColumn(
#"Expanded Custom",
"roster"),
#"Expanded roster1" = Table.ExpandRecordColumn(
#"Expanded roster",
"roster",
{"person", "jerseyNumber", "position"},
{"person", "jerseyNumber", "position"}
),
#"Expanded person" = Table.ExpandRecordColumn(
#"Expanded roster1",
"person",
{"id", "fullName", "link"},
{"id", "fullName", "link"}
),
#"Expanded position" = Table.ExpandRecordColumn(
#"Expanded person",
"position",
{"code", "name", "type", "abbreviation"},
{"code", "name", "type", "abbreviation"}
)
in
#"Expanded position"
Hi Sergei,
Thanks so much. I wish I knew how to derive that on my own. It works like a charm.
I modified your list of 1..7 to 1..30 and noticed there are a few bad points as there is no data for items such as 11, 27 etc.
Is it possible to pull in an array from excel rather than do the following:
PagesIndex = {1..10, 12..26, 28..30}
I tried something like this and it did not work:
GetParameter = Excel.CurrentWorkbook(){[Name="Index"]}[Content],
Index = GetParameter[Column1]{..},
If you know a quick way around that it helps from the manual building of PagesIndex.
Thanks again. Its much appreciated.
Mike.
Few more tries and I got it.
GetParameter = Excel.CurrentWorkbook(){[Name="Teams"]}[Content],
PagesIndex = GetParameter[ID],
Thanks again.
Works beautifully 🙂
Hi Mike,
In the middle, after adding the column with connections to JSON pages, you may insert one more step to remove records with errors which indicate that related page was not found
Please check in attached.