Q2Using Powerquery imported data from website to Excel spreadsheet not recognizing numbers

[Doofus1]

Hi Folks,
A day ago, I was shown how to import data from a website to an excel spread sheet.
Thank you, Mr Alansidman.
However, I now have a different issue with that spreadsheet.
The sheet does not appear to recognize the numbers in the cells, in the columns, for me to use with formulas.
I know that I can go to the DATA tab, select TEXT TO COLUMNS, select DELIMITED > NEXT> uncheck the TAB checkbox > NEXT > FINISH and the numbers in a cell become active for me to manipulate in a formula.
However, I need this condition to be permanent. Such that the numbers are active, for me to use with formulas - anytime I enter a new ticker symbol and refresh.
I have uploaded a spreadsheet as an example. See the example formulas in E16 and G17 as examples.
If you type in another stock ticker symbol and refresh, the formula in G17 is likely to produce a false result.
Does anyone know how to make this change permanent so that regardless of the data imported with Powerquery, the numbers in the cells are active for me to manipulate with formulas - without having to go through the steps to make the cells delimited?
Thank you
Doofus.

Be back in around 8hrs

[alansidman]

In the PQ Editor, change the column type to a decimal. It is currently formatted (by default) as a Text because you have two types of numbers in the output. I have done so in the attached.

Time to learn more about manipulating data in PQ. Get this book-->"M is for (Data) Monkey" by Ken Puls and Miguel Escobar. Look for it on Amazon.

[Doofus1]

Hi Alansidman,
Once again, THANK YOU!!!
I have ordered the book on Amazon.com.
It's gonna take a week to get to me.
Best regards
Doofus

I cannot add to your reputation right now because I get this message

You must spread some Reputation around before giving it to alansidman again

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.


You are learning what a great feature this is. You will be amazed at what you can do once you start to work through the book.