Hello,
I have a table which has staff timetables in a school. The table only has rows for where a member of staff has a class. I would like to add rows for all possible lessons, but the setcode is ‘Free’ if they don’t teach a class (essentially, putting Free in all the blue cells below).
Sample PBIX is attached.
Free lessons problem.pbix (35.5 KB)
How can I go about doing this?
Thanks in advance.
Matt
Hi @corkemp,
See if this works for you.
Copy this code into a new blank query in the supplied sample PBIX
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i1ScFTSUTIEYt/8vJTESiDDCSRgoOCerhSrA1dhhK7CEk2BMboCczQFIDtCSlOLCViCpsQITYkZphJjNCXmGErQ3QpySnhqSh5em0ywKUK3C+ycjNIifL4C2eZWlIk7ZMzRFUDcGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Teacher = _t, Lesson = _t, Day = _t, #"WeekA/B" = _t, Setcode = _t]),
DelOtherColumns = Table.SelectColumns(Source,{"Teacher"}),
RemoveDuplicates = Table.Distinct(DelOtherColumns),
GetAllPeriodIDs = Table.AddColumn(RemoveDuplicates, "Custom", each PeriodIDLookup),
ExpandPeriodData = Table.ExpandTableColumn(GetAllPeriodIDs, "Custom", {"PeriodId", "Lesson No.", "Day", "Week A/B"}, {"PeriodId", "Lesson No.", "Day", "Week A/B"}),
MergeTimeTable = Table.NestedJoin(ExpandPeriodData, {"Teacher", "Lesson No.", "Day", "Week A/B"}, #"Staff timetable", {"Teacher", "Lesson", "Day", "WeekA/B"}, "Staff timetable", JoinKind.LeftOuter),
ExpandTimeTableData = Table.ExpandTableColumn(MergeTimeTable, "Staff timetable", {"Setcode"}, {"Setcode"}),
GroupRows = Table.Group(ExpandTimeTableData, {"Teacher", "Week A/B"}, {{"MaxVal", each List.Max([Setcode]), type nullable text}, {"AllRows", each _, type table [Teacher=nullable text, PeriodId=number, #"Lesson No."=number, Day=text, #"Week A/B"=text, Setcode=nullable text]}}),
ExpandAllRows = Table.ExpandTableColumn(GroupRows, "AllRows", {"PeriodId", "Lesson No.", "Day", "Setcode"}, {"PeriodId", "Lesson No.", "Day", "Setcode"}),
ReplaceBlanks = Table.ReplaceValue(ExpandAllRows,null,"Free",Replacer.ReplaceValue,{"Setcode"}),
AddSetcode2 = Table.AddColumn(ReplaceBlanks, "Setcode2", each if [MaxVal] = null then null else if [Setcode] = null then "Free" else [Setcode], type text),
ChType = Table.TransformColumnTypes(AddSetcode2,{{"Week A/B", type text}})
in
ChType
.
[Setcode] all nulls replaced with “Free”
[Setcode2] all nulls replaced with “Free” in Weeks with at least one Setcode
I hope this is helpful.
Perfect - thank you so much!
Matt