Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a column like this:
I would lik to convert MiB to GiB, so that everything is in GiB, i have no idea of how to achieve it nor how to search for this... all the info i've found this far is converting from Mib to Gib, but nothing about MiB AND GiB to one of both.
Any help would be much appreciated.
Solved! Go to Solution.
Hi @mcflurry ,
For this need, I think there is a simpler way: use the replace value function in the power query, refer to the following test results:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIzMFVwz3RSitWJVjLUszSHc4AyIE4SRMbMVM/SUMEXptDcUs/cBMKNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","M","G",Replacer.ReplaceText,{"Column1"})
in
#"Replaced Value"
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @mcflurry ,
For this need, I think there is a simpler way: use the replace value function in the power query, refer to the following test results:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIzMFVwz3RSitWJVjLUszSHc4AyIE4SRMbMVM/SUMEXptDcUs/cBMKNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","M","G",Replacer.ReplaceText,{"Column1"})
in
#"Replaced Value"
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ok, both codes are working... didn't worked because i had 2 blank rows in the column.
The problem now is that both codes display the same value in every row, like this:
Did you create a measure or a column?
My code was for a column.
I'm assuming here that your column is a text field e.g. "2.05 GiB" is stored as a text value.
GiB =
VAR __Datatype = RIGHT( 'Table'[Reclaimable Space] , 3)
VAR __Length = LEN( 'Table'[Reclaimable Space] )
VAR __Value = VALUE( LEFT( 'Table'[Reclaimable Space] , __Length - 4) ) -- Return numeric value
RETURN
SWITCH( TRUE(),
__Datatype = "MiB", (__Value / 1024), -- Convert MiB value to GiB
__Datatype = "GiB", __Value -- Return GiB value
)
Yep, it's text value.
I've tried your code and it returns this error:
An argument of function 'LEFT' has the wrong data type or has an invalid value.
Open Power Query (Transform Data) and check that the column Reclaimable space is stored as text, i.e. the data type should be displayed as "ABC" and not "ABC 123". The latter would imply the column has not been explicitly formatted as text. One you have changed type to Text (if needed) close and apply. Then try the formula I previously shared. It works for me.
It's stored as Text.
But it isn't working yet.
An argument of function 'LEFT' has the wrong data type or has an invalid value.
Or try this (once you're sure the column is stored as text)
GiB =
VAR __Datatype = RIGHT( 'Table'[Reclaimable Space] , 3)
VAR __Length = LEN( 'Table'[Reclaimable Space] )
VAR __Value = VALUE( LEFT( 'Table'[Reclaimable Space] , __Length - 4) ) -- Return numeric value
RETURN
SWITCH( TRUE(),
__Datatype = "MiB", (__Value / 1024), -- Convert MiB value to GiB
__Datatype = "GiB", __Value, -- Return GiB value
0
)
Yep, working, but it displays the same result for every cell:
Same result:
An argument of function 'LEFT' has the wrong data type or has an invalid value.
How about this (wrapped in an IFERROR)
GiB =
VAR __Datatype = RIGHT( 'Table'[Reclaimable Space] , 3)
VAR __Length = LEN( 'Table'[Reclaimable Space] )
VAR __Value = VALUE( LEFT( 'Table'[Reclaimable Space] , __Length - 4) ) -- Return numeric value
RETURN
IFERROR(
SWITCH( TRUE(),
__Datatype = "MiB", (__Value / 1024), -- Convert MiB value to GiB
__Datatype = "GiB", __Value, -- Return GiB value
0
) , 0)
@mcflurry , Try this;
Hi, not working.
Cannot convert value '' of type Text to type Number.
@mcflurry Nope syntax is not incorrect. I tested and then shared the code. As per the below screen shot I am not getting any kind of error.
Yep, working, but it displays the same result for every cell:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |