topic Re: Convert Thousands(K) into Millinons(M) in DAX Commands and Tips

Convert Thousands(K) into Millinons(M)

Sathya141 — Mon, 11 Apr 2022 15:43:40 GMT

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.

Re: Convert Thousands(K) into Millinons(M)

daXtreme — Mon, 11 Apr 2022 16:14:04 GMT

Too little detail to go on.

Re: Convert Thousands(K) into Millinons(M)

Sathya141 — Wed, 13 Apr 2022 06:17:11 GMT

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

Re: Convert Thousands(K) into Millinons(M)

Anonymous — Thu, 14 Apr 2022 08:05:41 GMT

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.

Re: Convert Thousands(K) into Millinons(M)

Sathya141 — Sat, 16 Apr 2022 10:06:38 GMT

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

Re: Convert Thousands(K) into Millinons(M)

Anonymous — Mon, 18 Apr 2022 07:06:33 GMT

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.

Re: Convert Thousands(K) into Millinons(M)

Sathya141 — Mon, 18 Apr 2022 12:33:23 GMT

Thank you for the solution Anonymous