Forum Discussion

SeanFL's avatar
SeanFL
Regular Visitor
4 years ago
Solved

Convert quarterly date (Q3 2022) to numeric date

Hey,   I'm have a column of dates, some of which are formatted numerically 3/2/2022, some of which are entered as "Q1 2023", and a few "null" entries. The column itself is of Any (ABC-123). I'm try...
  • edhans's avatar
    4 years ago

    Make that field text. Working with ANY or ABC/123 is a pain.
    I get this:

    The formula I used in the custom column is:

    try 
       Date.FromText([Target Dev Start]) otherwise 
       Date.StartOfQuarter(
          #date(
              Number.From(Text.End([Target Dev Start],4)), 
              Number.From(Text.Middle([Target Dev Start], 1, 1)),
              1
           )
        )

     

    The full M code is

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRUMDIwMlaK1YlWMtQ31IdwYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Target Dev Start" = _t]),
        #"Added Date" = 
            Table.AddColumn(
                Source, 
                "Date", 
                each try Date.FromText([Target Dev Start]) otherwise 
                    Date.StartOfQuarter(
                        #date(Number.From(Text.End([Target Dev Start],4)), Number.From(Text.Middle([Target Dev Start], 1,1)), 1)
                    ),
                type date
            )
    in
        #"Added Date"

     How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.