Forum Discussion
Power Query - Text to Number Error
Can you share some sample data and the M query from the advanced editor?
Looks like there is some record which is unchangeable and hence stopping you from changing the type.
Hi PC2790 !
Thanks for trying to assist
I was unable to load a .xlsx or .csv file
so heres a direct copy&paste (if this even helps) - otherwise just let me know how I can attach this
| product_id | product_brand | product_name | product_sku | product_retail_price | product_cost | product_weight | recyclable | low_fat |
| 1 | Washington | Washington Berry Juice | 9.07E+10 | 2.85 | 0.94 | 8.39 | ||
| 2 | Washington | Washington Mango Drink | 9.65E+10 | 0.74 | 0.26 | 7.42 | 1 | |
| 3 | Washington | Washington Strawberry Drink | 5.84E+10 | 0.83 | 0.4 | 13.1 | 1 | 1 |
| 4 | Washington | Washington Cream Soda | 6.44E+10 | 3.64 | 1.64 | 10.6 | 1 | |
| 5 | Washington | Washington Diet Soda | 8.56E+10 | 2.19 | 0.77 | 6.66 | 1 | |
| 6 | Washington | Washington Cola | 2.98E+10 | 1.15 | 0.37 | 15.8 | ||
| 7 | Washington | Washington Diet Cola | 2.02E+10 | 2.61 | 0.91 | 18 | 1 | |
| 8 | Washington | Washington Orange Juice | 8.98E+10 | 2.59 | 0.8 | 8.97 | 1 | |
| 9 | Washington | Washington Cranberry Juice | 4.94E+10 | 2.42 | 0.77 | 7.14 | ||
| 10 | Washington | Washington Apple Juice | 2.21E+10 | 1.42 | 0.5 | 8.13 | 1 |
advanced editor
let
Source = Csv.Document(File.Contents("C:\Users\Mischa\Downloads\FoodMart+CSV+Files (1)\Product-Lookup.csv"),[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"product_id", Int64.Type}, {"product_brand", type text}, {"product_name", type text}, {"product_sku", Int64.Type}, {"product_retail_price", type text}, {"product_cost", type text}, {"product_weight", type text}, {"recyclable", Int64.Type}, {"low_fat", Int64.Type}})
in
#"Changed Type"
For me it is working fine.
You can try changing it to Decimal number by replacing your code with this:
let
Source = Csv.Document(File.Contents("C:\Users\Mischa\Downloads\FoodMart+CSV+Files (1)\Product-Lookup.csv"),[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"product_id", Int64.Type}, {"product_brand", type text}, {"product_name", type text}, {"product_sku", Int64.Type}, {"product_retail_price", type text}, {"product_cost", type number}, {"product_weight", type text}, {"recyclable", Int64.Type}, {"low_fat", Int64.Type}})
in
#"Changed Type"hi PC2790,
Still no success
unsure why it isn't working, I have tried everything I can think of
you can find the csv in here
https://files.onlineintelligence.co.za/f/55e6066262bf4982a15d/?dl=1
Product-Lookup
Could this perhaps be a settings issue?
Hi Mischa ,
It works fine on my device.
You got this error because your column contents are not only numbers they are alphanumeric so they couldnt totally get converted. Please convert the error column into text and check if there are any columns that are not numbers.Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.