Forum Discussion

mh20221111's avatar
mh20221111
Icon for Helper II rankHelper II
9 months ago
Solved

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

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

Need Help
  • AlienSx's avatar
    AlienSx
    9 months ago

     

     

     

    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

5 Replies

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    9 months ago

     

     

     

    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's avatar
      mh20221111
      Icon for Helper II rankHelper II
      9 months ago

      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