Apply rule table of multiple string conditions to Data table (issues with List.Contains)

[mh20221111]

Hi all,

I have two tables in Power Query:

Rules: columns Seq (unique number) and Condition (a string that contains a logical M expression).
Data: the dataset I want to classify.

There are multiple rows in Rules. I want to evaluate these conditions against each row in Data, and add the matching Seq value as a new column in Data. If multiple conditions match, picking one value (e.g., the first, minimum, or maximum Seq`) is fine.

I managed to make this work when the conditions only use AND, OR, =, <>, etc. But it breaks when a condition includes List.Contains.

What is the best way to evaluate these string conditions (including List.Contains) against each row of Data and return a single Seq per row?

Rules

[Rufyda]

I'm glad you found a solution! We're here in the community to support each other.

Regards,
Rufyda Rahma | Microsoft MIE

[AlienSx · Accepted solution]

let
    seq_no = List.Buffer(conditions[Sequence No]),
    cond = List.Buffer(List.Transform(conditions[Condition], (x) => Expression.Evaluate("each " & x, #shared))),
    cond_column = Table.AddColumn(
        data, 
        "cond",
        (x) => List.Transform(
            List.PositionOf(
                List.Transform(
                    cond, 
                    (w) => w(x)
                ), 
                true,
                Occurrence.All
            ), 
            (i) => seq_no{i}
        )
    )
in
    cond_column

[mh20221111]

Hi @AlienSx ,

I have an additional question. When I tried the described method on Desktop, it worked fine. Is it possible to perform the same process using Power BI Dataflows?
I understand that dynamic evaluation, such as using Expression.Evaluate or #shared, cannot be used in Dataflows.

Regards,
Miho

[AlienSx]

Hello, @mh20221111 I have no idea, sorry

[mh20221111]

Hi @AlienSx ,

It worked perfectly! Thank you so much for your help.