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.

natabird3's avatar
natabird3
Icon for Skilled Sharer rankSkilled Sharer
6 years ago

if you can already easily identify which half is month year and which half is year month you can separate the columns and align them properly. Meaning if you load to power bi one column is only month year and the other is only year month you can use the conversions within Power bI like in edit queries in transform tab, you can split column by number of characters and reorder the columns to match the one that works fine and then merge them again. Then combine with the other column as it will be blank for the first half and blank for the second half once merged will be one column with date. Hope this helps. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks very much for your quick reply.  I neglected to mention the source file is live, meaning new data is added to it daily.  The solution you recommended would work for a static file but I'm hoping there's a solution that auto converts the new data coming in into a date format.   

    • natabird3's avatar
      natabird3
      Icon for Skilled Sharer rankSkilled Sharer
      6 years ago

      Because it was saying using an excel source file so i assumed is an excel file. Then you will need to write a calculated column with if statements.

    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      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.

      • rocky_puff's avatar
        rocky_puff
        Frequent Visitor
        4 years ago

        Hi v-lid-msft ,

         

        I'm quite noob with the coding. How to make the "Type" column at Power Query which identifying the date format, either DDmm or mmDD?


        Appreciate your time and understanding. Thanks!