none
Why do I get big numbers converted to strings? RRS feed

  • Question

  • I am trying to import data from the following website: https://coinmarketcap.com/

    Everything gets imported the right way, except for numbers greater than a million. For example, the web page number is $249,588,207,825 but is imported as $249.59 B. I need to have this as numbers since I will perform calculations with them. Is there a way to do so?

    Monday, January 8, 2018 5:05 PM

Answers

  • I'd suggest that you use the public api link instead, as described in the following page:

    https://coinmarketcap.com/api/

    E.g. (using default link)

    let
        Source = Json.Document(Web.Contents("https://api.coinmarketcap.com/v1/ticker/")),
        ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedRecordColumn = Table.ExpandRecordColumn(
                                   ConvertedToTable, 
                                   "Column1", 
                                   {"name", "rank", "price_usd", "24h_volume_usd", "market_cap_usd", "available_supply", "percent_change_24h"}, 
                                   {"Name", "Rank", "Price", "Volume (24h)", "Market Cap", "Circulating Supply", "Change (24h)"}
                                ),
        ChangedType = Table.TransformColumnTypes(
                          ExpandedRecordColumn,
                          {
                            {"Rank", Int64.Type}, 
                            {"Price", Currency.Type}, 
                            {"Volume (24h)", Currency.Type}, 
                            {"Market Cap", Currency.Type}, 
                            {"Circulating Supply", Int64.Type}, 
                            {"Change (24h)", type number}
                          }
                       )
    in
        ChangedType

    • Marked as answer by JavierMoS Tuesday, January 9, 2018 3:27 PM
    Monday, January 8, 2018 11:17 PM

All replies

  • I'd suggest that you use the public api link instead, as described in the following page:

    https://coinmarketcap.com/api/

    E.g. (using default link)

    let
        Source = Json.Document(Web.Contents("https://api.coinmarketcap.com/v1/ticker/")),
        ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedRecordColumn = Table.ExpandRecordColumn(
                                   ConvertedToTable, 
                                   "Column1", 
                                   {"name", "rank", "price_usd", "24h_volume_usd", "market_cap_usd", "available_supply", "percent_change_24h"}, 
                                   {"Name", "Rank", "Price", "Volume (24h)", "Market Cap", "Circulating Supply", "Change (24h)"}
                                ),
        ChangedType = Table.TransformColumnTypes(
                          ExpandedRecordColumn,
                          {
                            {"Rank", Int64.Type}, 
                            {"Price", Currency.Type}, 
                            {"Volume (24h)", Currency.Type}, 
                            {"Market Cap", Currency.Type}, 
                            {"Circulating Supply", Int64.Type}, 
                            {"Change (24h)", type number}
                          }
                       )
    in
        ChangedType

    • Marked as answer by JavierMoS Tuesday, January 9, 2018 3:27 PM
    Monday, January 8, 2018 11:17 PM
  • Thank you so much, it worked perfectlly fine!!
    Tuesday, January 9, 2018 3:27 PM