Forum Discussion

msabrooks's avatar
msabrooks
Copper Contributor
Mar 07, 2019
Solved

Power Query and dynamic URL

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...
BI & Data Analytics
Need Help - Dynamic URL from cell for Power Query from Web
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 08, 2019

    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

msabrooks's avatar
msabrooks
Copper Contributor
Sep 09, 2019

msabrooks 

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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 10, 2019

msabrooks 

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"

 

 

Resources