Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.