03-07-2019
09:07 PM
- last edited on
07-12-2019
11:21 AM
by
TechCommunityAP
03-07-2019
09:07 PM
- last edited on
07-12-2019
11:21 AM
by
TechCommunityAP
Hi,
I have been beating my head against the wall for 2 weeks trying to figure out how to get the power query from web to work by updating a dynamic URL by data input from a cell.
Below is the code I attempted to use in order to update web query based on dynamic URL from a cell.
let
Temp= Excel.CurrentWorkbook(){[Name="URL"]}[Content],
URL= Temp{0}[Value],
Source = Json.Document(Web.Contents(URL)),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"assists", "gamesPlayed", "goals", "playerName", "playerPositionCode", "points"}, {"assists", "gamesPlayed", "goals", "playerName", "playerPositionCode", "points"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Column1",{"playerName", "playerPositionCode", "gamesPlayed", "goals", "assists", "points"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"playerName", "Player"}, {"playerPositionCode", "Pos"}, {"gamesPlayed", "GP"}, {"goals", "G"}, {"assists", "A"}, {"points", "Pts"}})
in
#"Renamed Columns"
Note all conversions and renaming columns was done previous to attempting to change the URL.
Any help would be greatly appreciated.
Thanks,
Mike
03-08-2019 08:18 AM
SolutionHi 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
03-08-2019 06:57 PM
Hi Sergei,
Thanks for the help. That did it.
Thanks again.
Mike.
09-09-2019 11:17 PM
Hi, I am trying to take the problem below to a new level.
I am able to create 1 table by adding two queries as shown in example below. How can I do this using a loop or perhaps the List.Generate function to do many queries into 1 table?
This code works:
Index = 1,
Source1 = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/teams/" & Number.ToText(Index) & "/roster")),
roster1 = Source1[roster],
Source2 = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/teams/" & Number.ToText(Index+1) & "/roster")),
roster2 = Source2[roster],
roster = roster1 & roster2,
Thanks in advance,
Mike
09-10-2019 01:58 PM
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"
09-10-2019 07:32 PM - edited 09-10-2019 07:46 PM
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
09-11-2019 03:13 AM
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.