Date columns

[Anonymous]

Hi all, I have a column with date values of the following format: 26-27 Jan, 44313, 25 Oct - 01 Nov and would like to convert this single column into 2 columns, start and end date like the below picture. Thanks in advance!

[AngeloParana · Accepted solution]

First step is to address how data is recorded on the source.  If that's not an option.

On powerquery create an if else statement for each case (maybe length of character as reference of condition) 

Similar to this

if Text.Length([DateRangeColumn])=9 then .... transformation

else if Text.Length([DateRangeColumn])=5 then .....

else .....

[Anonymous · Accepted solution]

Hi @Anonymous ,

I agree with Angelo's insights and have refined the code based on them, please try it:
StartDate

let 
length = Text.Length([Column1]),
vday = Text.BeforeDelimiter([Column1],"-"),
vmonth = Text.AfterDelimiter([Column1]," "),
vdate =  Text.BeforeDelimiter([Column1],"-"),
result =
if length = 5 then Date.AddDays(#date(1899,12,30),Number.FromText([Column1])) 
else if length = 9 then Date.FromText(vday&vmonth)
else if length = 15 then Date.FromText(vdate)
else null 
in 
result

EndDate

let 
length = Text.Length([Column1]),
vday = Text.BetweenDelimiters([Column1],"-"," "),
vmonth = Text.AfterDelimiter([Column1]," "),
vdate =  Text.AfterDelimiter([Column1],"-"),
result =
if length = 5 then Date.AddDays(#date(1899,12,30),Number.FromText([Column1])) 
else if length = 9 then Date.FromText(vday&vmonth)
else if length = 15 then Date.FromText(vdate)
else null 
in 
result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[AngeloParana · Accepted solution]

First step is to address how data is recorded on the source.  If that's not an option.

On powerquery create an if else statement for each case (maybe length of character as reference of condition) 

Similar to this

if Text.Length([DateRangeColumn])=9 then .... transformation

else if Text.Length([DateRangeColumn])=5 then .....

else .....