How to convert in M language a DATE(MID(find(mod))) function

[Anonymous]

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!

[Vijay_A_Verma · Accepted solution]

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","")

[Anonymous]

It works like a charm, THANKS A LOT!

[Anonymous]

Sure:

An example of the string: ABCDEF74L71781E, where

74	Year of birth, hence 1974
L	Month of birth, hence July
71	Day of birth, 31 (71-40)

The output is 31/07/1974 so the output is in dd/mm/yyyy format

[Vijay_A_Verma]

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.