Hi I am trying to create a lesson index so that I have them in the correct order when looking at a linechart. Unfortunately isnt as straight forward as there are a couple of exceptions.
This data comes from a folder with several identical excel files (1 file p/week).
In each file there are 9 sheets. The first 3 sheets have only 12 "Lessons"
| MA | MR | M1 | M2 | MB | M3 | M4 | ML | M5 | M6 | ME | MD | TA | TR | T1 | T2 | TB | T3 | T4 | TL | T5 | T6 | TE | TD | WA | WR | W1 | W2 | WB | W3 | W4 | WL | W5 | W6 | WE | WD | THA | THR | TH1 | TH2 | THB | TH3 | TH4 | THL | TH5 | TH6 | THE | THD |
The other 6 sheets have 13 "lessons"
| MA | MR | M1 | M2 | M3 | MB | M4 | M5 | ML | M6 | M7 | ME | MD | TA | TR | T1 | T2 | T3 | TB | T4 | T5 | TL | T6 | T7 | TE | TD | WA | WR | W1 | W2 | W3 | WB | W4 | W5 | WL | W6 | W7 | WE | WD | THA | THR | TH1 | TH2 | TH3 | THB | TH4 | TH5 | THL | TH6 | TH7 | THE | THD |
On Friday, all sheets only have 11 "lessons".
I've used the code below but the index is coming as null.
= Table.AddColumn(
#"Changed Type",
"Lesson Index",
each let
Digits = {"0".."9"},
// Split the Lesson column by digits and non-digits
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,
// Determine if we are on the first three sheets or the others
ColumnCount = Table.ColumnCount(#"Changed Type"),
SlotIndex = if ColumnCount = 12 then // Sheets with 1 less column (first 3 sheets)
if NumberPart = null then
if [Lesson] = "MA" then 10
else if [Lesson] = "MR" then 20
else if [Lesson] = "M1" then 30
else if [Lesson] = "M2" then 40
else if [Lesson] = "MB" then 50
else if [Lesson] = "M3" then 60
else if [Lesson] = "M4" then 70
else if [Lesson] = "ML" then 80
else if [Lesson] = "M5" then 90
else if [Lesson] = "M6" then 100
else if [Lesson] = "ME" then 110
else if [Lesson] = "MD" then 120
else null
else
DayOfWeekIndex * 100 + NumberPart
else if ColumnCount = 13 then // Sheets with all columns (remaining sheets)
if NumberPart = null then
if [Lesson] = "MA" then 10
else if [Lesson] = "MR" then 20
else if [Lesson] = "M1" then 30
else if [Lesson] = "M2" then 40
else if [Lesson] = "M3" then 50
else if [Lesson] = "MB" then 60
else if [Lesson] = "M4" then 70
else if [Lesson] = "M5" then 80
else if [Lesson] = "ML" then 90
else if [Lesson] = "M6" then 100
else if [Lesson] = "ME" then 110
else if [Lesson] = "MD" then 120
else null
else
DayOfWeekIndex * 100 + NumberPart
else if ColumnCount = 10 then // Fridays with special slots
if NumberPart = null then
if [Lesson] = "FA" then 10
else if [Lesson] = "FR" then 20
else if [Lesson] = "F1" then 30
else if [Lesson] = "F2" then 40
else if [Lesson] = "FB" then 50
else if [Lesson] = "F3" then 60
else if [Lesson] = "F4" then 70
else if [Lesson] = "FF" then 80
else if [Lesson] = "FL" then 90
else if [Lesson] = "FA" then 100
else if [Lesson] = "FO" then 110
else null
else
DayOfWeekIndex * 100 + NumberPart
else null // Fallback for unexpected column counts
in
SlotIndex
)
Anyone able to help?
Thank you
