Solved: How do I convert a GL Period (MAY-19) from Text in...

Jdokken · ‎03-06-2020

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

ImkeF · ‎03-06-2020

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

v-frfei-msft · ‎03-08-2020

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"

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.