Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Felicity
New Member

Use written formula from table to determine output in Power Query

I am attempting to write a tool for projecting cost over a series of months for a range of line items, where the item type determines how much cost is taken in which particular month.  Each line could have its own start date, end date and duration (using integer months).  

 

I want to make the tool as future-proof and user friendly as possible, e.g. new formulas for cost treatment can be created or modified without working within the power query module.  So far I've tried creating a table with basic algebra typed out for the current methods with the intention of using Text.Replace to push in the value to per line for variables "s" and "d" within the power query.

 

Felicity_0-1675975129413.png

 

= Table.ReplaceValue( PreviousStep , "d", each [CASH OUT MONTHS], Replacer.ReplaceText, {"% TOTAL COST", "MONTH OF IMPACT"})

 

This works to get a string like "1/10" in the case of cost treatment C, or "4+10/2" for the month of impact for cost treatment A, (using s = 4 and d = 10 as an example) but then I have no way I know of to compute the text formulae within power query to return results of 0.1 and 9 as values.  

 

Does anyone know of a way to execute the formula, or a suggestion of a different approach?

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

Expression.Evaluate can do this job.

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

Expression.Evaluate can do this job.

Perfect, thank you so much!

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.