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

 

[ranman256]

make an extra column to convert the string field:
=VALUE(A2)

then sort this col.

 

[Course]

make an extra column to convert the string field:
=VALUE(A2)

then sort this col.

The market Cap field is in the E column. Value(E2) doesn't work, it returns error #VALUE !

Btw when you say to add an extra column, do I do this through the edit query or manually enter it. If i manually enter it will it affect the real time query updates

 

[Norie]

If you wanted to get the numerical values for the Market Cap column you could add a custom column with this formula.

= if Text.End([Market Cap],1) = "B" then
100000000 * Number.FromText(Text.Range([Market Cap],1, Text.Length([Market Cap])-2))
else
1000000 * Number.FromText(Text.Range([Market Cap],1, Text.Length([Market Cap])-2))

You would do that in the query editor by selecting Custom Column on the Add Column.

If you want to categorize the Market Cap into Large, Mid and Small this formula could be expanded and/or you could add another custom column.

 

[Course]

If you wanted to get the numerical values for the Market Cap column you could add a custom column with this formula.

= if Text.End([Market Cap],1) = "B" then
100000000 * Number.FromText(Text.Range([Market Cap],1, Text.Length([Market Cap])-2))
else
1000000 * Number.FromText(Text.Range([Market Cap],1, Text.Length([Market Cap])-2))

You would do that in the query editor by selecting Custom Column on the Add Column.

If you want to categorize the Market Cap into Large, Mid and Small this formula could be expanded and/or you could add another custom column.

Thats a great help, Ive added a new column showing the numerical values.

Ive tried to add a second column showing the levels. I tried using these formulas but they dont work

=if([CapInDollars]>=1000000000 then "Large") Else[CapInDollars]<200000000 then "Small" Else "Mid")

=if([CapInDollars]>=1000000000,"Large",If([CapInDollars]<200000000,"Small","Mid"))

Can you help with the code.
Many thanks for helping out

 

[Norie]

I used this to categorise the CapInDollars column.

if [CapInDollars] <= 2000000 then "Low" else if [CapInDollars] > 2000000 and [CapInDollars] < 1000000000 then "Medium" else "High"

 

[Course]

I used this to categorise the CapInDollars column.

if [CapInDollars] <= 2000000 then "Low" else if [CapInDollars] > 2000000 and [CapInDollars] < 1000000000 then "Medium" else "High"

Worked perfectly. Are the formulas for Power Query written like in VBA, they dont seemas intuitive as the formulas for the spreadsheet itself?

Thanks very much for doing up the formulas, you have been a a great help

btw how do I leave you thanks in the icon under your profile on the left?

 

[Course]

If you wanted to get the numerical values for the Market Cap column you could add a custom column with this formula.

= if Text.End([Market Cap],1) = "B" then
100000000 * Number.FromText(Text.Range([Market Cap],1, Text.Length([Market Cap])-2))
else
1000000 * Number.FromText(Text.Range([Market Cap],1, Text.Length([Market Cap])-2))

You would do that in the query editor by selecting Custom Column on the Add Column.

If you want to categorize the Market Cap into Large, Mid and Small this formula could be expanded and/or you could add another custom column.

Just in case anyone uses the formula above, I Forgot to mention that the number for B in the formula above should include another 0. Ive marked it in bold below
if Text.End([Market Cap],1) = "B" then
1000000000 * Number.FromText(Text.Range([Market Cap],1, Text.Length([Market Cap])-2))
else
1000000 * Number.FromText(Text.Range([Market Cap],1, Text.Length([Market Cap])-2))

 

[Norie]

I'm always forgetting how many 0s there should be for billions, should have mentioned I wasn't sure when I posted the formulas.

 

[Course]

I'm always forgetting how many 0s there should be for billions, should have mentioned I wasn't sure when I posted the formulas.

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