Forum Discussion

Mischa's avatar
Mischa
Frequent Visitor
4 years ago

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 a...
Need Help
PC2790's avatar
PC2790
Community Champion
4 years ago

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's avatar
    Mischa
    Frequent Visitor
    4 years ago

    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_idproduct_brandproduct_nameproduct_skuproduct_retail_priceproduct_costproduct_weightrecyclablelow_fat
    1WashingtonWashington Berry Juice9.07E+102.850.948.39  
    2WashingtonWashington Mango Drink9.65E+100.740.267.42 1
    3WashingtonWashington Strawberry Drink5.84E+100.830.413.111
    4WashingtonWashington Cream Soda6.44E+103.641.6410.61 
    5WashingtonWashington Diet Soda8.56E+102.190.776.661 
    6WashingtonWashington Cola2.98E+101.150.3715.8  
    7WashingtonWashington Diet Cola2.02E+102.610.91181 
    8WashingtonWashington Orange Juice8.98E+102.590.88.971 
    9WashingtonWashington Cranberry Juice4.94E+102.420.777.14  
    10WashingtonWashington Apple Juice2.21E+101.420.58.131 

    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's avatar
      PC2790
      Community Champion
      4 years ago

      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"