Combine every three rows

[Anonymous]

Part of the problem here is that I don't know how to word what I'm trying to do in a way I can Google, so I apologize if this is not a good explanation.

I have a set of data converted from a PDF in a format like this:

School	Number of students	Time spent (min)	Syllabus complete %
Sample HS
Cumulative	100	8000	35%
Usage Period	50	1200	10%
Another HS
Cumulative	30	250	21%
Usage Period	4	10	2%

and so on, that I would like to be able to convert into something like:

School	Cumulative number of students	Cumulative time spent (min)	Cumulative syllabus complete	Usage period number of students	Usage period time spent (min)	Usage period syllabus complete
Sample HS	100	8000	35%	50	1200	10%
Another HS	30	250	21%	4	10	2%

I'm sure there must be some obvious way to do this that I'm just missing, but I don't know what it is. Can anyone help?

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MLchJVfAIVtJRUoDjWJ1oJefS3NKcxJLMslSgkKGBAZC0MABTxqaqYBWhxYnpqQoBqUWZ+SlAYVOQnKERWImhAUSJY15+SUZqEUHzjUGajMAmGBliNd0EbCpIHigdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [School = _t, #"Number of students" = _t, #"Time spent (min)" = _t, #"Syllabus complete %" = _t]),

    #"Split Records" = List.Split(Table.ToRecords(Source),3),
    #"Custom Records" = List.Transform(#"Split Records", each Record.Combine(List.Transform(List.Skip(_,1), each let prefix = [School], fnames = List.Skip(Record.FieldNames(_),1), names = List.Transform(fnames, each prefix & " " & _) in Record.RenameFields(_, List.Zip({fnames, names})))) & Record.SelectFields(_{0}, {"School"})),
    #"Combined Records" = Table.FromRecords(#"Custom Records")
in
    #"Combined Records"

[watkinnc · Accepted solution]

Let's say your table is named MyTable:

#"Added Index" = Table.AddIndexColumn(MyTable", "Index", 1, 1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Modulo", "Schools", each if [Modulo] = 1 then [School] else null, type text),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Schools"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"Schools"}, {{"Details", each _, type table [School=nullable text, Number of students=nullable number, #"Time spent (min)"=nullable number, #"Syllabus complete %"=nullable number, Index=number, Modulo=number, Schools=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Details]{1}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Number of students", "Time spent (min)", "Syllabus complete %"}, {"Cumulative.Number of students", "Cumulative.Time spent (min)", "Cumulative.Syllabus complete %"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each [Details]{2}),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Added Custom1", "Custom", {"Number of students", "Time spent (min)", "Syllabus complete %"}, {"Usage Period.Number of students", "Usage Period.Time spent (min)", "Usage Period.Syllabus complete %"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"Details"})
in
#"Removed Columns"

--Nate

[Anonymous]

I tried this and it overwrote the columns from "Expanded Custom" at "Added Custom1" for some reason - it worked up until that point, though. 

[Anonymous]

Never mind - I found the error and it was on my end.