Forum Discussion

Hamdan1234's avatar
Hamdan1234
Helper III
4 years ago
Solved

M and B

I have the Balance Sheet data where it shows the sales  and % in this format 1.36M , 2.4B and 1.2% now I want the Power query to convert it in 1360000,2400000000 and % should remain the same. Is thei...
Need Help
  • Icey's avatar
    Icey
    4 years ago

    Hi Hamdan1234 ,

     

    Two menthods, please check:

     

    #1. Replace value on current column.

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMtQzNvNVitWJVjLSM3ECMwz1jFSVYmMB", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Sales = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Sales", type text}}),
  Custom1 = Table.ReplaceValue(
    #"Changed Type",
    each [Sales],
    each
      if Text.EndsWith([Sales], "M") then
        Text.From(Number.From(Text.Replace([Sales], "M", "")) * 1000000)
      else if Text.EndsWith([Sales], "B") then
        Text.From(Number.From(Text.Replace([Sales], "B", "")) * 1000000000)
      else
        [Sales],
    Replacer.ReplaceText,
    {"Sales"}
  )
in
  Custom1

     

    #2. Create a new column.

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMtQzNvNVitWJVjLSM3ECMwz1jFSVYmMB", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Sales = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Sales", type text}}),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Custom",
    each
      if Text.EndsWith([Sales], "M") then
        Number.From(Text.Replace([Sales], "M", "")) * 1000000
      else if Text.EndsWith([Sales], "B") then
        Number.From(Text.Replace([Sales], "B", "")) * 1000000000
      else
        [Sales]
  )
in
  #"Added Custom"

     

     

    Best Regards,

    Icey

     

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