Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
az38
Community Champion
6 years ago

Anonymous 

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

for example, 1 september 2008 and 1 october 2008?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      az38
      Community Champion
      6 years ago

      Anonymous 

      create a column

      Column = DATE(LEFT('Table'[Column1], 4), MID('Table'[Column1], 5, LEN('Table'[Column1])-6), RIGHT('Table'[Column1], 2))
    • az38's avatar
      az38
      Community Champion
      6 years ago

      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)))