Forum Discussion

nisha_deepak's avatar
nisha_deepak
Helper III
5 years ago
Solved

Comma formatting

I have a field named Flood Details which is text type contains mix  of numbers plus text value like i show below.

 

i want to format number values with comma separated like this example below;

if value is

2,50,000   = 250,000(change to like this)

1,00,000   = 100,000(change to like this)

1000000    = 1,000,000(change to like this).

Above column column conatin mix of type (number + text).I can't format to whole number .

How can i do this ?

Need Help
Show and Tell
Tips and Tricks
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi nisha_deepak 

    Please see this sample PBIX file for this M Code to do what you want.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVMTAwUIrVATJ1TA3gHEMgC8ExAAMw2y+/RME5vyy1KDUFzIdoVVXIzcxTMETSo6sUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [all = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try Number.ToText(Number.From([all]),"N") otherwise [all]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each try if Text.Contains([Custom],".00") then Text.Start([Custom],Text.Length([Custom])-3) else [Custom] otherwise ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

     

     

    Regards

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi nisha_deepak 

    Please see this sample PBIX file for this M Code to do what you want.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVMTAwUIrVATJ1TA3gHEMgC8ExAAMw2y+/RME5vyy1KDUFzIdoVVXIzcxTMETSo6sUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [all = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try Number.ToText(Number.From([all]),"N") otherwise [all]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each try if Text.Contains([Custom],".00") then Text.Start([Custom],Text.Length([Custom])-3) else [Custom] otherwise ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

     

     

    Regards

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

    • nisha_deepak's avatar
      nisha_deepak
      Helper III
      5 years ago

      I need to change numbers only to format with commas...

      example value from flood details like 

      2,50,000   = 250,000(change to like this)

      1,00,000   = 100,000(change to like this)

      1000000    = 1,000,000(change to like this).

       

      I want to put the value like 2% min 100,000 this as itself in column.Only this type of values to be changed from 2,50,000 to 250,000. Is it possible?plse help...