Power Query - Text to Number Error

[Mischa]

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

[LungisGran]

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.

[PC2790]

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.

[Mischa]

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"

[PC2790]

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"

[Mischa]

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?

[v-stephen-msft]

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.

[amitchandak]

@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

[Mischa]

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 😞