Generate a new column containing a list of extracted instances from a given string

[Supervoid]

Hi,

Here is a typical string from my data set:

Input:

"Development of additional interactive tasks on three levels (A, B, C) of complexity for grade 7 in the subject (200101010315) "Chemistry" in Kazakh language 8 lessons, for grade 7 in the subject (200102010315) "Chemistry" in Russian language 8 lessons"

Other strings my have less or more instances, but "x lessons" structure is constant.

I need to extract the number the number of lessons. A new column "Lessons" should contain a list with all instances.

Desired output:

Lessons

8

8

Note:

In my previous task where I needed to extract 12 digit numbers in the same sort of way I used the sollution by @m_dekorte. Maybe it will help:

List.Select( List.Transform( Splitter.SplitTextByWhitespace()( [DATES] ), each try Number.From( Text.Select(_, {"0".."9"})) otherwise null ), (x)=> Text.Length(Text.From( x)) =4 )

Link:

(https://community.fabric.microsoft.com/t5/Power-Query/Extract-only-numbers-of-x-digits-from-a-column...)

THANKS!

[m_dekorte · Accepted solution]

Hi @Supervoid,

See how this works for you

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc2xDoJAEEXRX3mh0oRCMEZbxc7OFihGGGFl2TU7A1G/XrDWmGnfPZPn0ZFHtv7es1P4K6iujRrvyMI45UCVmpGhJJ3AO2gbmGHnSLDYxzjEyJZzWfn+bvlh9ImrD2gC1YztpEwNQ4bLjSvFIl2tks+tk80SRZS13BvR8CyieXuiF3UtLLlmoIaxm36JeCfxfzX9qZ4HEUPuCxuV5Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    AddLessons = Table.AddColumn(Source, "Lessons", each 
        let 
            a = Splitter.SplitTextByWhitespace()([Input]), 
            b = List.PositionOfAny( a, List.Select(a, each Text.Contains(_, "lesson", Comparer.OrdinalIgnoreCase )), Occurrence.All ), 
            c = List.Transform( b, each a{_-1}?) 
        in c, type list 
    )
in
    AddLessons

it returns a column with a list of values

[Supervoid]

Thank you @m_dekorte ! It worked just fine!

[Supervoid]

Hi, @m_dekorte,

Thank you for your reply. I would never come up with anything like this myself 😃

I will try the code tomorrow write a feedback.

Thanks!