Forum Discussion

Danielnir's avatar
Danielnir
Helper II
4 years ago

Extract values from a text, based on condition

Hi,   I have a dataset with item descriptions. Within those, there is information about volumes. How to extract just them?  Sample photo showing how the description works:     As you can ...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Assuming the column name is Data, use below formula in a custom column

= List.Select(Text.Split([Data]," "),(x)=> try Value.Is(Number.From(Text.Split(Text.Replace(Text.Lower(x),"ml","l"),"l"){0}), type number) otherwise false and Text.Split(Text.Replace(Text.Lower(x),"ml","l"),"l"){1}=""){0}

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5JTcwrTlVwS0xOVQhPLM5QMDIwyM1R8A9yVYrViVbySMxLUXAuSk3MVTAFioPFggPcjA0UfPJLMvPzwKIKugpu/kEKvv4uYPlksHIjM3MfhZLyUrCQhamBr48CWEIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Select(Text.Split([Data]," "),(x)=> try Value.Is(Number.From(Text.Split(Text.Replace(Text.Lower(x),"ml","l"),"l"){0}), type number) otherwise false and Text.Split(Text.Replace(Text.Lower(x),"ml","l"),"l"){1}=""){0}, type text)
in
    #"Added Custom"

 

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I'd recommend using List.First instead of {0} to avoid errors if the list is empty.

     

    Here's a slightly simpler version:

    List.First(
    List.Select(
        Text.Split([Data]," "), each
            try Value.Is(Number.From(Text.Remove(Text.Lower(_), {"m","l"})), type number)
            otherwise false
    )
)