Problem with imported data table

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!

First, you need a lookup key - your codes do not match those in the lookup table, so you won't get any matches, anyway.

AliGW said:

First, you need a lookup key - your codes do not match those in the lookup table, so you won't get any matches, anyway.

Click to expand...

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!

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?

Change your Table 0 query to include lines to remove the $ symbols and properly convert to decimals (with locale was needed):
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:

Thanks

p45cal said:

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

Click to expand...