Forum Discussion
Convert TB to GB
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
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.
4 Replies
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
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 🙂
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.
