Power Query: Build Date out of Text

Question

our Ticketsystem gives me nice reports, unfortunately they are stupidly formatted. in a monthly overview, I get the date column as:

Mon 1 
Tue 2 
Wed 3 
... 
Sat 31

I'd like to build a proper date out of this nonses. My Idea is to:

1. Split the column by delimiter (first space from left) giving me Columns Date.1 and Date.2
2. Concatenate Date.2 & "." & "current month - 1" & "." & "current year"
3. Format this as date

Is this correct so far? If so: I know how to get e.g. the month out of a date, but how do I get "currentmonth - 1" (i.e. the last month number) and current year?

Thanks

Answer 1

You can format the date, then add a custom column with Date.AddMonths. If your date column is in [Date], then the custom formula you want to add is Date.AddMonths([Date], -1).

If you want the current year (local), you can use Date.Year(DateTime.FixedLocalNow)). Building a date from this is problematic, however, because the table will change based on when you view the report.

Answer 2

Got it:

Date.Month(Date.AddMonths(DateTime.FixedLocalNow(), -1))

Answer 3

I would do like that:

    SplitTable = //split that date
    Now = DateTime.FixedLocalNow(),
//then
    NewCol = Table.AddColumns(SplitDate, "NewDate", 
              each #date(Date.Year(Date.AddMonths(Now, -1)), Date.Month(Date.AddMonths(Now, -1)), [Date.2]  ),
//etc.

But also you have to add checks if [Date.2] day exists in prev. month, since e.g. in march you'll get errors for days later than 28/29.