Forum Discussion

ssm_214's avatar
ssm_214
New Member
2 years ago
Solved

I am looking to make Events numbered based on specific dates and make the process dynamic.

I have a set of dates for an event. I know that the events usually happen twice a year from certain months. I want to create a column where each event is numbered in order as time goes on, Event 1, Event 2, Event 3, Event 4, etc. I figured i may need a helper column for this but i am not sure where to start.

 

Any advice is appreciated. Thank you.

 

I will attach an example file for you to view. Summit Data.xlsx

Need Help
  • ManuelBolz's avatar
    ManuelBolz
    2 years ago

    Hello ssm_214,

     

    I'm not sure if I understand your question correctly. My following code Creates the TimeFrame column based on Approved Date. If the approval date is between December (12) and February (2), enter the number 1, and if the approval date is between May (5) and July (7), enter the number 2.

    Did I understand you correctly?

    let
    Source = YOUREXCELFILE,
    Type = Table.TransformColumnTypes(Source,{{"Approved Date", type date}}),
    ColumnTimeFrame = Table.AddColumn(Type, "TimeFrame", each 
    if Date.Month([Approved Date]) >= 12 or Date.Month([Approved Date]) <= 2 
        then 1 
    else if Date.Month([Approved Date]) >= 5 or Date.Month([Approved Date]) <= 7 
        then 2
    else null
    , Int64.Type)
in
    ColumnTimeFrame


    Best regards from Germany
    Manuel Bolz

    If this post helped you, please consider Accept as Solution so other members can find it faster.

    🤝Follow me on LinkedIn

1 Reply

  • ManuelBolz's avatar
    ManuelBolz
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    Hello ssm_214,

     

    I'm not sure if I understand your question correctly. My following code Creates the TimeFrame column based on Approved Date. If the approval date is between December (12) and February (2), enter the number 1, and if the approval date is between May (5) and July (7), enter the number 2.

    Did I understand you correctly?

    let
    Source = YOUREXCELFILE,
    Type = Table.TransformColumnTypes(Source,{{"Approved Date", type date}}),
    ColumnTimeFrame = Table.AddColumn(Type, "TimeFrame", each 
    if Date.Month([Approved Date]) >= 12 or Date.Month([Approved Date]) <= 2 
        then 1 
    else if Date.Month([Approved Date]) >= 5 or Date.Month([Approved Date]) <= 7 
        then 2
    else null
    , Int64.Type)
in
    ColumnTimeFrame


    Best regards from Germany
    Manuel Bolz

    If this post helped you, please consider Accept as Solution so other members can find it faster.

    🤝Follow me on LinkedIn