Results 1 to 6 of 6

Thread: Problem with imported data table

  1. #1
    Neophyte Wallybok's Avatar
    Join Date
    Oct 2021
    Posts
    3
    Articles
    0
    Excel Version
    2021

    Problem with imported data table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there! I'm struggling with with a data table I created and I can't seem to get around my issue...
    Basically I pulled from the web a table containing all the cryptocurrencies with their price which will update in real time.
    The price is is USD but I needed to be in Euro.
    To solve this I imported another table with real time exchange rate EUR>USD.
    I just wanted to multiply the USD value of the cryptocurrency table with the exchange rate value obtaining in another column the value in euro.
    It doesn't work...I suspect is because the price value of the cryptocurrency has the $ symbol in front of the number so the formula won't work.
    How do I get around this?
    Attached the file I'm using.

    Thanks in advance!
    Attached Files Attached Files
    Last edited by Wallybok; 2021-10-22 at 12:37 PM.

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,721
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    First, you need a lookup key - your codes do not match those in the lookup table, so you won't get any matches, anyway.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte Wallybok's Avatar
    Join Date
    Oct 2021
    Posts
    3
    Articles
    0
    Excel Version
    2021
    Quote Originally Posted by AliGW View Post
    First, you need a lookup key - your codes do not match those in the lookup table, so you won't get any matches, anyway.
    Hi AliGW thanks for your reply, I'm not really an expert on excel therefore what you said for me makes no sense. Can you explain it to me or make a concrete example on my file? Thank you so much!

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,721
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    For instance, ETH in your working table has no corresponding code in the lookup table. If there is no way to match the data in the tables, then a lookup will never work: how would Excel know which data to return?
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,032
    Articles
    0
    Excel Version
    365
    Change your Table 0 query to include lines to remove the $ symbols and properly convert to decimals (with locale was needed):
    Code:
    let
        Source = Web.Page(Web.Contents("https://cryptoreport.com/all")),
        Data0 = Source{0}[Data],
        #"Removed Columns" = Table.RemoveColumns(Data0,{"#", "", "Change", "Market Cap", "24 Hour Volume"}),
        #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","$","",Replacer.ReplaceText,{"Price"}),
        #"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Price", type number}}, "en-GB")
    in
        #"Changed Type with Locale"
    You say the prices are all in USD then:
    if your new column is going to be part of the existing Table 0 you'll need a formula such as:
    =[@Price]/VLOOKUP("USD",latest_access_key_00ebbe490dd041648c338d2a754f3454,2,0)

    if it's going to be outside that table then:
    =Table_0[@Price]/VLOOKUP("USD",latest_access_key_00ebbe490dd041648c338d2a754f3454,2,0)

    You could do all this inside Power Query in one:
    Code:
    let
        Source = Web.Page(Web.Contents("https://cryptoreport.com/all")),
        Data0 = Source{0}[Data],
        #"Removed Columns" = Table.RemoveColumns(Data0,{"#", "", "Change", "Market Cap", "24 Hour Volume"}),
        #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","$","",Replacer.ReplaceText,{"Price"}),
        #"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Price", type number}}, "en-GB"),
        Source2 = Json.Document(Web.Contents("http://api.exchangeratesapi.io/v1/latest?access_key=00ebbe490dd041648c338d2a754f3454")),
        rates = Source2[rates],
        #"Converted to Table" = Record.ToTable(rates),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Value", type number}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Name] = "USD")),
        XRate = Table.FirstValue(Table.SelectColumns(#"Filtered Rows",{"Value"})),
        AddedEuroPrice = Table.AddColumn(#"Changed Type with Locale", "Euro Price", each [Price] / XRate)
    in
        AddedEuroPrice
    Last edited by p45cal; 2021-10-23 at 12:20 AM.

  6. #6
    Neophyte Wallybok's Avatar
    Join Date
    Oct 2021
    Posts
    3
    Articles
    0
    Excel Version
    2021
    Thanks



    Quote Originally Posted by p45cal View Post
    change your table 0 query to include lines to remove the $ symbols and properly convert to decimals (with locale was needed):
    Code:
    let
        source = web.page(web.contents("https://cryptoreport.com/all")),
        data0 = source{0}[data],
        #"removed columns" = table.removecolumns(data0,{"#", "", "change", "market cap", "24 hour volume"}),
        #"replaced value" = table.replacevalue(#"removed columns","$","",replacer.replacetext,{"price"}),
        #"changed type with locale" = table.transformcolumntypes(#"replaced value", {{"price", type number}}, "en-gb")
    in
        #"changed type with locale"
    you say the prices are all in usd then:
    If your new column is going to be part of the existing table 0 you'll need a formula such as:
    =[@price]/vlookup("usd",latest_access_key_00ebbe490dd041648c338d2a754f3454,2,0)

    if it's going to be outside that table then:
    =table_0[@price]/vlookup("usd",latest_access_key_00ebbe490dd041648c338d2a754f3454,2,0)

    you could do all this inside power query in one:
    Code:
    let
        source = web.page(web.contents("https://cryptoreport.com/all")),
        data0 = source{0}[data],
        #"removed columns" = table.removecolumns(data0,{"#", "", "change", "market cap", "24 hour volume"}),
        #"replaced value" = table.replacevalue(#"removed columns","$","",replacer.replacetext,{"price"}),
        #"changed type with locale" = table.transformcolumntypes(#"replaced value", {{"price", type number}}, "en-gb"),
        source2 = json.document(web.contents("http://api.exchangeratesapi.io/v1/latest?access_key=00ebbe490dd041648c338d2a754f3454")),
        rates = source2[rates],
        #"converted to table" = record.totable(rates),
        #"changed type" = table.transformcolumntypes(#"converted to table",{{"value", type number}}),
        #"filtered rows" = table.selectrows(#"changed type", each ([name] = "usd")),
        xrate = table.firstvalue(table.selectcolumns(#"filtered rows",{"value"})),
        addedeuroprice = table.addcolumn(#"changed type with locale", "euro price", each [price] / xrate)
    in
        addedeuroprice

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •