Problem with imported data table

Wallybok

New member
Joined
Oct 22, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2021
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!
 

Attachments

  • Crypto.xlsx
    51.6 KB · Views: 10
Last edited:

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,795
Reaction score
3
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
First, you need a lookup key - your codes do not match those in the lookup table, so you won't get any matches, anyway.
 

Wallybok

New member
Joined
Oct 22, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2021
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!
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,795
Reaction score
3
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
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?
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,170
Reaction score
12
Points
38
Excel Version(s)
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:

Wallybok

New member
Joined
Oct 22, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2021
Thanks



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
 
Top