Forum Discussion

msabrooks's avatar
msabrooks
Copper Contributor
Mar 08, 2019

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 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

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

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    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
      Mar 09, 2019

      Hi Sergei,

       

      Thanks for the help.  That did it.

       

      Thanks again.

      Mike.

    • pqsingh's avatar
      pqsingh
      Copper Contributor
      Jul 07, 2022
      Hi, I am facing some problem with a link, it is a download link. Earlier I was able get data from the excel file under the link but now I am not this error 'Details: "External table is not in the expected format."' And I can not get any data. It would be helpful if anyone can give a workaround.
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jul 07, 2022

        Hi pqsingh 

         

        Your point isn't really related to this thread, it'd be better you raise a separate discussion, if necessary...

         

        Let's be logic: "Earlier I was able get data from the excel file under the link but now I am not". If you didn't make any change to your query it's almost sure the problem doesn't come from the query but from the Data Source - the Excel find "behind" the link

         

        Re. DataFormat.Error: External table is not in the expected format with Excel files there's a quite common reason that i.e. Maxim Zelensky explained here:

        Sometimes that error occured when Excel file is the result of export from a third-party program (accounting system, for example). These files can have a minor errors in XML schema, and they can be easily read by Excel itself, but Power Query raises an error. I met this errors permanently with my accounting system exports. The only way I found to fix it is open in Excel, save and close. After that Power Query has no problem with these files

  • msabrooks's avatar
    msabrooks
    Copper Contributor
    Sep 10, 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
      MVP
      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"

       

       

      • msabrooks's avatar
        msabrooks
        Copper Contributor
        Sep 11, 2019

        SergeiBaklan

         

        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 🙂

Resources