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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors