Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Convert Text date to date format

I have a text date field in the format of YYYYMMDD. I need to be able to reference just the YYYYMM in a slicer. How can I get this into a slicer?

 

Thanks

Need Help

5 Replies

  • Irwan's avatar
    Irwan
    Super User
    2 years ago

    hello Anonymous 

     

    you need to extract year, month, and day value from that string to create date format YYYY-MM-DD.

    this is one way of many ways.

    Both DAX and Power Query work same (extract string number).

    if you want to use DAX.

    Date format DAX =
    var _year = LEFT('Table'[Column1],4) //extract year number
    var _day = RIGHT('Table'[Column1],2) //extract day number
    var _month = RIGHT('Table'[Column1],4)-RIGHT('Table'[Column1],2)
    var _monthnum = SUBSTITUTE(_month,RIGHT(_month,2),"") //extract month number
    Return
    DATEVALUE(CONCATENATE(CONCATENATE(_year&"-",_monthnum&"-"),_day))
     
    if you want to use Power Query:
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjEwMzRUitWBcAwNDU2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Inserted First Characters" = Table.AddColumn(#"Changed Type", "First Characters", each Text.Start(Text.From([Column1], "en-ID"), 4), type text),
    #"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Text Range", each Text.Middle(Text.From([Column1], "en-ID"), 4, 6), type text),
    #"Extracted First Characters" = Table.TransformColumns(#"Inserted Text Range", {{"Text Range", each Text.Start(_, 2), type text}}),
    #"Inserted Last Characters" = Table.AddColumn(#"Extracted First Characters", "Last Characters", each Text.End(Text.From([Column1], "en-ID"), 2), type text),
    #"Merged Columns" = Table.CombineColumns(#"Inserted Last Characters",{"First Characters", "Text Range", "Last Characters"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date", "Date Power Query"}})
    in
    #"Renamed Columns"
     

     

    dont forget to change data type into date after using DAX or Power Query since both result still in text.

     

    Hope this will help you.

    Thank you.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    This custom column in Power Query will work

    Date.From(Text.From([DateKey], "en-US")  

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Ashish, Could you provide an example. I'm not sure I understand the syntax.

       

      Thanks

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Hi,

        That code simply has to be pasted in the Custom Column window of Power Query.

  • Kaviraj11's avatar
    Kaviraj11
    Solution Sage
    2 years ago

    Hi,

     

    There's a couple of ways you can create a format and use in slicer

    1. Calculated Column in Date Table:

      • If you have a date table, create a calculated column with the following formula:
        Date_New = FORMAT('my_data'[Date], "YYYYMM")

    2. M Query in Query Editor:

      • In the Query Editor, write a single M statement to convert YYYYMMDD to a proper date format:
        Date.From(Text.From([Date], "en-US"))

    3. Concatenation for Month Slicer:

      • If you want to use the YYYYMM as a slicer, you can create a calculated column for the month:
        Yearmonth = CONCATENATE(YEAR([Date]), MONTH([Date]))

        Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!