Question-> Excel 2016 New Query -> From Other Sources -> From Web

[pianohohn]

Hello,
Using Excel 2016 New Query -> From Other Sources -> From Web -> Stock Quote FF FutureFuel Corp. -> Table 0-> 12 rows loaded.

The data comes in beautifully, but the problem is it all shows as "Text" data type, so when I pull in the data to a separate sheet using a simple vlookup, then from the same separate sheet and different cell do an "IF" statement to the vlookup value, the vlookup value isn't seen as a decimal number or a percentage number, whatever the case may be. OK,.. I see I can edit the web query's data type on a case by case basis to be something other than text (it for some reason forces the entire column to be decimal number rather than just one cell i am focusing on and then percentage values in the same column are turned into errors). Not sure why this is so difficult? Why can't the values pulled from the web be their true value and type? Need advice please, thanks.

 

[pvr928]

Hi I've got Excel 2013 so not entirely sure of how Excel 2016 'looks' - but are you using "Get and Transform Data" (ie Power Query in Excel 2013 and 2010)?

If so, when you're in the editor, you can change the data type, including to numbers. Once you've done that and loaded the query to Excel the numbers will be formatted and recognised as numbers, and not text.

Have fun!

cheers

pvr928

 

[pianohohn]

Yes thankyou for the response kindly,... I understand that,.. but my burning question is the data type changer doesn't seem to be able to shape or transform a text value of let's say "10%", I see options of decimal number & whole number but choosing either then show the 10% text value as "error" during data type change within the query editor.
Using Excel 2016 New Query (formerly "Power Query" I guess-> From Other Sources -> From Web -> Stock Quote FF FutureFuel Corp. -> Table 0-> 12 rows loaded.
The table there starting with index and so on is great but when attempting to transform the % text values to something excel can read as a number percentage is proving challenging, can someone test and see if there is something I'm missing?

 

[pianohohn]

DataFormat.Error: We couldn't convert to Number.
Details:
3.57%