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

[Anonymous]

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. 

[az38 · Accepted solution]

@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]

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

[MattAllington]

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]

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]

@Anonymous 

anyway you could try a new custom column

= 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)),1,1)

then choose a date format for this column

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

LinkedIn

[Anonymous]

@az38, your solutions seems to be the closest to what I need. Thank you for the effort. Could you just tell me, how I can make the else part "more loosely"? I need it to recognize that 20181205 is YYYYMMDD and to extract it in the form DD/MM/YYYY.

Thanks a lot.

[Jimmy801 · Accepted solution]

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

[Anonymous]

Well, I just checked it. It really does. Why do you use "de-DE", however? 

[Jimmy801]

Hello

To force power query to use the German standard as all three examples show German standards

Jimmy

[Anonymous]

Thank you, both  @Jimmy801 and  @az38. You were very helpful. 

@Jimmy801, could you provide a link to what date format is specific to which country and how Power BI interprets it? Is there any documentation that can support your statement? I hope my question is not off topic. 

Thank you a lot. 

[Jimmy801]

Hello @Anonymous 

these codes are forcing power query to use the formats of the country indicated. So just check how this country shows dates, times, currencies etc. and you are fine.

Jimmy

[az38 · Accepted solution]

@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

[Anonymous]

So, this is what I get when I apply the syntax, it seems that now I get 4th December but the first row is interpreted in unexpected way. 

[Jimmy801]

Hello @Anonymous 

did you already try my solution? For sure it would be possible to apply the same logic to a new column

Jimmy

[Anonymous]

Hello @Jimmy801, I did. Though I get the solution I want, I see it three times. I also destroys my original dataset I am working with. 

[Anonymous]

By the way,  @Jimmy801. It works only for the sample data I provided. It doesn't work on my original dataset. I just double-checked it. 

[Jimmy801]

Hello

Therefore i asked you if there are any other logics in your column. As i also stated that the logic could also be applied to a new column.

Bye

Jimmy

[az38]

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

[Anonymous]

It's only one.