Forum Discussion

Squirrel15's avatar
Squirrel15
Frequent Visitor
5 years ago

Power Query change column with numbers and text into decimal

Hi everyone, 

I am currently working with the App "Scroller" in Power BI. And in order to add data/tables to Scroller they have to be decimals (it doesnt get texts). 

I am working with data like this (+0.23ct), (-0.75bp),(+1.75%) etc. which is classified as text in Query but I want it to be classified as "decimals". 

However, when I change it in Query, I either get an Error or ct and bp get changed into %. 

 

Does anyone know how to solve this? Thank you all in advance! 🙂 

Need Help

5 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    5 years ago

    You have to get rid of the "ct", "bp" parts of the data.  This is just general data cleaning.

    You can do this by 'Replace Value' feature. (right-click the column heading to find that )

    or 'Split column by delimiter' feature (right-click column heading and choose the appropriate option)

    There are lots of ways to do it.

    Then change the data type to decimal

    • Squirrel15's avatar
      Squirrel15
      Frequent Visitor
      5 years ago

      Thanks for your quick reply. However, I want to show the ct and bp together with the numbers. 

      But unfortunately, Scroller is only able the read decimals tables here (I'm adding the table to "Measure Deviation" in Scroller).

      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Squirrel15 ,

         

        How about this:

         

        1. Create two custom column.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jbQMzJOLlGK1YlW0jXQMzdNKgCztQ2BbFWl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"%") then Text.Start([Column1],Text.Length([Column1])-1) else Text.Start([Column1],Text.Length([Column1])-2)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.Contains([Column1],"%") then Text.End([Column1],1) else Text.End([Column1],2)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type number}, {"Custom.1", type text}})
in
    #"Changed Type1"

         

         

        2. Put "Custom.1" column into "Category" field and "Custom" column into "Measure Deviation".

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.