Help creating table in Power Query

[elJukes]

Hello

I have a table of student data including the following columns:

StudentID Enrolement Start Date Enrolment End Date Current/Last Year Group

0001	26 September 2007	21 July 2012	6
0002	12 September 2008	21 July 2012	5
0003	18 September 2007	21 July 2012	6

What I need to do is create a separate table that lists for every StudentID, which Year Group they were in for each year they were enroled similar to:

StudentID Year Year Group

0001	2012	6
0001	2011	5
0001	2010	4
0001	2009	3
0001	2008	2
0001	2007	1
0002	2012	5
0002	2011	4
0002	2010	3
0002	2009	2
0002	2008	1
0003	2012	6
0003	2011	5
0003	2010	4
0003	2009	3
0003	2008	2
0003	2007	1

I've tried googling, and going over the forums but am completely stuck. From what I've picked up I'm thinking this may be a Power Query scenario? I'm not overly familiar with Power Query however and would appreciate any help anyone could offer.

[Stachu · Accepted solution]

try this code, you will need to change the first step to reference your original table (red code)

the main trick was using the list generator - e.g. {2..5} generates list from 2 to 5 (blue code)

let
    Source = Table1,
    #"Added Custom1" = Table.AddColumn(Source, "Year", each {Date.Year([Enrolement Start Date])..Date.Year([Enrolment End Date])}),
    #"Expanded Year" = Table.ExpandListColumn(#"Added Custom1", "Year"),
    #"Added Custom" = Table.AddColumn(#"Expanded Year", "Year Group", each [Year]-Date.Year([Enrolement Start Date])+1),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"StudentID", "Year", "Year Group"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"StudentID", Order.Ascending}, {"Year", Order.Descending}})
in
    #"Sorted Rows"

[elJukes]

Hi Stachu

Thank you very much indeed! That code works excellently and taught me an awful lot about Power Query.

Regards

Sam