Forum Discussion

Dfarn's avatar
Dfarn
New Member
1 year ago
Solved

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...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    1 year ago

    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's avatar
slorin
Super User
1 year ago

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