Forum Discussion
Solved
Converting specific text string to date in PowerQuery
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.
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
1 Reply
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