Create Date from MM/DD format and include current year? Power Query-CodePudding

I have a table that has a series of Columns with data I need to split out. Example below

STATUS@10/16 12:00:00 (CODE)

I've been able to split it easy enough and when I originally tried to set the date on an older dataset it identified it as a date e.g. 16th Oct 2021 However I started to get errors on this date column and trying with different datasets (10/12, 10/13, 10/14) it is not finding the date. I tried the following query code but I'm receiving errors

[STATUS DATE] is split to 10/14, 10/15 etc

#date( Date.Year(DateTime.LocalNow), Date.Month(Text.End([STATUS DATE]), 2), Date.Day(Text.Start([STATUS DATE]),2))

However I'm getting a function error so I tried

Date.From(Date.Day(Text.Start([STATUS DATE]),2) & Date.Month(Text.End([STATUS DATE]),2) & Date.Year(DateTime.LocalNow)

I have also tried to do this from an example column however the query created is looking at the cell value e.g. if 10/14 then 14/10/2021 else if 13/10 then 14/10/2021. This method i feel is prone for error once I include a larger dataset.

Is there anyway I can determine the date value based on mm/dd format? But with year end in mind, make the YYYY be determined by current year unless we move into Jan and then I don't want the Oct, Nov, Dec value showing as 2022.

CodePudding user response：

Split [STATUS DATE] one more time into [Month] and [Day] column, using the "/" as a separator. Then you don't have to bother with 1 or 2 digit numbers and you can simply use this formula:

#date(Date.Year(DateTime.LocalNow()), [Month], [Day])

DateTime.LocalNow() is a function, so you need to add the brackets.

[Month] and [Day] are numbers already, so you don't need the Date.Month() or Date.Day() functions.

CodePudding user response：

You don't really show what your original data looks like. But if it is like:

Source

Then you can use this code in the Add Custom Column dialog:

let 
   split=Text.SplitAny([STATUS DATE],"@/ "),
   mnth = Number.From(split{1}),
   dy = Number.From(split{2})
in 
   #date(Date.Year(DateTime.LocalNow()),mnth,dy)

The Text.SplitAny function lets you input a list of delimiters and the text will split on all of them. So it is relatively simple to extract the month and day values

to create: