Column with both Mib and Gib values, convert Mib to Gib

[mcflurry]

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.

[v-henryk-mstf · Accepted 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.

[mcflurry]

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:

[Adescrit]

Did you create a measure or a column?

My code was for a column.

[Adescrit]

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
    )

[mcflurry]

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.

[Adescrit]

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.

[mcflurry]

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.

[Adescrit]

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
    )

[mcflurry]

Yep, working, but it displays the same result for every cell:

[mcflurry]

Same result:

An argument of function 'LEFT' has the wrong data type or has an invalid value.

[Adescrit]

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)

[Tahreem24]

@mcflurry , Try this;

Column =

VAR char_ = RIGHT(YourTable[Reclaimable Space],3)

VAR num_ = ABS(LEFT(YourTable[Reclaimable Space],LEN(YourTable[Reclaimable Space])-LEN(char_)))

RETURN IF(char_="GiB", DIVIDE(num_,1024),num_)

[mcflurry]

Hi, not working.

Cannot convert value '' of type Text to type Number.

[Tahreem24]

@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.

[mcflurry]

Yep, working, but it displays the same result for every cell: