Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

date format wrongly interpreted in Power BI (not the same as in Excel, the source file)

Hello, I have an issue standardizing the date format. I have the following columns in Excel (the source file).

1. 20181205 (formatted as General in Excel and represents 5 Dec.)

2. 4/12/2018 (formatted as Date in Excel and really is 4 Dec.)

3. 22/11/2018 (formatted as General in Excel and represents 22 November)

 

I cannot change the Excel file. I am wondering how can I standardize this in Power BI, in order to see it as:

 

1. 05/12/2018

2. 4/12/2018

3. 22/11/2018

 

So, I need DD/MM/YYYY format. My locale is set to English (United Kingdom).

 

I have tried to convert to directly to date type but it produces errors.

I also tried to convert it to text and then use Date.FromText() but it also produces errors?

 

Any suggestions?

 

Thanks in advance. 

 

 

 

 

Need Help
  • az38's avatar
    az38
    6 years ago

    Anonymous 

    sorry, there was mistake in my statement.

    try this

    =if 
Text.Contains([Date],"/") 
then 
#date(
Number.From(Text.Range([Date],Text.Length([Date])-4)),
Number.From(Text.Range([Date],Text.PositionOf([Date],"/",Occurrence.First)+1,Text.PositionOf([Date],"/",Occurrence.Last)-Text.PositionOf([Date],"/",Occurrence.First)-1 )),
Number.From(Text.Range([Date],0,Text.PositionOf([Date],"/",Occurrence.First))) 
)
else #date(Number.From(Text.Range([Date],0,4)),Number.From(Text.Range([Date],4,2)),Number.From(Text.Range([Date],6,2)))

    after that go to the Transform ribbon and set Date type as Date

    at this moment it doesn't matter what date format do you see, press close & apply 

    then, in report mode pick your custom column in the Field pane and set Format type as you wish

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    Just saw that you marked a post as solution, and find asking myself why use this whole if-statements, when it would do this

    Date.FromText(Text.From([Date]), "de-DE")

    as stated in my proposal. It should do exactly the same 🙂

     

    Jimmy

18 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    6 years ago

    You may have to do it in 2 parts. 
    duplicate the column

    convert one column to date forcing the errors

    Remove the errors leaving nulls

    parse the other column from text 

    combine the columns in a new custom column

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      So, the first column is the original (the way it was imported from Excel). The third row is formatted as "General" in the source file rather than Date.

      The second column is forced to Date. I expect 4th December in the second row and 22nd November in the third row. 

      The third column is forced to Text and in the 4th is the text extracted. I still get 12th April instead of 4th December. 

       

      I have 900 rows with such a  mess. I would appreaciate a hint now. 

       

      Thank you. 

       

       

       

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Hi Anonymous 

        are there 3 different columns or the only column with different date formats as values?

        do not hesitate to give a kudo to useful posts and mark solutions as solution

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Anonymous 

     

    Do you have any logic to transform the dates? I mean, if in Excel you have a date-format, this is no issue. We can check if the row is a date. But for the remaining one? Are there only two variants? YYYYMMDD and the DD/MM/YYYY or are there also others to identify? If not, here an example of how it works

     

     

    let
    ImportExcel = #table({"Date"}, {{20181205}, {#date(2018,12,4)}, {"22/11/2018"}}),
    TranformToDate = Table.TransformColumns
    (
        ImportExcel,
        {
            {
                "Date",
                (dateint)=>
                if Value.Type(dateint)= type date then
                    dateint 
                    else
                    Date.FromText(Text.From(dateint), "de-DE"),
                type date
            }
        }
    )
in
    TranformToDate

     

     

     

    Copy paste this code to the advanced editor to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy