Position of criteria

[Mederic]

Hello everyone,
I want to indicate the position number if one of the criteria in my list is found in my table1 otherwise 0

Thanks in advance

Best regards

Comment

currently under construction

Winding road

Completed on 31/12/2022 after 3 years

Transport on 12/12/2023 - By air

Production on 01/10/22 Made in

Maintenance month 12/23 - under warranty

Contract 11/22 - duration 1 year

Text

/22 -

/2022 -

/2022

/2022

/23

/2023

/2023 -

/2023

[Mederic]

Hello @dufoq3 
Many thanks for this code, which works like a charm,
You're right, it can have 2 positions according to the text.
Otherwise, well done, that's exactly what I wanted the result to be, i.e. the end text after the criteria.
With the position number, I then thought of using the functions: Text.Middle(, Text.PositionOf(), Text.Length()))

But that's no longer necessary, your solution solves everything I wanted in the end
Thank you for your insight

Best regards

[dufoq3]

Youre welcome @Mederic, enjoy 😉

[dufoq3 · Accepted solution]

Hi @Mederic,

have you provided correct output? I've created End of position number column for you which is combination of criteria with end of pos number separated by |. As you can see in row 4 - there are 2 matches.

I don't know why do you need such position, but maybe you just want to extract text after this position. If yes, you can delete all steps except Source and Ad_Extract.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY69CsMwDAZfRXhuSOy8QTsHOhQ6hAzCdltDIgdFofjt65+u4nTfzbOyJ7MnWROc5DyDjXQIn1ZCJLVcZvUM5AK9gSO6erjFbV+9eAeRYNS9Nr0ZjAF8Sf4fIXnko5IPRjr2yFLIjDVyhA6uCTBwhe4cXZsr1JB9Q59tEzoPoSVMGEg8IVkPWyT5FFn1tOYvcl6S9M8jYbQCWhdPB+5krHpd09Sy/AA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Comment = _t]),
    Criteria = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jcyUtBVUIrVATEN0DlITBjbGCGIwtRF5gDZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    CritBuffer = List.Buffer(Criteria[Text]),
    StepBack = Source,
    Ad_EndOfPositionNumber = Table.AddColumn(StepBack, "End of position number", each
        [ a = List.Transform(CritBuffer, (x)=> let a1 = Text.PositionOf([Comment], x), a2 = if a1 = -1 then null else Text.Combine({x, Text.From(a1 + Text.Length(x))}, "| ") in a2),
          b = List.RemoveNulls(a),
          c = Text.Combine(b, "#(lf)")
        ][c], type text ),
    Ad_Extract = Table.AddColumn(Ad_EndOfPositionNumber, "Extract", each
        [ a = Text.Split([Comment], " "),
          b = List.PositionOf(List.Transform(a, (x)=> Text.Contains(x, "/")), true),
          c = Text.Trim(Text.Combine(List.Skip(a, b+1), " "), {" ", "-"})
        ][c], type text)
in
    Ad_Extract