Converting MB, GB TB to same range with Power Query

[little_alien]

Hey all

I have a tool that is loading in multiple CSV files and running some queries before exporting to a table etc.

As part of this there us a couple of columns that have Either MB - GB or TB values, ie the cell reads 157GB

I Need to level all these out at GB values -

For example -

845.1MB needs to be 0.845
400GB needs to be 400
1.2TB needs to be 1200

Want this to be done as part of the query.

Cheers.

 

[MarcelBeug]

Example query with data from Excel Table1.
This solution features Splitter.SplitTextByPositions using parameter startAtEnd = true, in order to split the text on position 2 from the end.
Unfortunately, this parameter is not mentioned in the linked documentation.

The actual syntax is:

Splitter.SplitTextByPositions(positions as list, optional startAtEnd as nullable logical) as function

Example query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Size", type text}}),
    Convert = 
    each 
    let Split = Splitter.SplitTextByPositions({0,2},true)(_), 
                Factor = {0.001,1,1000}{List.PositionOf({"MB","GB","TB"},Split{1})}, 
                Result = Number.From(Split{0}) * Factor
    in
        Result,
    Converted = Table.TransformColumns(#"Changed Type",{{"Size", Convert, type number}})
in
    Converted

 

[little_alien]

excellent cheers