Date format issue in Power Bi Desktop in UK - some dates get converted to US format.

[MarkH]

Here is the source data.

Just Source = DATATABLE (

    "StartDate", DATETIME,

    "EndDate", DATETIME,

    {

        { "16/03/2023 09:00", "16/03/2023 17:30" },

        { "16/03/2023 14:00", "16/03/2023 17:30" },

        { "17/03/2023 09:30", "17/03/2023 12:30" },

        { "16/03/2023 09:00", "17/03/2023 17:00" },

        { "16/03/2023 09:00", "20/03/2023 17:00" },

        { "16/03/2023 09:00", "20/03/2023 12:30" },

        { "16/03/2023 14:30", "20/03/2023 12:30" },

        { "17/03/2023 14:30", "20/03/2023 12:30" },

        { "16/03/2023 09:00", "27/03/2023 17:00" },

        { "02/03/2023 09:00", "27/03/2023 17:00" }

    }

)

Every thing is fine in the desktop  except the very last StartDate. 02/03/2023

This actually shows as 03/02/2023. All the other dates return UK format just fine.

Testing shows that if the first 2 digits are 01 to 12 it converts to US format.

I know you can change the locale in Power query but this is pure DAX.

To be clear, my locale is UK.

Why is it not consistent at keeping UK format?

How to fix it please.

Thanks

[v-heq-msft · Accepted solution]

Hi @MarkH ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Using DAX to create a calculate table:

Just Source = DATATABLE (
    "StartDate", DATETIME,
    "EndDate", DATETIME,
    {
        { "16/03/2023 09:00", "16/03/2023 17:30" },
        { "16/03/2023 14:00", "16/03/2023 17:30" },
        { "17/03/2023 09:30", "17/03/2023 12:30" },
        { "16/03/2023 09:00", "17/03/2023 17:00" },
        { "16/03/2023 09:00", "20/03/2023 17:00" },
        { "16/03/2023 09:00", "20/03/2023 12:30" },
        { "16/03/2023 14:30", "20/03/2023 12:30" },
        { "17/03/2023 14:30", "20/03/2023 12:30" },
        { "16/03/2023 09:00", "27/03/2023 17:00" },
        { "02/03/2023 09:00", "27/03/2023 17:00" }
    }
)

1.Select the field in the Modeling view, and then select the dropdown arrow under Format in the Properties pane.

2.Once you've selected Custom from the Format dropdown menu, choose from a list of firstformat.

3.Repeat the step1 and 2 and apply to another column

4.Final output

Best Regards,

Albert He

[MarkH]

however, that creates the columns as text & when you convert them to datetime the original problem reappears 😪

[MarkH]

Fixed it by not using DATATABLE  and forcing a format. (You cannot format using DATATABLE)

Source2 = UNION (

    ROW ( "StartDate", FORMAT("16/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("16/03/2023 17:30", "dd/mm/yyyy hh:mm") ),

    ROW ( "StartDate", FORMAT("16/03/2023 14:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("16/03/2023 17:30", "dd/mm/yyyy hh:mm") ),

    ROW ( "StartDate", FORMAT("17/03/2023 09:30", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("17/03/2023 12:30", "dd/mm/yyyy hh:mm") ),

    ROW ( "StartDate", FORMAT("16/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("17/03/2023 17:00", "dd/mm/yyyy hh:mm") ),

    ROW ( "StartDate", FORMAT("16/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("20/03/2023 17:00", "dd/mm/yyyy hh:mm") ),

    ROW ( "StartDate", FORMAT("16/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("20/03/2023 12:30", "dd/mm/yyyy hh:mm") ),

    ROW ( "StartDate", FORMAT("16/03/2023 14:30", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("20/03/2023 12:30", "dd/mm/yyyy hh:mm") ),

    ROW ( "StartDate", FORMAT("17/03/2023 14:30", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("20/03/2023 12:30", "dd/mm/yyyy hh:mm") ),

    ROW ( "StartDate", FORMAT("16/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("27/03/2023 17:00", "dd/mm/yyyy hh:mm") ),

    ROW ( "StartDate", FORMAT("02/03/2023 09:00", "dd/mm/yyyy hh:mm"), "EndDate", FORMAT("27/03/2023 17:00", "dd/mm/yyyy hh:mm") )

)

[Dangar332]

Hi, @MarkH 

use column tools 

in column tools choose format option and select    dd/mm/yyyy  or as you want 

[MarkH]

I had tried that & unfortunately it doesn't work.

wierd 😫