Converting specific text string to date in PowerQuery

[jrduboue]

Hello all,

I have a text column with dates in the current format: "mddyyyy" or "mmddyyyy" or "0" (to indicate no date in the cell). Can I get assistance with how to convert these text strings into date values in the format "mm/dd/yyyy" or "null".

Example: 2012016 to 02/01/2016 and 0 to null or blank

Thank you in advance.

[v-shex-msft · Accepted solution]

Hi @jrduboue,

You can try to use below formula to format the value:

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Start([DateString],Text.Length([DateString])-6)&"/"& Text.Start(Text.End([DateString],6),2)&"/"&Text.End(Text.End([DateString],6),4)),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",0,null,Replacer.ReplaceValue,{"Value"})

Regards,

Xiaoxin Sheng