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.
Thank you so much. That worked perfectly.