Dataflow Issues converting Date type from US to UK

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