Forum Discussion
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
Community 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.- SeanFLRegular Visitor
Thank you so much. That worked perfectly.
- mrozzano
Advocate 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 ?