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 trying to create a new column that copies dates entered in the numeric format and those that are null and changes anything written as a quarterly date into a generic placeholder date.

Q1 - 1/1/2023

Q2 - 4/1/2023

Q3 - 7/1/2023

Q4 - 10/1/2023

 

I know my code doesn't work and I appreciate any direction or assistance.

 

 

if Type.Is([Target Dev Start], Date.Type) = true or null then [Target Dev Start] else 
Date.StartOfQuarter(#date(Number.From(Text.End([String],4)), Number.From(Text.Start([String], 2)), 1))

 

 

 

  • 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.

4 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion

    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.

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion

      SeanFL - was that helpful? If not, please post back with info so we can fine tune the answer. If it was, please mark it as a solutions so others can know the thread was solved.

    • SeanFL's avatar
      SeanFL
      Regular Visitor

      Thank you so much. That worked perfectly. 

    • mrozzano's avatar
      mrozzano
      Icon for Advocate I rankAdvocate I

      Does this solution really work ? The 2nd argument of #date is the month, but the quarter number doesn't correspond to the month. Hence, only Q1 shows the start of the quarter, but no others quarters will. See below, and how it's interpreted when I use an imaginary Q5 value.

       

       

      Am I missing something in this solution ?