Forum Discussion
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
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
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.
