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

Anonymous — Sun, 01 Nov 2020 04:46:14 GMT

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.

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

AlB — Sun, 01 Nov 2020 08:02:21 GMT

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

topic Re: create new rows for each number of students identified in the cell 'Number of new students' in DAX Commands and Tips

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

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