cancel
Showing results for
Did you mean:
New Member

## Combine every three rows

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?

1 ACCEPTED SOLUTION
Solution Sage

Let's say your table is named MyTable:

#"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]}}),
#"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 %"}),
#"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

I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
4 REPLIES 4
Super User III
``````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"``````

Solution Sage

Let's say your table is named MyTable:

#"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]}}),
#"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 %"}),
#"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

I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
New Member

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

New Member

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

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.