Combining current and previous year enrolment figures with different course codes

[Tyron]

Can I ask for some assistance with an issue, I need to combine enrolment figures for a course with differing codes over the past 4 years, additionally adding the old code to the row. Each row contains 1 enrolment. Below is a sample table:

Code	Course	Year 1	Year 2	Year 3	Year 4
ABC123	Business Course	1	0	0	0
ABC123	Business Course	0	1	0	0
ABC123	Business Course	1	0	0	0
ABC123	Business Course	1	0	0	0
ABC123	Business Course	0	1	0	0
ABC123	Business Course	0	0	1	0
ABC456	Business Course	0	0	0	1
ABC456	Business Course	0	0	0	1

Expected table result should look like this:

Code	Superseded Code	Course	Year 1	Year 2	Year 3	Year 4
ABC456	ABC123	Business Course	3	2	1	2

Any assistance would be greatly appreciated as I have thousands of rows with hundreds of different courses, thanks.

[v-jingzhang · Accepted solution]

Hi @Tyron 

Create a new blank query, open Advanced Editor and paste below codes to replace its original code there. Or download the pbix for reference. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVtJROrTAqbQ4My+1uFjBOb+0qDgVLGYIJA3gOFaHoAaQQkNSNJBsA+2dBDPdEFmDiakZQQ0QTaRriAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Course = _t, #"Year 1" = _t, #"Year 2" = _t, #"Year 3" = _t, #"Year 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Course", type text}, {"Year 1", Int64.Type}, {"Year 2", Int64.Type}, {"Year 3", Int64.Type}, {"Year 4", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Course"}, {{"GroupTable", each _, type table [Code=nullable text, Course=nullable text, Year 1=nullable number, Year 2=nullable number, Year 3=nullable number, Year 4=nullable number]}, {"Year 1", each List.Sum([Year 1]), type nullable number}, {"Year 2", each List.Sum([Year 2]), type nullable number}, {"Year 3", each List.Sum([Year 3]), type nullable number}, {"Year 4", each List.Sum([Year 4]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Record.FromList(List.Distinct([GroupTable][Code]),{"old code", "new code"})),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"old code", "new code"}, {"old code", "new code"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"GroupTable"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"new code", "old code", "Course", "Year 1", "Year 2", "Year 3", "Year 4"})
in
    #"Reordered Columns"

The main steps are "Grouped Rows" and "Added Custom". Use Transform > Group By and Add Column > Custom column.

Let me know if you have any questions.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Tyron]

Hi @v-jingzhang 

Thank you for this, appreciated. I am still learning Powery Query and if I have to add other colums in such as Region (state) and location (city) is this possible by just adding the column names in the line

 #"Grouped Rows" = Table.Group(#"Changed Type", {"Course"},

by adding {"Region"}, {"Location"} after {"Course"}, or is there another line to add this as well?

Thanks again.

[v-jingzhang]

Hi @Tyron 

Yes, it would be something like Table.Group(#"Changed Type", {"Course", "Location"}, ............)

I suggest that you can modify this step by using the User Interface. I also use Group By feature under Transform tab to do it. To modify a step, you can click on the gear icon in Applied Steps, then in Group By window, click Add grouping to add more columns to group by. After you click OK to apply the change, you will find the code in formula bar above updated. 

Reference: Grouping or summarizing rows | Microsoft Docs

Best regards,

Jing

[Tyron]

Hi @Greg 

Thanks for your suggestion, I have unpivoted the Year columns but the expected result is not what I need to do.

I tried to unpivot and then pivot with "Don't Aggregate" as well but that was also not the expected result required.

Do you have any other suggestions other than manually going through the superseded column?

[Greg_Deckler]

@Tyron If possible, unpivot your Year columns and then this becomes trivial.