Results 1 to 9 of 9

Thread: How to create a month ID in PQ

  1. #1
    Acolyte jazzista's Avatar
    Join Date
    Jan 2017
    Posts
    35
    Articles
    0
    Excel Version
    excel 2016

    How to create a month ID in PQ



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    43
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Do you mean something like this?

    Code:
    = 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)

  3. #3
    Acolyte jazzista's Avatar
    Join Date
    Jan 2017
    Posts
    35
    Articles
    0
    Excel Version
    excel 2016
    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

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,375
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Attached Files Attached Files
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Acolyte jazzista's Avatar
    Join Date
    Jan 2017
    Posts
    35
    Articles
    0
    Excel Version
    excel 2016
    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.

  6. #6
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    106
    Articles
    0
    Excel Version
    Excel 365
    Code:
    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"

  7. #7
    Acolyte jazzista's Avatar
    Join Date
    Jan 2017
    Posts
    35
    Articles
    0
    Excel Version
    excel 2016
    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.

  8. #8
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    43
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    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"

  9. #9
    Acolyte jazzista's Avatar
    Join Date
    Jan 2017
    Posts
    35
    Articles
    0
    Excel Version
    excel 2016
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •