Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tyron
Frequent Visitor

Combining current and previous year enrolment figures with different course codes

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:

 

CodeCourseYear 1Year 2Year 3Year 4
ABC123 Business Course 1000
ABC123 Business Course 0100
ABC123 Business Course 1000
ABC123 Business Course 1000
ABC123 Business Course 0100
ABC123 Business Course 0010
ABC456 Business Course 0001
ABC456 Business Course 0001

 

Expected table result should look like this:

CodeSuperseded CodeCourseYear 1Year 2Year 3Year 4
ABC456 ABC123 Business Course 3212

 

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

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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"

21090203.jpg

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.

View solution in original post

5 REPLIES 5
v-jingzhang
Community Support
Community Support

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"

21090203.jpg

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.

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.

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. 

21091002.jpg

 

Reference: Grouping or summarizing rows | Microsoft Docs

 

Best regards,

Jing

Tyron
Frequent Visitor

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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors