Forum Discussion
Convert quarterly date (Q3 2022) to numeric date
- 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.
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.
- SeanFL4 years agoRegular Visitor
Thank you so much. That worked perfectly.
- mrozzano1 year ago
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 ?