Forum Discussion
Custom date format with date data type
I have what must be a simple issue, but can't find a solution. I have a date column which the client needs formatted in European style, i.e. mm.dd.yyyy
If I use the format function PBI seems to insist on calling it text, but I need it to be a date field. How is this accomplished?
Hi pozzyal,
Based on my research, I find that there is no region which use date format like mm.dd.yyyy(if your date is mm.dd.yyyy, you could change this to date type by = Table.TransformColumnTypes(Source, {{"Column1", type date}}, "es-PA") in M code, but you can't change type to mm.dd.yyyy). You could refer to this link for details. So we can't use built-in date format, we need to use format expression which convert type to text.
So I suggest you could use date type to calculate result , use mm.dd.yyyy as text column to show in table.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
So what's the actual end goal, and what does your source data look like? You say European style but here, like the rest of the non-US world, we use dd/mm/yyyy, so I can only assume what you actually mean is you want dots as the separator?
The client deals with mostly European countries and they described the format as "European" so I assumed that's what it is. Mainly with the dots instead of dashes, and I thought they switch month and day from what we do - I may have gotten that wrong in my example. Bottom line is I want to use a date format which isn't in the list provided in the drop-down, but still have it be a date column. Seems like that has to be possible, but I can't find it.
Hi pozzyal,
Based on my research, I find that there is no region which use date format like mm.dd.yyyy(if your date is mm.dd.yyyy, you could change this to date type by = Table.TransformColumnTypes(Source, {{"Column1", type date}}, "es-PA") in M code, but you can't change type to mm.dd.yyyy). You could refer to this link for details. So we can't use built-in date format, we need to use format expression which convert type to text.
So I suggest you could use date type to calculate result , use mm.dd.yyyy as text column to show in table.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
