Expand an Algebraic Expression RRS feed

  • Question

  • A Column contains the following values


    This need to be transformed as below

    A/B - becomes A and B is two seperate rows 

    -(A+B) - becomes one row of -A-B

    -((A/B)+C) becomes two seperate rows -A-C AND -B-C

    Is this possible using power Query - The split to rows on / is simple - but how do you open the brackets  for the last example

    • Edited by Sameer Bhide Wednesday, September 4, 2019 7:13 AM
    Sunday, September 1, 2019 12:01 PM


All replies

  • It sounds like you would need to write a simple expression parser/interpreter in M.


    Tuesday, September 3, 2019 8:08 PM
  • "Simple expression parser/interpreter" 

    Hi Ehern - with my M skills Its going to take me ages :-)

    Could you share some pointers or links - I have not found examples of this anywhere.

    • Edited by Sameer Bhide Wednesday, September 4, 2019 7:11 AM
    Wednesday, September 4, 2019 7:11 AM
  • I'm not aware of any examples of this either, and implementing it for you is beyond the scope of the kind of help we can provide here on the forum. If you take a crack at it I'm sure folks would be happy to help you with your questions, though.


    Monday, September 9, 2019 6:06 PM
  • Thanks Ehern - appreciate your time. Will give it a shot.

    If only we could have a peek into Expression.Evaluate - after all if I replace a,b and c in the above examples with numbers then Expression.Evaluate already parses it correctly.

    So there is a Math Parser already built in - rewriting it is never going to as efficient. 

    Tuesday, September 10, 2019 2:22 AM
  • So there is a Math Parser already built in - rewriting it is never going to as efficient. 

    It's still numeric computation - not symbolic computation that you find in products like Mathcad, or CAS calculators. Your example is only the beginning. You would also have to cater for more complex expressions with all manner of parentheses, minus signs, multiplication, division. Power query is not the tool for this sort of stuff - period.

    I would be interested in understanding what you're doing that requires symbolic computation though.

    Tuesday, September 10, 2019 11:26 AM
  • Hi Colin - my problem is far more complex than what I described in the above example

    It has an ETL part - of extracting a table of Access, doing removing columns, UnPivot, Filter, Text Extraction and Clean + Trim

    Then there is a Math Parser operation on two columns of Two tables and an Anti Join to determine the missing rows.

    All the tasks can be done with with PQ

    One of the Columns contains an algebraic expression with the following Operators

    "+", "-" , "+-" and "/" along with "(" and ")"

    "+" is additive

    "-" is inverse

    +- is XOR and

    "/" is "OR" - confused - bear with me.

    Let's assume A1,A2,A3 are members of a product family, likewise B1,B2 and C1 and C2 and E1 and E2

    Then the expression -(A1/A2)+-(B1-C1)-E1 is to be expanded as below




    Repeat Above but replace A1 with A2 (so there more rows)

    I wish PQ could somehow do this ? do you think it's doable ?

    Thanks in Advance


    Thursday, September 12, 2019 4:06 PM
  • Friday, September 13, 2019 2:38 AM