Some values are "£x per month and some are £x per week". How can I use Power Query to remo-CodePudding

I have a column of figures named "Price".

Some of my data are displayed as "£1000 per month", "£500 per month" and some of them are "£120 per week" or "£250 per week" for example.

I want to convert this column using Power Query into a column that returns the weekly value.

For example, "£250 per week" would return "250" and "£1000 per month" would return (1000/4.34813 - a rough conversion to per week) 229.98.

I am using Excel 2016.

CodePudding user response：

One way, in powerquery

right click column and replace values per week with /1

right click column and replace values per month with /4.34813

Add column, custom column with

= Expression.Evaluate(Text.Select([OriginalColumnNameHere],{"0".."9",".","/"}))

That will evaluate the text €1000/4.34 and return 230.41

(That said, no idea why 4.34 is the right number. Youd need to know the number of weeks per month in the month you are currently in using dates)

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,"per week","/1",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","per month","/4.34",Replacer.ReplaceText,{"Column1"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each Expression.Evaluate(Text.Select([Column1],{"0".."9",".","/"})))
in  #"Added Custom"