Forum Discussion

aso1's avatar
aso1
Helper II
6 years ago
Solved

Add text after numbers

Hi.  I want to add a column which adds the text "GB" after a column which has numbers in it.    Basically I want to take 'the colum to the right (TotalPhysicalMemory_GB) and add "GB" to it.  I tr...
Need Help
  • dax's avatar
    dax
    6 years ago

    Hi aso1 , 

    You could try below M code to see whether it work or not.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVMzJVitUBssz0jM3ALCMjPQMDiJipsZ4ZlGVmqWdsDFFqZKhnZGgCZMcCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.ToText([Column1]) & " GB"),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Column1", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1)
in
    #"Added Index"

     

     

    By the way, when you add "GB"  after numbert, the column type will change to text, so it will sort by alphabetically. You need to create an index or rank column, then set it sort by index or rank column, it should work.

    Best Regards,
    Zoe Zhi

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

aso1's avatar
aso1
Helper II
6 years ago

hi Pragati!.. 

thanks. am I doing it right?... I get this error : 

 

Pragati11's avatar
Pragati11
Super User
6 years ago

HI aso1 ,

 

Please chnage Table1 to your table-name. It is an example.

 

Thanks,

Pragati

  • aso1's avatar
    aso1
    Helper II
    6 years ago

    this worked. 

     

     last thing : I would like only two digits after the dot. for example "23.99 GB"

    How can I achieve this?.. 

     

    • aso1's avatar
      aso1
      Helper II
      6 years ago

      also, I cannot sort in order.. it does not work. why is that?

      • dax's avatar
        dax
        Community Support
        6 years ago

        Hi aso1 , 

        You could try below M code to see whether it work or not.

         

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVMzJVitUBssz0jM3ALCMjPQMDiJipsZ4ZlGVmqWdsDFFqZKhnZGgCZMcCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.ToText([Column1]) & " GB"),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Column1", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1)
in
    #"Added Index"

         

         

        By the way, when you add "GB"  after numbert, the column type will change to text, so it will sort by alphabetically. You need to create an index or rank column, then set it sort by index or rank column, it should work.

        Best Regards,
        Zoe Zhi

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

    • Pragati11's avatar
      Pragati11
      Super User
      6 years ago

      Hi aso1 ,

       

      In order to limit number of decimals, you will need to modify your original colum "Total Physical Memory_GB" to have only 2 decimals.

      This can be done under modelling tab.

       

      Thanks,

      Pragati