Convert TB, GB, MB in one format

[zzzsharepoint]

I have a table where I have values in terms of TB , KB MB etc. I want it to convert it in one format say GB. How should I do it. here is the sample column from  table I have:

Value

2 TB

3 GB

12 MB

12 TB

21 GB

321 KB

11

[Anonymous]

Hi @zzzsharepoint ,

In the Power Query Editor, please refer to the following steps.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMlIIcVKK1YlWMlZwhzAMjRR84SyopJEhTNYYyPQGMmMB", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Value = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Value", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", Int64.Type}, {"Value.2", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [Value.2] = "TB" then [Value.1]*1024 else if [Value.2] = "MB" then [Value.1]/1024 else if [Value.2] = "KB" then [Value.1]/1048576 else if [Value.2] = "GB" then [Value.1] else null),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Value", each Number.ToText([Custom]) & "GB"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value.1", "Value.2", "Custom"})
in
    #"Removed Columns"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[djurecicK2 · Accepted solution]

Hi @zzzsharepoint ,

 First, I would separate the numeric value from the unit. So value is 2 and unit is TB.

Then create a new column called Value in GB or something similar.

Adapt the DAX below to your dataset

Value In GB=

var _Value= Table[Valuefield]

var _Unit= Table[Unitfield]

return

SWITCH(TRUE(),

_Unit="TB", _Value*1000,

_Unit="MB", _Value/1000,

etc...)

 Please consider accepting as solution if this has answered the question.