Transforming DATE data types

[Anonymous]

I have a dataset that does not recognize the calendar date format. My dataset is based on sales and only has the "Period/Year" as a date option. (period being the month it occured). So all my sales have a "Period/Year" associated with it but no actual date (ex: the 16th or the 31st). The format of the "Period/Year" looks like this "001/2024", "1" representing the month of January and year being 2024. 

Whenever I try to change the data type in the Power Query from Text to Date, it returns back "1/1/2024" which is not accurate to the sales. I tried to create a separate column by month and year and it still won't let me change it/

How can I create a DATE data type with just the month and year?

[m_dekorte · Accepted solution]

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.

[collinsg · Accepted solution]

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.

[m_dekorte · Accepted solution]

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.