Forum Discussion
Power Query - Text to Number Error
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"