Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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"
Hi Stachu
Thank you very much indeed! That code works excellently and taught me an awful lot about Power Query.
Regards
Sam
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |