Indirect Formula in Power Query

[MikeDBMan]

In Power Query I have a text column I will call "Formula" that contains the text "[Col1] + (([Col2] + [Col3]) *.05)" and I want to evaluate that text cell and make the caldulation that would be [Col1] + (([Col2] + [Col3]) *.05) and return that value in another column in Power Query. Can this be done? Theoretically there would be a different formula in each row of the "Formula" field so each calculation would be different. Thanks for any help!

 

[Matt Allington]

There is a function Expression.Evaluate() that should do what you want. You first must have the calculation in a column as text and then pass that text to this function.

 

[MikeDBMan]

There is a function Expression.Evaluate() that should do what you want. You first must have the calculation in a column as text and then pass that text to this function.

I must be missing something (see attachment). I created a very simple model and the Eval column which contains the Expression.Evaluate just returns the text of the formula, not the results of the formula. What am I doing wrong? Thanks.

 

[JGordon11]

No quotes in the Rules.Formula column and the row context doesn't work inside Expression.Evaluate so you need to replace the column references like below

 

[MikeDBMan]

Unfortunately, none of my formulas are this simplistic. How would I nest things to evaluate multiple fields like this:

[TotalAmount]+[MfgOH]+[MOH]+[Fringe]+[EngLaborOH]) * [GAPRO]

 

[MikeDBMan]

Unfortunately, none of my formulas are this simplistic. How would I nest things to evaluate multiple fields like this:

[TotalAmount]+[MfgOH]+[MOH]+[Fringe]+[EngLaborOH]) * [GAPRO]

And some of the formulas don't have the same number of fields. The above is one formula. In the next formula, it is

If [Category]="Outside Services" then 0 else [TotalAmount])+[MfgOH]+[MOH]+[Fringe]+[EngLaborOH]+[IntG&A]) * [FeeRate]

 

[JGordon11]

This approach might work if your data set is not too big and your rule formulas are just if statements and simple math operators

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    myTextReplace = (headers as list, row as record, txt as text)=>
        List.Accumulate(headers, txt, (s,c)=> Text.Replace(s, "[" & c & "]", Text.From(Record.Field(row,c)))),
    Result = Table.AddColumn(Source, "Eval", each 
        try Expression.Evaluate(myTextReplace(Table.ColumnNames(Source), _, [Rules.Formula]))
        otherwise "Not supported")
in
    Result

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Col1	Col2	Col3	Rules.Formula		Col1	Col2	Col3	Rules.Formula	Eval
2	17	13	23	[Col1]+[Col2]*[Col3]		17	13	23	[Col1]+[Col2]*[Col3]	316
3	7	21	15	Number.Power([Col2],2)		7	21	15	Number.Power([Col2],2)	Not supported
4	7	21	15	[Col2]*[Col2]		7	21	15	[Col2]*[Col2]	441
5	1	22	14	[Col1]-[Col3]		1	22	14	[Col1]-[Col3]	-13
6	11	18	6	[Col1]*[Col2]*[Col3]		11	18	6	[Col1]*[Col2]*[Col3]	1188
7	1	222	3	if [Col1] = 2 then 3 else [Col2]		1	222	3	if [Col1] = 2 then 3 else [Col2]	222
8	3	2	1	if [Col2]-[Col1]>0 then 50 else 22.3		3	2	1	if [Col2]-[Col1]>0 then 50 else 22.3	22.3
9	4	5	6	List.Sum({1,2,3})		4	5	6	List.Sum({1,2,3})	Not supported
10	Will	this	work?	[Col1] & [Col2] & [Col3]		Will	this	work?	[Col1] & [Col2] & [Col3]	Not supported
11
Sheet2

 

[JGordon11]

added #shared as the Expression.Evaluate environment and added quotes to to text values and the previously unsupported formulas work

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    myTextReplace = (headers as list, row as record, txt as text)=> 
        List.Accumulate(headers, txt, (s,c)=> 
            let x = Record.Field(row,c), y = if Value.Is(x, type text) then """" & x & """" else x in  
            Text.Replace(s, "[" & c & "]", Text.From(y))),
    Result = Table.AddColumn(Source, "Eval", each 
        Expression.Evaluate(myTextReplace(Table.ColumnNames(Source), _, [Rules.Formula]), #shared))
in
    Result

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Col1	Col2	Col3	Rules.Formula		Col1	Col2	Col3	Rules.Formula	Eval
2	17	13	23	[Col1]+[Col2]*[Col3]		17	13	23	[Col1]+[Col2]*[Col3]	316
3	7	21	15	Number.Power([Col2],2)		7	21	15	Number.Power([Col2],2)	441
4	7	21	15	[Col2]*[Col2]		7	21	15	[Col2]*[Col2]	441
5	1	22	14	[Col1]-[Col3]		1	22	14	[Col1]-[Col3]	-13
6	11	18	6	[Col1]*[Col2]*[Col3]		11	18	6	[Col1]*[Col2]*[Col3]	1188
7	1	222	3	if [Col1] = 2 then 3 else [Col2]		1	222	3	if [Col1] = 2 then 3 else [Col2]	222
8	3	2	1	if [Col2]-[Col1]>0 then 50 else 22.3		3	2	1	if [Col2]-[Col1]>0 then 50 else 22.3	22.3
9	4	5	6	List.Sum({1,2,3})		4	5	6	List.Sum({1,2,3})	6
10	Will	this	work?	[Col1] & [Col2] & [Col3]		Will	this	work?	[Col1] & [Col2] & [Col3]	Will this work?
11	Will	this	work?	Text.Combine({[Col1],[Col2],[Col3]}, "")		Will	this	work?	Text.Combine({[Col1],[Col2],[Col3]}, "")	Will this work?
12
Sheet2