Forum Discussion
Convert 'YYYYMDD' to date
Hello everyone,
I am trying to convert a text which is concatenate with different value like '2020128' for 2020 = Year, 1 = January, 28 = Day
It works for 2 digits month but for 1 digit month it shows an error.
For now I am using a conditionnal column to fix my issue for all numbers < 10. Like if 1 => "01" etc.
But I want to know if they are another option more efficient to do this ?
And also do you know if there are a list exhaustive of date pattern that Power Query or Dax can convert directly to a date ?
Like 'YYYYMM', 'YYYY-MM-DD' etc.
Thank you very much for your help,
Have a nice day,
Loveboch
Hi Anonymous
Add column
Text.Combine({Text.Start(Text.From([date], "en-US"), 4), Text.Middle(Text.From([date], "en-US"), 4, Number.From(Text.Length(Text.From([date], "en-US")))-6), Text.End(Text.From([date], "en-US"), 2)}, "/")change type
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Anonymous
how does 1-digit day number look like in your data source?
for example, 1 september 2008 and 1 october 2008?
Anonymous
create a column
Column = DATE(LEFT('Table'[Column1], 4), MID('Table'[Column1], 5, LEN('Table'[Column1])-6), RIGHT('Table'[Column1], 2))
Hi Anonymous
Add column
Text.Combine({Text.Start(Text.From([date], "en-US"), 4), Text.Middle(Text.From([date], "en-US"), 4, Number.From(Text.Length(Text.From([date], "en-US")))-6), Text.End(Text.From([date], "en-US"), 2)}, "/")change type
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
