Help required with dax

[VDS9]

Hi, I have a report that involves slots throughout the school day which includes M1 (monday lesson 1), M2, M3, M4,M5,M6,M7, T1,T2,T3,T4 etc.... however we also have other slots which are not numbered such as MA (monday arrival), MB (monday breakfast), ML (monday lunch), TA (tuesday arrival), TB (tuesday breakfast)....etc.

In order to have them in the correct order and to create a clear timeline I've created the following:

= 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 = LessonSplit{0},
Number = Number.From(LessonSplit{1}),
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 5
in
DayOfWeekIndex * 100 + Number)

Thisa is applying correctly to all actual lessons (M1,M2,M3,M4...F6) but all other slots are coming as error.

Can anyone help with this or suggest a solution?

Thank you

[Bibiano_Geraldo]

Hi @VDS9 ,
I think thatt the logic in your Lesson Index column assumes that all Lesson values contain a number after the day identifier (M1, T3). However, slots like MA, MB, ML cause errors since they don't have a number part.
You can modify the formula to handle both numbered and non-numbered slots by assigning a separate index for non-numbered slots.
Please try the bellow Code:

= 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
)

[VDS9]

Thanks. I was going to enter the new dax code but there has been a slight change as in the headers. 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,MR,M1,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 look like this for everyone: FA,FR,F1,F2,FB,F3,F4,FL,FF,FA,FO.

Will I be able to still create something like this? Or due to the slight changes wont be possible?

Thanks

[Bibiano_Geraldo]

Hi @VDS9 ,

The key is to normalize the column headers, meaning you will need to handle the first three sheets (which have fewer columns) and the Friday-specific columns in a way that ensures consistent indexing across all sheets. You can achieve this by checking the column count and adjusting your logic accordingly.

= 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] = "FL" then 80 
                            else if [Lesson] = "FF" then 90 
                            else if [Lesson] = "FO" then 100
                            else null
                        else 
                            DayOfWeekIndex * 100 + NumberPart
                    else null // Fallback for unexpected column counts
    in 
        SlotIndex
)

[VDS9]

Hi @Bibiano_Geraldo thanks for your help. I have entered the code you suggested and although there were no errors detected, the index is now coming as null for every slot.

What do you think the issue is?

[bhanu_gautam]

@VDS9 , Try using

m
= 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 = LessonSplit,
Number = try Number.From(LessonSplit) 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 5,
SlotIndex = if Number <> null then DayOfWeekIndex * 100 + Number
else if Text.EndsWith([Lesson], "A") then DayOfWeekIndex * 100 - 3
else if Text.EndsWith([Lesson], "B") then DayOfWeekIndex * 100 - 2
else if Text.EndsWith([Lesson], "L") then DayOfWeekIndex * 100 - 1
else null
in
SlotIndex
)