Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Convert text to dates in a column with mixed date types

Hi all,   I'm trying to do time studies using an excel source file with a monthdaytime column populated with data coming from multiple countries.  In the date column, half of the dates are written ...
Need Help
  • natabird3's avatar
    natabird3
    6 years ago
    Spoiler
     

    Is this what you try to achieve? You can use the custom column option and create the rules needed to do the transformation displayed in the tables. Hope this helps. 

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    Do you have another column identify the date column type? For example the date 2/1, If can be first day of february or the second day of january if you do not have any other column can identify the format.

     

    If you do have one, we can create a custom column depends on it easily in Power Query Editor.

     

     

    if [Type] = "MMDD" then Date.FromText([Date]) else Date.FromText([Date],"fr-SN")

     

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi, thanks for your suggestion and apologize for the delayed response.

If I understand your screen shot correctly, I would create an if-then statement for each day, i.e. up to 365 if-thens?

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

We just need to create one if-else formula if you have another column for each rows, such as following tables:

 

IDFormatDate
1MMDD1/2/2019
2DDMM1/2/2019
3DDMM31/1/2019
4MMDD12/1/2019
5MMDD10/1/2019

 

We can create a custom column using formula based on the Format Column. The format column could also be the country or other value that can identify the way you want to format the date.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.