cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gram_brinson
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:

SchoolNumber of studentsTime spent (min)Syllabus complete %
Sample HS   
Cumulative100800035%
Usage Period50120010%
Another HS   
Cumulative3025021%
Usage Period4102%

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

SchoolCumulative number of studentsCumulative time spent (min)Cumulative syllabus completeUsage period number of studentsUsage period time spent (min)Usage period syllabus complete
Sample HS100800035%50120010%
Another HS3025021%4102%

 

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

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


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!!

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-07-22 095731.png

watkinnc
Super User
Super User

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


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!!

View solution in original post

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

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors