Forum Discussion
Lead time format from text to numeric
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.
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.
5 Replies
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
EvaluationStéphane
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.
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.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.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.
