Transforming DATE data types

Hi Anonymous 

A date data type in M classifies date values that's why a conversion yielded an actual date value, the 1st of the month. You can change its format but a month - year combination without a day component (which is no true date) will be of type text.

Good day jwasilko,

As your data does not include the day of month the best that can be done is to choose a day of the month to represent the month - for example the first day of each month or the last day of each month. If your requirement can be satisified by turning "001/2024" to 1st January 2024, "002/2024" to 1st February 2024 etc, then this code will solve the problem.

The approach is to

• Transform the column, splitting each value at the "/" into a list of two items.
• Transform the column once more, creating a date using the day of month as 1 and taking the month and year from the items in the list.

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAw1DcyMDJRitUBcYyQOcZQTiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month/Year" = _t]),
  #"Split at slash" = Table.TransformColumns(Source, {{"Month/Year", each Text.Split(_,"/"), type text}}),
  #"Convert to first of month" = Table.TransformColumns( #"Split at slash", {{"Month/Year", each #date(Number.From(_{1}), Number.From(_{0}),1), type date}} )
in
  #"Convert to first of month"

 This will turn...

into

If you wished to use the end of the month rather than the start of month you could transform the column once more using Date.EndOfMonth.

Hope this helps.