create new rows for each number of students identified in the cell 'Number of new students'

[Anonymous]

Hi,

I have schood data that summarises the column named 'Number of Students' as number values - See below table:

School ID	Year	Semester	Year Level	Assessment Results	Number of students	Assessment Criteria Name
2	2016	1	Year 1	E	7	Reading and viewing
4	2017	2	Year 2	A	105	Reading and viewing
7	2018	3	Year 3	C	170	Reading and viewing
5	2019	4	Year 4	B	58	Reading and viewing

I want to create new rows for each number of students identified in the cell 'Number of new students'.

Example: If 'Number of new students' cell is '58' then create '58' new row instances of that cell.

The goal is to result in a single row for 1 student. Hence... 1 row represents 1 student. 

Appreciate the help!

Thanks,

Theo. 

[AlB · Accepted solution]

Hi @Anonymous 

This can be done easily and best in Power Query. Place the following M code in a blank query to see the steps. Could be done in DAX as well if required

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIyMDQDUoZAHJmaWKQAYrgCsTkQB6UmpmTmpSsk5qUolGWmlgPZSrE60UomEH0gJUYwfSCGI8ggA1M8Os0hOi2AlDFMJ4jhDNJpboBHpylEpyWQMoHpBDGcgNjUApfGWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"School ID" = _t, Year = _t, Semester = _t, #"Year Level" = _t, #"Assessment Results" = _t, #"Number of students" = _t, #"Assessment Criteria Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"School ID", Int64.Type}, {"Year", Int64.Type}, {"Semester", Int64.Type}, {"Year Level", type text}, {"Assessment Results", type text}, {"Number of students", Int64.Type}, {"Assessment Criteria Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Numbers(1,[Number of students])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Custom"})
in
    #"Removed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers