Does ID starts with any value from list from column in the same table

[MarcioLeal89]

Hi all,

I need to verify if each row on column "ID" starts with any value from a list from the column "ID starts with" in the same table.

This list will be different for each "Group ID".

I am trying the following but gives an error (Expression.Error: We cannot convert a value of type List to type Text.)

List.AnyTrue( List.Transform({Text.SplitAny([ID Starts With], ",")}, (substring) => Text.Contains([ID], substring)))

I have to do this on the Power Query editor.

Example:

"Group ID"      "ID"                      "ID Starts with"         "Result expected"

A                     TRP123                TRP, PT                          Yes

A                     RUN123               TRP, PT                          No

B                      OL123                 RUN                              No

C                      PT123                 TR, RUN                        No

C                      TR123                 TR, RUN                        Yes

D                     ABC123               ABC , RUN, PT, TR        Yes

Appreciated for your help.

[wdx223_Daniel · Accepted solution]

NewStep=Table.AddColumn(PreviousStepName,"Result Expected",each List.Contains(Text.Split([ID Starts with],","),[ID],(x,y)=>Text.StartsWith(y,x)))

[Anonymous]

This approach worked for me too, thanks for posting.

Can you explain the last part of the step? The "[ID],(x,y)=>Text.StartsWith(y,x)))" part? I've never seen the => in a step, and i dont understand why the x,y before the => switches to y,x (flips order). I assume the x, y are variables?