Can I still use similar code to the below if headers are different on different sheets and also the Friday is different from other 4 days of the week in every sheet. Data is coming from an excel file with different sheets. However although headers are almost the same, the first 3 sheets have 1 less column. For example, first 3 sheets will have: MA(arrival),MR(reading),M1(lesson1),M2,MB,M3,M4,ML,M5,M6,ME,MD and the remaining sheets have MA,MR,M1,M2,M3,MB,M4,M5,ML,M6,M7,ME,MD. Also, Fridays looks differentfrom other days of the week for everyone: FA,FR,F1,F2,FB,F3,F4,FL,FF,FA,FO.
Trying to have a timeline with lessons and sessions in the right order.
Will I be able to still create something like this? Or due to the different headers won’t be possible?
Help appreciated. I’m stuck here.
Thanks,
= Table.AddColumn(
#"Changed Type",
"Lesson Index",
each let
Digits = {"0".."9"},
LessonSplit = Splitter.SplitTextByCharacterTransition(each not List.Contains(Digits,_), each List.Contains(Digits,_))([Lesson]),
DayOfWeek = try LessonSplit{0} otherwise null,
NumberPart = try Number.From(LessonSplit{1}) otherwise null,
DayOfWeekIndex = if DayOfWeek = "M" then 0
else if DayOfWeek = "T" then 1
else if DayOfWeek = "W" then 2
else if DayOfWeek = "TH" then 4
else if DayOfWeek = "F" then 5
else null,
// Handle non-numbered slots
SlotIndex = if NumberPart = null then
if [Lesson] = "MA" then 10
else if [Lesson] = "MB" then 20
else if [Lesson] = "ML" then 30
else if [Lesson] = "TA" then 110
else if [Lesson] = "TB" then 120
else null
else
DayOfWeekIndex * 100 + NumberPart
in
SlotIndex
)
