I need to sort a Cryptocurrency table on a field that has numbers entered as Text. How do I do this?

Course

Board Regular
Joined
Aug 7, 2014
Messages
144
I am importing this table https://www.investing.com/crypto/currencies into Excel using Power Query.

The column named Market Cap is entered as Text.

All caps greater than 1 Billion are Large Caps, < 1 billion and > 200 million are mid caps and < 200 million are small caps.

I need to either sort the Table based on the size of the market Cap or create a new column that shows whether the Market Cap field was Large,Mid or Small.

How can i do this using Power Query without affecting the real time data?

Thank you
 
Last edited:
No problem, Ive never had the need to write a billion before:)

It didnt look right in the column so I use google to check the figures

Another error found in code below
if [CapInDollars] <= 2000000 then "Low" else if [CapInDollars] > 2000000 and [CapInDollars] < 1000000000 then "Medium" else "High"

All caps greater than 1 Billion are Large Caps, < 1 billion and > 200 million are mid caps and < 200 million are small caps.
The code should be
if [CapInDollars] <= 200000000 then "Low" else if [CapInDollars] > 200000000 and [CapInDollars] < 1000000000 then "Medium" else "High"
 
Last edited:
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
I'll blame by failing eyesight on that one.:)
 
Upvote 0
If I was actually 'working' with it I'm sure I'd know pretty quickly if I'd missed something out.

To be honest when I posted I was actually working so couldn't give it all my concentration, just tried to get the logic/syntax right which I hopefully did.

PS Did you ever find out how to return more than 100 rows/currencies?
 
Upvote 0
If I was actually 'working' with it I'm sure I'd know pretty quickly if I'd missed something out.

To be honest when I posted I was actually working so couldn't give it all my concentration, just tried to get the logic/syntax right which I hopefully did.

PS Did you ever find out how to return more than 100 rows/currencies?

I was joking. I just checked the other thread now and there are some replies so Ill see do they work. Really appreciate your help with this thread.
 
Upvote 0
I knew were joking, and so was I - I've worked in places where a little typo like that could cost you the job.:)
 
Upvote 0
If I was actually 'working' with it I'm sure I'd know pretty quickly if I'd missed something out.

To be honest when I posted I was actually working so couldn't give it all my concentration, just tried to get the logic/syntax right which I hopefully did.

PS Did you ever find out how to return more than 100 rows/currencies?

I'm having a similar problem, could one of you help me break through this wall? Separate thread link below:

https://www.mrexcel.com/forum/power...-my-rows-100-all-web-queries.html#post4994190
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top