Answered by:
Expand an Algebraic Expression
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 AB
((A/B)+C) becomes two seperate rows AC AND BC
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
Answers

Excel 365 Pro Plus
((A/B)+C) translated into PQ Boolean manually and exercised.
http://www.mediafire.com/file/7wl5rvft7qn3xc4/09_12_19.xlsx/file
http://www.mediafire.com/file/mqngqhzowfxhxk5/09_12_19.pdf/file Proposed as answer by Ehren  MSFTMicrosoft employee, Owner Tuesday, September 24, 2019 9:23 PM
 Marked as answer by Imke FeldmannMVP, Moderator Saturday, October 26, 2019 6:18 AM
All replies


"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

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.
Ehren

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.

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.

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)+(B1C1)E1 is to be expanded as below
A1B1+C2E1
A1B1C1E1
A1+B1+C1E1
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
Sam

Excel 365 Pro Plus
((A/B)+C) translated into PQ Boolean manually and exercised.
http://www.mediafire.com/file/7wl5rvft7qn3xc4/09_12_19.xlsx/file
http://www.mediafire.com/file/mqngqhzowfxhxk5/09_12_19.pdf/file Proposed as answer by Ehren  MSFTMicrosoft employee, Owner Tuesday, September 24, 2019 9:23 PM
 Marked as answer by Imke FeldmannMVP, Moderator Saturday, October 26, 2019 6:18 AM