Extract Text

[Anonymous]

Hi, I have few Product descriptions with me eg:

HP 16GB (1X16GB) 2RX4 PC3L-12800R
HP 16GB(1X16GB) 2RX4 PC3L-12800R
HPE 16GB 1RX4 PC4-2400T-R DIMM
16GB PC4-2933 DUAL-RANK X8 DDR4
8GB PC4-2933 SINGLE-RANK X8 DDR4
HPE 32GB DUAL RANK X4 DDR4-2933 DIM
64GB PC4-2666 V-R, REG SDRAM 2G X4
HP 32GB (1X32GB) 4RX4 PC3L-10600L 8.

I want to extract size of memory from these, 32gb, 64gb and 16gb should be the result in these cases.

Thanks

Priyanshu

[andre]

not sure if you are trying to implement a column of a measure, but this is the pseudocode to extract memory from your strings:

Memory =

-- replace the string "HP 16GB (1X16GB) 2RX4 PC3L-12800R" with your column name or selectedvalue of that column

var GBStart = SEARCH("GB", "HP 16GB (1X16GB) 2RX4 PC3L-12800R", , 0)

return IF(GBStart = 0, BLANK(), MID("HP 16GB (1X16GB) 2RX4 PC3L-12800R", GBStart-2, 4))

[lbendlin]

Here is a basic version without any error checking. Add that as needed.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY/LCsIwFER/ZejKQgN5EeKymhKLaSm3KoHS//8Nm8Sq4MLVLM65c5llqS4ThPEnHERMWUNS1JjOKjAhLedUrc3b+id1pUsUqpnUnN8YwfXDkJWMMzkqBXdvA6N2vCJaOEc6O/ZbmfvRh+5XSr+U3MzUgYJ1xq/qvjw0em8zxuDBqAF1HrOjdoD029G+L7dtA1PW0J+B3HAeYKt1fQI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each 
    let g = Text.PositionOf([Column1],"GB"),
    sp = Text.PositionOf([Column1]," "),
    st = if sp<g then Text.Range([Column1],sp+1,g-sp+1) else Text.Start([Column1],g+2) 
in 
    st
)
in
    #"Added Custom"

[az38]

@Anonymous 

For DAX you can use calculated column

Memory Column = 
VAR GbStart = SEARCH("GB", [Column1])
VAR PrevSpace = SEARCH(" ", [Column1]) + 1
VAR StartPosition = IF(PrevSpace > GbStart, 1, PrevSpace)
VAR TextLen = GbStart - StartPosition + 2
RETURN
IF(StartPosition = 0, LEFT([Column1], TextLen), MID([Column1], StartPosition, TextLen ))

[mahoneypat]

You can add a custom column in the query editor with this formula.  It worked with your sample data (replace [TextColumn] with your actual column name), but you may have to adapt it, if there are scenarios not covered.

= List.First(List.Transform(List.Select(Text.Split([TextColumn], " "), each Text.Contains(_, "GB")), each Text.BeforeDelimiter(_, "GB") & "GB"), ", ")

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous · Accepted solution]

power Query, add Conditional column 

[Anonymous]

Hi Karlos,

Thanks for this solution, it worked like a charm. Only challenge I am facing here is the font variation, I observed that there are few descriptions which have 16Gb, 16 GB, 16Gb and 16gb as memory size. For now i have added all of these scenarios as condition in column but it would be halpful if i can do the same thing immaterialistic of font and space.

Thanks

[Anonymous]

Hey Dude, 

You could go to Transform, format, CAPITALISE. This would make everything in that column uppercase, then I guess you'd just need to add additional rules to account for the spaces.  

= Table.AddColumn(#"Changed Type", "Custom",

each if Text.Contains([Column1], "16GB") then "16GB"

else if Text.Contains([Column1], "16 GB") then "16GB"

else if Text.Contains([Column1], "32GB") then "32GB"

else if Text.Contains([Column1], "32 GB") then "32GB"

else if Text.Contains([Column1], "64GB") then "64GB"

else if Text.Contains([Column1], "64 GB") then "64GB"

else null)

[harshnathani]

Hi @Anonymous 

You can use find and replace for making all the GB's in Capital.

Once all are Capital GB

Then find 16 GB and replace it with 16GB.

Regards,

Harsh Nathani