I have two challenges, i am trying to display the current year/next year with employeed id as a prefix.Also i wish to group date eight two-week periods ~= 4 months in powerquery . This is the Challenge

Expected Outcome:

How do i group the date, considering that Ist term start from september, 17th this year (1st term), Second begins January,17th next year (2nd term) and Third term start from June 17th next year, until it begin another fresh cycle starting from Sepetember 17th and so on and so forth.

I wish this to be automated in powerquery.

Thank you.

CodePudding user response：

Something like this

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name of Participant", type text}, {"empid", Int64.Type}, {"Year", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Enrollmentid", each "M-"& Text.From([empid])& "-"&Text.From(Date.Year([Year]))& "/"&Text.From(Date.Year([Year]) 1)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Term", each
    if Date.DayOfYear([Year]) <17 then "1st term" else
    if Date.DayOfYear([Year]) >16 and Date.DayOfYear([Year])<168 then "2nd term" else
    if Date.DayOfYear([Year]) >167 and Date.DayOfYear([Year])<260 then "3rd term" else
    if Date.DayOfYear([Year]) >259 then "1st term"
    else null)
in  #"Added Custom1"

CodePudding user response：

Based on the example dates, it looks like you have fixed start and end dates for terms rather than a repeating 16 week cycle (8x2).

You can determine the term by checking if the Year date falls within the term start and end dates.
Create a helper table with dates to determine the term.
The table should have the term start and end dates together with the term as columns.

Import it using Power Query

Add a custom column that creates a list of dates between each start and end date.

=List.Dates([Start Date],Duration.Days( [End Date] - [Start Date] )   1,  #duration( 1, 0, 0, 0 ))

Expand the list of dates.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Term", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "All Dates", each List.Dates([Start Date],Duration.Days( [End Date] - [Start Date] )   1,  #duration( 1, 0, 0, 0 ))),
    #"Expanded All Dates" = Table.ExpandListColumn(#"Added Custom", "All Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded All Dates",{{"All Dates", type date}})
in
    #"Changed Type1"

In the main query, merge the helper table joining the Year date with the All Dates column.
Expand the table to return the Term.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name of Participant", type text}, {"empid", Int64.Type}, {"Year", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Enrollmentid", each "M-"& Text.From([empid])& "-"&Text.From(Date.Year([Year]))& "/"&Text.From(Date.Year([Year]) 1)),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Year"}, Table3, {"All Dates"}, "Table3", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(#"Merged Queries", "Table3", {"Term"}, {"Table3.Term"})
in
    #"Expanded Table3"