none
Dynamic change of a URL for Query Editor RRS feed

  • Question

  • Hi there,

    I've been searching the forums and have found answers to my question, but I am still not able to successfully implement it to my spreadsheet...

    I am wanting to download historical data from coinmarketcap with the following URL:

    https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180117

    I can do that and get a nice table, but the final date in bold changes every day. I therefore have a cell in my excel document that successfully updates this string each day using the TODAY() function.

    I have placed this into a table, called Table2, and have used the advanced editor within the query editor to produce the following:

    let
        
        Parameter = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        URL= Parameter{0}[Value],
        Source = Web.Page(Web.Contents(URL)),
        Data0 = Source{0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", type number}, {"Market Cap", type number}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Coin", each "Bitcoin")
    in
        #"Added Custom"

    I do not see why this does not work and I receive the following error:

    Expression.Error: The field 'Value' of the record wasn't found.
    Details:
        Parameter=https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180117

    Any help is much appreciated :)

    Shlopps

    Wednesday, January 17, 2018 8:13 PM

Answers

  • Try this:

    Parameter{0}[Column1]

    with the "C" capitalized. As I said before, the name in square brackets, and the name in B6 have to match.

    Ehren

    • Marked as answer by Shlopps Friday, January 19, 2018 11:20 PM
    Friday, January 19, 2018 10:57 PM
    Owner

