Forum Discussion

Jdokken's avatar
Jdokken
Icon for Helper III rankHelper III
6 years ago
Solved

How do I convert a GL Period (MAY-19) from Text into a date 5/1/2019?

If I try to change the GL Period to DATE in Power Query Editor in Power BI it changes May-19 to 5/19/2020 and it should change it to 5/1/2019.  After I figure this out, then I need to figure out how to get the dates to sort properly. Jan-2019, Feb-20

 
Need Help
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Jdokken 

    try:

    Date.FromText( "1-" & "May-19" )

     

    You need a sort-column which you can sort the text columns by:

     

    Date.Year([Custom]) * 100 + Date.Month([Custom])

     

    Provided that [Custom] returns a proper date-format.

     

     

3 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Jdokken 

    try:

    Date.FromText( "1-" & "May-19" )

     

    You need a sort-column which you can sort the text columns by:

     

    Date.Year([Custom]) * 100 + Date.Month([Custom])

     

    Provided that [Custom] returns a proper date-format.

     

     

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Jdokken ,

     

    As ImkeF  said, Date.FromText should work well. Then we can sort the table by date.

     

    Date.FromText( "1-" & [date])

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k2s1DW0VIrViVbyKs2DMd1Sk8DMWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.FromText( "1-" & [date]))
in
    #"Added Custom"