Forum Discussion
How to convert in M language a DATE(MID(find(mod))) function
Hi there,
I need you help in converting the folowing Excel function in Power query:
=DATE(MID(B2;7;2);FIND(MID(B2;9;1);"ABCDEHLMPRST");MOD(MID(B2;10;2);40))
This function extracts the date of birth from an alphanumerical code, but i am not sure on how i can implement it on Power Query.
Thanks!
Put below formula in a custom column. After pressing OK, select this custom column, Transform menu - Detect data type to convert 2 digits year to 4 digits year
= Text.Replace(Date.ToText(#date(Number.From(Text.Middle([Data],6,2)),Text.PositionOf("ABCDEHLMPRST",Text.At([Data],8))+1,Number.Mod(Number.From(Text.Middle([Data],9,2)),40))),"00","")
4 Replies
Put below formula in a custom column. After pressing OK, select this custom column, Transform menu - Detect data type to convert 2 digits year to 4 digits year
= Text.Replace(Date.ToText(#date(Number.From(Text.Middle([Data],6,2)),Text.PositionOf("ABCDEHLMPRST",Text.At([Data],8))+1,Number.Mod(Number.From(Text.Middle([Data],9,2)),40))),"00","")Please post the sample strings on which this function operates. Please post as text not as picture. You can press table button in the editor to post table as well.