Forum Discussion

Dee's avatar
Dee
Icon for Helper III rankHelper III
5 years ago
Solved

Transform dates with different formats in a column To Date

I'm importing data from an excel source.

 

From the source, the whole column is in the right date format.

 

Once imported to power Bi, it's formatted as text/int, upon converting I discover some rows are reading error.

 

On my checking, I discover the column has dates in two formarts 

Mon 16/11/20 throws an error

3/15/2021 correctly formats

6/28/2021

Mon 24/05/21

 

Any idea on how I can reformat this on power query to the right date format?

 

TIA

 

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Dee ,

     

    You can complete it in the Power Query Editor or in the Report view.

     

    Sample data:

    Date

    Mon 16/11/20
    3/15/2021
    6/28/2021
    Mon 24/05/21
    Tue 24/11/20
    Mon 23/11/20
    11/25/2020
    11/26/2020
    11/27/2020

     

    In the Power Query Editor

    1.Right click the Date column to select Split Column By Delimiter. Then specify the space as the delimiter.

     

    2.Right click the Date.2 column and select Add as new query. Then change the new query to a table. Remove the blank rows.

     

    3.Change the type as follows.

     

    4.In the orginal table, change the Date column to Date type, then remove errors and remove Date.2 column.

     

    5.Finally, use the Append operation then combine two columns into one.

     

     

    In Report view

    1.Create calculated columns. Then change the Column 3 to Date type.

    Column = 
VAR tt =
    RIGHT ( [Date], 8 )
RETURN
    IF (
        LEN ( [Date] ) > 10,
        DATE ( "20" & RIGHT ( tt, 2 ), LEFT ( RIGHT ( tt, 5 ), 2 ), LEFT ( tt, 2 ) )
    )
    Column 2 = 
IF ( LEN ( [Date] ) <= 10, [Date] )
    Column 3 = 
IF ( ISBLANK ( [Column] ), [Column 2], [Column] )

     

    You can check more details from here.

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

4 Replies

    • Dee's avatar
      Dee
      Icon for Helper III rankHelper III
      5 years ago

      jthomson Is this possible in power query, seems like I can't replace part of a record text.

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    Hi Dee 

    If your source data has dates in multiple formats as shown, they must be stored as text in Excel? I can't think of a way to convert the dates inside PBI.  For example, how does PBI know what date 12/2/2020 is?  Is it Feb 12th or Dec 2nd?

    Best to fix the source data before import.

    Regards

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Dee ,

     

    You can complete it in the Power Query Editor or in the Report view.

     

    Sample data:

    Date

    Mon 16/11/20
    3/15/2021
    6/28/2021
    Mon 24/05/21
    Tue 24/11/20
    Mon 23/11/20
    11/25/2020
    11/26/2020
    11/27/2020

     

    In the Power Query Editor

    1.Right click the Date column to select Split Column By Delimiter. Then specify the space as the delimiter.

     

    2.Right click the Date.2 column and select Add as new query. Then change the new query to a table. Remove the blank rows.

     

    3.Change the type as follows.

     

    4.In the orginal table, change the Date column to Date type, then remove errors and remove Date.2 column.

     

    5.Finally, use the Append operation then combine two columns into one.

     

     

    In Report view

    1.Create calculated columns. Then change the Column 3 to Date type.

    Column = 
VAR tt =
    RIGHT ( [Date], 8 )
RETURN
    IF (
        LEN ( [Date] ) > 10,
        DATE ( "20" & RIGHT ( tt, 2 ), LEFT ( RIGHT ( tt, 5 ), 2 ), LEFT ( tt, 2 ) )
    )
    Column 2 = 
IF ( LEN ( [Date] ) <= 10, [Date] )
    Column 3 = 
IF ( ISBLANK ( [Column] ), [Column 2], [Column] )

     

    You can check more details from here.

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.