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

[Course]

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

 

[Course]

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"

 

[Norie]

I'll blame by failing eyesight on that one.

 

[Course]

I'll blame by failing eyesight on that one.

Can you imagine working with this stuff and leaving out a zero

 

[Norie]

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?

 

[Course]

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.

 

[Norie]

I knew were joking, and so was I - I've worked in places where a little typo like that could cost you the job.

 

[Chikaleen]

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