Help creating table in Power Query

Question

Hello&nbsp;I have a table of student data including the following columns:&nbsp;&nbsp;StudentID Enrolement Start Date Enrolment End Date Current/Last Year Group000126 September 200721 July 20126000212 September 200821 July 20125000318 September 200721 July 20126&nbsp;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:&nbsp;&nbsp;StudentID Year Year Group000120126000120115000120104000120093000120082000120071000220125000220114000220103000220092000220081000320126000320115000320104000320093000320082000320071&nbsp;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 Answer

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"

Forum Discussion

Help creating table in Power Query

2 Replies

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Creating Date Tables

Creating a couple of tables

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

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