Power Query: Catch Operator from Column and use in Calculated Column

0
votes

I have an Excel-File with some tables to calculate article-prices. In one of these tables, the user can define how to calculate a price.

For example, power query should evaluate then MIN, MAX or AVG price from a count of competitors (this is no problem and works fine). This calculated price is the initial value for further calculations. The User can enter an Operator (+, -, *, /) and an operant. enter image description here

In PQ I combine and transform the table with other tables an get and result:

enter image description here

I want to use the operator in a new calculated column, something like that:

= [Preis_MIN] [Operator] [Wert]

I know, that I can use IF()-Function(s), but if's are very slow, and at the end the table will have thousands of rows.

Is there a way to convert a TEXT in an operator? Or use

Text.Combine({Text.From([Preis_MIN]), [Operator], Text.From([Wert])}, " ") and convert the (Text) result in a formula?

1
excelpowerquerym

1 Answers

1
votes

You can use Expression.Evaluate. eg:

Expression.Evaluate(Number.ToText([Value]) & [operator] & Number.ToText([operand]))

enter image description here