All replies

  • Hi Shlopps. Which step does the error occur in? From what you pasted, it looks like the issue might be in the URL step. Can you confirm?

    Ehren

    Thursday, January 18, 2018 1:15 AM
    Owner
  • Hi Ehren,

    Yes, it looks like it is, but I have no idea why...

    I would attach the spreadsheet, but am not sure how...

    In my spreadsheet I left click anywhere on my data - go to the ribbon and click on query, then edit followed by advanced editor. In there I get rid of: 

    Source = Web.Page(Web.Contents("https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180117")),

    and replace it with:

        Parameter = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        URL= Parameter{0}[Value],
        Source = Web.Page(Web.Contents(URL)),

    I don't see why it doesn't work - unless it does not read the data in my table as text? However I have formatted the specified cell in the table as text so I don't see why it would...

    Your help would be very much appreciated!

    Thanks again,

    Shlopps


    • Edited by Shlopps Thursday, January 18, 2018 8:11 PM
    Thursday, January 18, 2018 8:06 PM
  • What does your parameter data (i.e. "Table2") look like?

    Ehren

    Thursday, January 18, 2018 8:38 PM
    Owner
  • Ehren,

    I would like to send a screen shot but its saying I need to verify my account which I do not know how to do...

    The URL string is on sheet 1 cell B7. When I click on that cell, it is part of a table where the table column heading is in cell B6. The table is B6:B7 and when i click on design in the ribbon it states: Table Name: Table2.

    S

    Thursday, January 18, 2018 10:51 PM
  • Account verification happens automatically...you'll just need to wait. In the meantime, can you copy/paste the text of the table here? Something like:

    Col1 Col2
    A 1
    B 2
    C 3

    Ehren

    Thursday, January 18, 2018 11:11 PM
    Owner
  • B6 is the column title that just says column1 and has a filter arrow on it. B7 is then the information I need which is: https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180119

    Which comes from the following formula:

    ="https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end="&E11&D11&C11

    Where E11 = 2018, D11= 01 and C11 = 19 - those numbers are all calculated using the Day(), Month() and Year() functions, where each of them refers to a cell that has the Today() function.

    S

    Friday, January 19, 2018 7:43 PM
  • It sounds like your issue is that the column is named column1, but you're trying to get a column called Value.

    This:

    Parameter{0}[Value]

    should be

    Parameter{0}[column1]

    The name has to match the content of cell B6.

    Ehren

    Friday, January 19, 2018 7:47 PM
    Owner
  • It's the cell content in B7 I am after not B6. If I place what you suggested into the code I get the following:

    Expression.Error: The field 'column1' of the record wasn't found.
    Details:
        Column1=https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=20180119

    It is clear that the [Value] is the issue... I'm just not sure what should go in there. If I get rid of it and have the following:

        Parameter = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        URL= Parameter{0},
        Source = Web.Page(Web.Contents(URL)),

    Then I get this error:

    Expression.Error: We cannot convert a value of type Record to type Text.
    Details:
        Value=Record
        Type=Type

    S



    Friday, January 19, 2018 10:54 PM
  • Try this:

    Parameter{0}[Column1]

    with the "C" capitalized. As I said before, the name in square brackets, and the name in B6 have to match.

    Ehren

    • Marked as answer by Shlopps Friday, January 19, 2018 11:20 PM
    Friday, January 19, 2018 10:57 PM
    Owner
  • Brilliant, that worked!

    Thank you very much! So does it work by setting {0} as the first value in the column and then [Column1] setting the first column to look at? I.e. if I had a second column called column 2 and I wanted the second piece of information I would use: Parameter{1}[Column2]?

    Thanks again!

    S

    Friday, January 19, 2018 11:20 PM
  • You're welcome!

    Parameter in your scenario is a table. Using {Index} gives you a particular row (rows are represented as something called "records" in M). Using [FieldName] gives you a particular field of that record.

    So if you had a second column (Column2), you could get its value (assuming the value is also in the first row) by writing:

    Parameter{0}[Column2]

    You can also click on the step where you're extracting the parameter (URL in the above example) and experiment with modifying it.

    Ehren



    Friday, January 19, 2018 11:41 PM
    Owner
  • This is so helpful, thanks.

    I've now got multiple Queries set up for different URLs, each URL getting different sets of data. I then append the two queries together so they all go into one table.

    This is fine if it's just a couple, but I want to do this for hundreds... so it would be best for me to get this all in one bit of code for one Query... Is there anyway I can modify the following to do that?

    let

        Parameter = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

        URL= Parameter{0}[Column1],

        Source = Web.Page(Web.Contents(URL)),

        Data0 = Source{0}[Data],

        #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", type number}, {"Market Cap", type number}}),

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Coin", each "Bitcoin"),

        Parameter1 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

        URL= Parameter1{1}[Column1],

        Source1 = Web.Page(Web.Contents(URL)),

        Data1 = Source1{0}[Data],

        #"Changed Type" = Table.TransformColumnTypes(Data1,{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", type number}, {"Market Cap", type number}}),

        #"Added Custom1" = Table.AddColumn(#"Changed Type", "Coin", each "Ethereum")

    in

        #"Added Custom"

    S

    Saturday, January 20, 2018 6:41 PM
  • Yes, you can turn the above query into a function, which takes the url as an argument. Then iterate over a table of URLs, calling the above function.

    If you do a web search for "Power Query multiple web pages" you'll see many examples of how to do this. Here's one:

    https://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/

    Ehren

    Monday, January 22, 2018 6:35 PM
    Owner
  • Hi Ehren,

    Me again! I've done this and got a function working in the Query Editor. However, when I say save and close and it tries to load the information into Excel I get the following error:

    Container exited unexpectedly with code 0x4000001F. Used features: (none).

    What is more odd though, is that it was at one point stating to enable scripts in explorer by going tot he following link:

    https://support.microsoft.com/en-us/help/3135465/how-to-enable-javascript-in-windows

    When I followed those steps I noticed everything was as it should be though...

    That error randomly went away though after a few attempts at refreshing the info and now the 0x4000001F now comes up as mentioned previously...

    Cheers,

    S

    Wednesday, February 7, 2018 9:27 PM
  • Hi there. Did this issue start occurring after you installed any updates? Also, could you try rebooting your machine, and also try refreshing the Queries on another computer to see if the same error occurs?

    EDIT: Also, if you go to IE's Internet Options -> Advanced tab, are the two "Disable script debugging" checkboxes checked or unchecked?

    Ehren


    Thursday, February 8, 2018 12:18 AM
    Owner
  • Hi Ehren,

    No it has been happening the whole time. I've tried rebooting. I need to try refreshing on a different machine though...

    I just unchecked those two boxes (they were previously checked) and no luck unfortunately...

    Would you like me to post the codes I'm using?

    S

    Thursday, February 8, 2018 9:41 PM
  • Sure, posting the code of your M queries would be very helpful.

    Ehren

    Thursday, February 8, 2018 9:43 PM
    Owner
  • The function is called GetData and is as follows:

    let

        GetData = (Crypto as text) =>

    let

        Parameter = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

        Date_URL= Parameter{0}[Date URL],

        Source = Web.Page(Web.Contents("https://coinmarketcap.com/currencies/" & Crypto & "/historical-data/" & Date_URL)),

        Data0 = Source{0}[Data],

        #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", type number}, {"Market Cap", type number}}),

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Coin", each Crypto)

    in

        #"Added Custom"

    in

        GetData

    Then I have my Query that calls it up:

    let

        Source = Json.Document(Web.Contents("https://api.coinmarketcap.com/v1/ticker/?limit=1500")),

        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Coin"}}),

        #"Expanded Coin" = Table.ExpandRecordColumn(#"Renamed Columns", "Coin", {"id", "name", "symbol", "rank", "price_usd", "price_btc", "24h_volume_usd", "market_cap_usd", "available_supply", "total_supply", "percent_change_1h", "percent_change_24h", "percent_change_7d", "last_updated"}, {"Coin.id", "Coin.name", "Coin.symbol", "Coin.rank", "Coin.price_usd", "Coin.price_btc", "Coin.24h_volume_usd", "Coin.market_cap_usd", "Coin.available_supply", "Coin.total_supply", "Coin.percent_change_1h", "Coin.percent_change_24h", "Coin.percent_change_7d", "Coin.last_updated"}),

        #"Removed Columns" = Table.RemoveColumns(#"Expanded Coin",{"Coin.name", "Coin.symbol", "Coin.rank", "Coin.price_usd", "Coin.price_btc", "Coin.24h_volume_usd", "Coin.market_cap_usd", "Coin.available_supply", "Coin.total_supply", "Coin.percent_change_1h", "Coin.percent_change_24h", "Coin.percent_change_7d", "Coin.last_updated"}),

        #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each GetData([Coin.id])),

        #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date", "Open", "High", "Low", "Close", "Volume", "Market Cap", "Coin"}, {"Custom.Date", "Custom.Open", "Custom.High", "Custom.Low", "Custom.Close", "Custom.Volume", "Custom.Market Cap", "Custom.Coin"})

    in

        #"Expanded Custom"

    I've tried in a new fresh excel file and it's still not happy unfortunately.

    Another key bit of info is that the data in Table 3 is as follows:

    ?start=20170813&end=20180213

    Many thanks,

    S

    Tuesday, February 13, 2018 10:44 PM
  • Hi Shlopps. I was able to recreate your queries, and I'm seeing the same error. Without delving too deeply, I'm guessing that the PQ container process is actually running out of memory (or hitting some other kind of resource limit). The query is doing 1,500 web requests, each of which returns dozens of rows. I was able to get the Query to run successfully by limiting the number of cryptocurrencies (in the step before you invoke the custom function) as well as the date range. I'd recommend starting with a restricted data set and building it up bit by bit to see what works.

    Ehren

    Thursday, February 15, 2018 11:43 PM
    Owner