Dataflow Issues converting Date type from US to UK

[LouisWells]

I've imported some data from an MS list where the date/time values print with a US local

"1/30/2025, 3:32:54 PM"

For the purposes of this report I need to merge in a calendar that maps our reporting periods to dates (they don't line up with months but shift year to year) so I need my dates in a UK format.

I've confirmed that the workspace is set to a UK date format by default, that my machine is set to a UK date and language format by default, and that my browser is set to a UK date and language format by default, however none of this fixed the dataflow converting the date/time to mm/dd/yyyy format when converting to date type.

A list of things I've tried with no success:

I've converted the string value to date/time in the "en-US" format before then converting it to a date type with a "en-GB" locale, didn't work.

I've split the column along the , delimiantor into two text columns, date and time and then taken the date from text of the date column and set that format type to "en-GB" and it didn't work.

I've converted the date from the date time column into text formatted dd/MM/yyyy which did get it in the right format but the wrong type, but then converting it back to a date type with a "en-GB" locale still turned it back to MM/dd/yyyy.

Setting the column to date time, timezone does this: "7/22/2025, 12:00:00 AM +00:00" so still the wrong format.

After extensive googling I have found lots of people with the same issue, but no proposed solution that works for a dataflow.

[BA_Pete · Accepted solution]

Hi @LouisWells ,

Not sure you need to do anything here to be honest as US dates will merge/relate to UK dates just fine - DateTime is just a decimal number under the hood, Date an integer. As long as your calendar table has the date in the format you want for reporting front-end, there should be no issue.

If there really is an issue relating these columns, then you can create ISO columns in both your fact table and your calendar table to relate on instead. something like this:

// Convert date to ISO
Text.Combine(
    {
        Text.From(Date.Year([YourDate])),
        Text.PadStart(Text.From(Date.Month([YourDate])), 2, "0"),
        Text.PadStart(Text.From(Date.Day([YourDate])), 2, "0")
    }, "-"
)

This *should* give you dates like 2025-09-03 in both tables to relate/merge on.

As an aside:

-1- Don't merge your calendar onto this table, relate it in the data model. Merging is a very resource-expensive operation and in 90% of cases (made up stat, but easily the majority of cases) can be replaced with very efficient relationships.

-2- Split your DAteTime fields into Date and Time component columns as a matter of best practice. This reduces the cardinality of the column(s) greatly reducing footprint and relationship size.

Pete

[LouisWells]

Makes sense that it merges fine since its using some unix epoch like number.

I'll leave as is since I can format the columns in my reports itself.

I'm chosing to merge over using relationships in reports where possible as the report will be opened far more frequently than it is refreshed, and as far as I understand it, if I merge in my dataflow although it will slow down my dataflow refresh, it will ensure my report isn't slowed down by complex relationships. Though the relationships in the report I'm making would be pretty simple many to one from various date fields from an MS list (created modified, etc) to the periodic calendar lookup.

[LouisWells]

A solution I could use for merging in the right period identifiers I need could be converting the data of my calendar lookup to text, and the dates in this dataflow to text in the right format, merging in the data I need then converting it all back to date, but that doesn't solve my issue of the data being in the wrong locale format.