How to create a month ID in PQ

[jazzista]

Hello... I am trying to create a custom column in my calendar table so that i can do date math with the month column: so i need months 1 thru 12 in the 1st year, then month 13 thru 24 in the second year, 25 thru 36 in the 3rd year and so on. Is it possible to do it dynamically ? I am attaching the desired result. Thanks in advance to this great forum. Regards

 

[NormS]

Do you mean something like this?

= Table.AddColumn(#"Changed Type", "Custom", each 12*(Date.Year([Day])-Date.Year(#"Changed Type"[Day]{0}))+Date.Month([Day])-Date.Month(#"Changed Type"[Day]{0})+1)

 

[jazzista]

Hi NormS: Thanks for your solution. But, when I copy and paste the code into a new column, it creates a new table. I just want the month ID in a single column. So year 1 goes 1 thru 12 , year 2 goes 13 thru 24 ,year three 25 thru 36 and so. Thanks for your help.

monthid
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

 

[AliGW]

You need to copy the query, group by year and month, add an index, then merge the new query with the old one. This mimics a COUNTIFS scenario in Excel.

 

[jazzista]

AliGW: Thank you so much for your solution. I did not know that it was this complex : I though by doing a custom column using the M code in the same date table using Table.Addcolumn function like Norms Did in his prior posting. I saw your code and its very clever specially adding the index column ( create a copy of your calendar, then group by year and month then merge those 2 calendar using left outer join) I will keep this solution and practice it.

 

[Bill Szysz]

let
    Source = Excel.CurrentWorkbook(){[Name="calendarwithmonthid"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type date}, {"month", Int64.Type}, {"year", Int64.Type}, {"month ID number", Int64.Type}}),
    YearOne = List.Min(#"Changed Type"[year]),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "month ID", each [month] + ([year]-YearOne)*12)
in
    #"Added Custom"

 

[jazzista]

Bill: Thank you verry muchhhhhh!!!!!!!!!!!!!!!!!!!!!!!!!!!!! This is the solution I was looking for. Quick question for general knowledge . You added a custom list called "YearOone" and you use it in the add columns function. Could you just use the year number column from the table? Again thank you for this great solution.

 

[NormS]

Jazzista: At first I didn't understand your reply to my question, but had you pasted my line of code at the end of your query, instead of creating a new query, you'd have ended up with the same result as Bill's. Granted, his code is a lot cleaner and easier to read since he's using the month and year columns instead of calculating those values from the day column. You do need to subtract the starting year in the formula. He uses the List.Min function to find the first year in the year column; I assumed the table was sorted chronologically and took the year from the entry in the first row (Date.Year(#"Changed Type"[Day]{0})). You'll also note that my formula includes the month from the first row. If you wanted your calendar table to start on July 1 instead of January 1 this ensures that the monthID starts at 1.

Finally, while I hope this has been a valuable learning experience, if you're going to build your calendar table on a worksheet you might consider adding this column to your table with the worksheet equivalent of Bill's formula "=12*(F2-F$2)+E2"

 

[jazzista]

Nomrs. Thanks for the clear explanation. I printed the your previous M code and Bilss code so i can play around . My calerndar table is bound to a data set and i use a techinque that i learned at this forum from Ken Puls ( Start.date End.date) and I wanted to add that month id column so I can do rolling averages going back 3 months, 6months in DAX . I Have learned a lot from this forum. Again, thanks you very much for your explanation and have a great day