Convert 'YYYYMDD' to date

[Anonymous]

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

[v-juanli-msft · Accepted solution]

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.

[az38]

@Anonymous 

how does 1-digit day number look like in your data source?

for example, 1 september 2008 and 1 october 2008?

[Anonymous]

Thank you for your reply,

For the days it's always a 2 digits day like '28' or '04' but for the month it's 1 for all numbers < 10

[az38]

@Anonymous 

or try to add a custom column in Power Query

= #date(Number.FromText(Text.Start([Column1], 4)), Number.FromText(Text.Middle([Column1], 4, Text.Length([Column1])-6)), Number.FromText(Text.End([Column1], 2)))

[az38]

@Anonymous 

create a column

Column = DATE(LEFT('Table'[Column1], 4), MID('Table'[Column1], 5, LEN('Table'[Column1])-6), RIGHT('Table'[Column1], 2))