Forum Discussion
Power Query - Text to Number Error
Hello!
I am currently busy with loading a new Query in Power Query.
However, I cannot seem to transform a text column to number
>>
>>
I have tried new column trim and then converting and this still does not work
please help!
thank you kindly
M
9 Replies
Mischa , I think there is still some character, click on the error and check what error you get, and try to address that. Also change to decimal then currency first
Hi amitchandak !
I have tried TRIM then converting to dec number, whole number or currency. All of which render no results.
See error message
I have also tried Clean and then converting to the various number formats
Still no luck 😞
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"
Good Day,
I was strugglng with the same issue today and tried a number of options which did not work.
Use the Parse option on the Transform Tab and choose JSON. It worked pefectly.
