M and B

[Hamdan1234]

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 their any way ? I have tried doing it with Replace value but did not work. Is there a way to convert it with power query not with DAX?

Thanks

[Icey · Accepted solution]

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.

[AlexisOlson]

You can control the display format in the visual Format pane.

https://powerbidocs.com/2020/09/26/custom-display-units-in-power-bi/