Convert Thousands(K) into Millinons(M)

[Sathya141]

My data have a column(downloads) where the values are shown from "1"(ONLY 0ne) to 99M (99millions) including "K" (thousands) also. I want to convert "k" (thousands) into "M" (millions) OR convert "M" (millions) into "k" (thousands).

Help me to find the solution. Thanks in advance.

Regards,

Sathya Chitte.

[Sathya141]

Thank you for the solution @Anonymous 

[Sathya141]

Thank you for response, but mere replacing doesn't serve the purpose as by replacing, millions(m) will become thousands(k)

[Anonymous · Accepted solution]

Hi @Sathya141 ,

I think you want to replace M by K or K by M and keep the values not change.

I have a test and you may refer to my M code to learn more details.

Whole M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIz9FWK1QEyjAwMvMEsY0MDAwhDzxgiZ6EUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Size", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Move K and M", each if Text.EndsWith([Size],"K") then Text.Replace([Size],"K","") else if Text.EndsWith([Size],"M") then Text.Replace([Size],"M","") 
else [Size]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "String", each if Text.EndsWith([Size],"K") then "K" else if Text.EndsWith([Size],"M") then "M" else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Move K and M", type number}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Convert M to K", each if [String] = "M" then [Move K and M]*1000 else [Move K and M]),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom2", "String", "String - Copy"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"Convert M to K", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2","M","K",Replacer.ReplaceText,{"String - Copy"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"Convert M to K", "String - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Convert M to K.1")
in
    #"Merged Columns"

Best Regards,
Rico Zhou

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

[Sathya141]

in the "Size" column, I want to convert thousands (value in "K") to millions(values in "M"). can you help me to get the output. many thanks

[Anonymous]

Hi @Sathya141 ,

According to your screenshot, I know [Size] column is in TEXT format. If you want to convert "K" by "M" you can try replace function in Power Query Editor.

"Right Click" on [Size] > "Replace Values"

Replace "K" with "M"

Result is as below.

Best Regards,
Rico Zhou

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

[daXtreme]

Too little detail to go on.