Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Extract date and specific string from text

Hi I have the following table that contains everything in one cell!!! I want to extract the fist & last date entered in that cell and first & last size in the formate of (00/64"). That 4 columns ...
Need Help
AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
2 years ago

Hi, Anonymous 

let
    Source = your_table,
    delim = {" on ", ". ", ", ", " "},
    col =     
        Table.AddColumn(
            Source, "rec", 
            each 
                [a = 
                    List.Buffer(
                        List.Skip(
                            Splitter.SplitTextByAnyDelimiter(
                                delim, 
                                QuoteStyle.None
                            )([Remark])
                        )
                    ),
                b = 
                    [First Size = a{0}?,
                    First Date = a{1}?,
                    Last Size = a{List.Count(a) - 2}?,
                    Last Date = List.Last(a)]][b]
        ),
    expand = Table.ExpandRecordColumn(col, "rec", {"First Size", "First Date", "Last Size", "Last Date"})
in
    expand