Lead time format from text to numeric

[Dfarn]

Good morning, 

I am trying to find a way to convert lead time data from what is attached to a numeric value such as the line for 6 months to be displayed as 168 days. exported data is shown below. Any help or ideas would be greatly appreciated. 

[v-veshwara-msft]

Hi @Dfarn ,

Just following up again regarding your issue. If your problem has been resolved, please mark the helpful reply as the Accepted Solution to assist others in the community.

If you still need assistance, feel free to reach out.
Thank you.

[v-veshwara-msft]

Hi @Dfarn ,
We wanted to check in again regarding your issue. If you have found a solution please mark it as 'Accepted solution' to help others benefit. If further assistance is needed please reach out.
Thank you.

[v-veshwara-msft]

Hi @Dfarn ,
Just following-up to check if any of the provided solutions have addressed your needs. If yes, please consider marking the helpful replies as accepted solutions to assist others with similar queries. Or if any other method worked, please share it here to help the community.
If further assistance is needed, please reach out.
Thanks @ronrsnfld , @slorin  for sharing your valuable insights.

Best Regards,
Vinay.

[ronrsnfld · Accepted solution]

You need to supply an algorithm for determining that conversion or use a look up table. For example, you want 6 months to be displayed as 168 days. But various six month periods can have 182-184 days (fewer if you don't count the weekends) so it is hard to make the conversion to 168 days. Both months and years have varying number of days.

[slorin]

Hi @Dfarn 

let
Source = Your_Source,
Replace_Days = Table.ReplaceValue(Source," Day(s)","",Replacer.ReplaceText,{"Lead Time"}),
Replace_Months = Table.ReplaceValue(Replace_Days," Month(s)","*30",Replacer.ReplaceText,{"Lead Time"}),
Replace_Years = Table.ReplaceValue(Replace_Months," Year(s)","*360",Replacer.ReplaceText,{"Lead Time"}),
Replace_Space = Table.ReplaceValue(Replace_Years," ","+",Replacer.ReplaceText,{"Lead Time"}),
Evaluation = Table.TransformColumns(Replace_Space,{{"Lead Time", Expression.Evaluate, type text}})
in
Evaluation

Stéphane