Convert TB to GB

[Anonymous]

Please I am very new to Power BI and I am trying to visualize virtual machine data.

I have a column like this 

I would like to convert those few TB roles to GB

What is the simplest way to achieve this? Thanks

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Try this in Power Query Advanced Editor, copy/paste it into a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEw0DMwUHB3UorViVYCE0Z65hYKIRABYxRpYxSOKRLHBL8ppoYw6VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"VM Size" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"VM Size", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([VM Size], "TB") then Text.From(Number.FromText(Text.Start([VM Size] , Text.PositionOf([VM Size]," ")))*1000) & " GB" else [VM Size])
in
    #"Added Custom"

I've multiplied the TB value by 1000 to get GB but strictly speaking it should be multiplied by 1024.

If you want the converted GB values to all have 2 decimal places then in the above query change the string " GB" to ".00 GB"

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Exactly what I needed!

This technology is nothing without the amazing community behind it!

Thanks alot

[Fowmy]

@Anonymous 

You can do it in power query, 

Split the column by delimiter, choose space.

Add a conditional column to check if the new column = TB then the original column multiply by 1000 else same column value.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[AlB]

Hi @Anonymous 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEw0DMwUHB3UorViVYCE0Z65hYKIRABY4R0LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"VM Size" = _t]),

    #"Split Column by Delimiter" = Table.SplitColumn(Source, "VM Size", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"VM Size.1", "VM Size.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"VM Size.1", type number}, {"VM Size.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [VM Size.2] = "TB" then [VM Size.1]*1000 else [VM Size.1], type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "VM Size in GB", each Text.From([Custom]) & " GB", type text)
in
    #"Added Custom1"

 You have one column with the result as number and another one with the result as text. Pick the one you prefer

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers